Deadlock Analysis (Examples)

As promised in my former blog about Deadlock Analysis we proceed today with the deadlock serie with examples and their solutions.

• Update Locks

The first example is a Deadlock with two identical statements, doing deletes on table BC_MON_AGREGATES:

The statements are:

DELETE FROM
BC_MON_AGREGATES
WHERE
AGR_INTERVAL = @P0 AND START_TIME < @P1;

As you can see in the deadlock graph both threads are having (at least) a X locks on a index named PK__BC_MON_A__3214EC272B5F6B28, the primary key on this table. And both are requesting an U lock on the same index. As the U locks are only used for checking if the row matches the WHERE condition, this constellation is very often seen when the threads are scanning the table or index instead of seeking through an index to find the rows.

In this case the solution was to create an fitting index for the WHERE clause to get the two thread to seek instead to scan the index.

• RID Locks

In the next case we see, that both threads are requesting RID locks on table GENSETC:

The statements were:

UPDATE
GENSETC
SET NAME = @P1,COMPONENT = @P2,GENSTATUS = @P3
WHERE
PGMID = @P4
AND OBJECT = @P5
AND OBJNAME = @P6

RID locks are only used if there is no clustered index on that table. If there is an clustered index, we will see KEY locks. The root cause for this deadlock was the missing clustered index as defined in the SAP dictionary (PGMID, OBJECT, OBJNAME).

• PAGE locks

Here we see PAGE locks on table /1OT/FDA12IT2001:

Both statements were running a DELETE on that table:

DELETE FROM /1OT/FDA12IT2001
WHERE CLIENT = @P1 AND ORDID IN ( @P2,.....,@P101 )

PAGE locks are used when the SQL Server Optimizer expects a lot of rows to process. The PAGE locks are used from the beginning, means this is not an lock escalation, as there are only lock escalations from PAGE to TABLE and from ROW to TABLE, but not from ROW to PAGE. In this case the solution can be to disallow PAGE locks on the table, to reduce the IN clause or to create an fitting index for the WHERE clause (index on ORDID).

• Deadlock with a SELECT

In the next deadlock we have an UPDATE and a SELECT deadlocking each other:

The SELECT is easy to identify by the S locks it is using, the right one is the UPDATE using an fitting index (no scanning).

How can a SELECT be part of a deadlock, as SAP is reading most of the data in "READ UNCOMMITED" ? The answer is, that this SELECT runs in "READ COMMITED" mode, means it only reads data that is is already committed and it will be blocked through U and X locks. The application that issues the UPDATE and SELECT statement has to analyze this deadlock and to avoid the parallel execution of these two statement by e.g. semaphores.

• Multiple Tables

A deadlock not only happens between two threads, the SQL Server can detect deadlocks in any depth. A deadlock between three threads is looking like:

The SQL Server will kill the thread with the least amount of log used, in this case the left one. In some case the remaining threads will deadlock again as a two node deadlock, but most of the time the deadlock is resolved by kill one out of the chain.

The most impressive deadlock I saw was this one:

This is a deadlock with 103 threads, the left end with the victim looks like:

The victim was an UPDATE on BC_JMSQUEUE and all the other waiting ones were a DELETE on the same table. The root cause was, that there was no fitting index for the DELETE statement, which causes the DELETE to scan the table instead using an index. After creating the index, the problem was resolved.

• Different tables

Deadlock can happen not only on the same table, but between different tables as well:

We see a deadlock between table CBS_ACCESSQUEUE and CBS_LOCK. The statements are UPDATE statements on each table. These kind of deadlock are not easy to understand as it only shows the end of the deadlock. The two statements that let to the two initial locks (the KEY lock ACCESSQUEUE from the victim and the KEY lock on CBS_LOCK of the winner) are not part of the deadlock, but might be part of the problem. How to analyse such deadlocks will be part of my third post of this deadlock serie (coming soon).