Find and resolve process deadlocks?

Deadlocks occur when query X locks records A then B and query Y locks B then A. Process Y can’t get record A until Process X releases it, and Process X can’t release it until it gets record B, hence they are deadlocked. The only way out is for SQL Server to kill one of the queries.

SQL Server normally kills the cheapest query, the one it will have the least effort in rolling back. This means the deadlock victim as reported in the error is only one side of the story – to get the full story we need a deadlock graph from the database server, which shows which two transactions were involved and on which objects they were deadlocked. The trace flags described in the resolution below will give us this information.

These don’t have to be records in a table necessarily – they can be locking indexes for update or select in different orders.

Deadlocks are usually exacerbated by slow running queries, so first step is to get the worst query plans from the DBA and start to look for big table or index scans or CPU intensive loops and joins.

The steps below talk about methods to reduce the amount of data in the database – a query won’t show up as being inefficient until there is lots of data to trawl through, so generally they occur in large databases where this is lots of contention from a high number of resource PCs – before reducing the data it’s worth getting the info from the database so we can improve the product (the related article on database timeouts describes how this can be done in more detail).

There are a few things that can be tried, depending on version. For older versions of Blue Prism, you can run a script to add an index for the Work Queue Items tables to the database. In addition, the following SQL script can be used to delete any unused Work Queue tags:-

delete t
from BPATag t
left join BPAWorkQueueItemTag it on t.id = it.tagid
where it.tagid is null;

(See also the related document on database maintenance, with some useful scripts for reducing the size of various tables within the database).

Finally if deadlocks are still occurring, ask the Database Administrator to set trace flags 1204 and 1222 on the database and send us the error logs, which will tell us which indexes/tables are deadlocking.

The deadlock is caused by the statuses “locked”, “exception”, “deferred”, and “finished” being accessed in a range lookup index and also being attempted to be updated later in the transaction which causes the cross over.

In 4.2.42 and above, these status fields are removed from the index and the queries are optimised for better performance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s