Simple way to simulate a deadlock

2014-05-05

Simple way to simulate a deadlock

Filed under: Uncategorized — ---- @ 11:47 PM

Why ?

Sometimes we need to test our apps for deadlocks. In this case we might need a simple way to simulate this type of error.

Intro

According to this, a deadlock occurs when two or more transactions block each other thus:

Every transaction (T1, T2) has successfully got a lock on a resource (T1 <- R1, T2 <- R2) and
Every transaction try to lock the other resource (T1 -> R2, T2 -> R1) and
For every resource, existing locks and requested locks are incompatible (ex. T1 <- R1 and T2 -> R1).

(source)

Resources: here (section Deadlock Information Tools, Resource attributes) and here (section Resource Details) you may find a full list with all resources types. Usually, resources which appear in deadlocks are KEYs and PAGes. A KEY refers to index records and this type of resource is identified by a hash value (hash). A PAGe refers to 8K data pages and this type of resource is indentified by a pair <file_id>:<page_in_file>.

Example:

Code Snippet
CREATEDATABASESimpleDeadlock;
GO
 USE SimpleDeadlock;
 GO
 CREATE TABLE dbo.MyTable (
     Col1 INT NOT NULL CONSTRAINT PK_MyTable_Col1 PRIMARY KEY (Col1),
     Col2 VARCHAR(50) NOT NULL
 );
 GO
 INSERT dbo.MyTable (Col1, Col2) VALUES (1, 'A');
 INSERT dbo.MyTable (Col1, Col2) VALUES (2, 'B');
 GO

Note: for every PRIMARY KEY constraint SQL Server automatically creates a UNIQUE INDEX (clustered or non-clustered). In this case, SQL Server creates an UNIQUE index (PK_MyTable_Col1) for the primary key constraint.

In order to get information about the hash value of every record within index PK_MyTable_Col1 we could use the following query:

Code Snippet
SELECT    *, %%lockres%% AS [KEY: hash value] 
 FROM    dbo.MyTable WITH(NOLOCK)
 /*
 Col1 Col2  KEY: hash value
 —- —– —————
 1    A    �a0)
 2    B     (61a06abd401c)
 */

Note: %%lockres%% function is undocumented.

Because this table is small it consumes only one 8K page. We can get information about this page by using sp_AllocationMetadata stored procedure (or by using sys.system_internals_allocation_units view):

Code Snippet
EXEC dbo.sp_AllocationMetadata 'dbo.MyTable'
 /*
 Object Name Index ID    Alloc Unit ID        Alloc Unit Type First Page
 ———– ———– ——————– ————— ———-
 MyTable     1          �5104    IN_ROW_DATA     (1:283)   
 */

As you can see, this page is 283 within file 1.

Lock modes (types) are described here and lock compatibility is described here. For example:

X (exclusive) locks are “Used for data-modification operations, such as INSERT, UPDATE, or DELETE”,
S (shared) locks are “Used for read operations that do not change or update data, such as a SELECT statement” and
S and X locks are incompatible.

Example

In this example we will simulate a write – read deadlock thus:

Every transaction will take an eXclusive lock on those two records and then
Every transaction will try to get a Shared lock on the other record.
Because X and S locks are incompatible these two transactions will generate a deadlock.

Using SQL Server Management Studio, open a new window (SQLQuery1.sql) and start a transaction T1 thus

Code Snippet
– Step #1
 BEGIN TRAN;
     UPDATE    dbo.MyTable 
     SET        Col2 =  Col2 + '*' – 'A' -> 'A*'
     WHERE    Col1 = 1;
 – End of Step #1

At this moment only one resourcea0) is locked X by transaction T1:

Now, open a new window (SQLQuery2.sql) and initiate a new transaction (T2):

Code Snippet
– Step #2
BEGINTRAN;
UPDATE    dbo.MyTable
SET        Col2=Col2+'*'– 'B' -> 'B*'
WHERE    Col1= 2;
     SELECT * 
     FROM    dbo.MyTable 
     WHERE    Col1 = 1;
 – End of Step #2

At this moment, UPDATE statement will take an X lock on record 2 B and SELECT will try to take a S lock on record 1 A (which is already locked by T1). Because record 1 A is already locked X by T1, T2 will have to wait till this X lock is released:

Now, we have to return in the first window (SQLQuery1.sql) and execute

Code Snippet
– Step #3
     SELECT * 
     FROM    dbo.MyTable 
     WHERE    Col1 = 2;
 ROLLBACK;
 – End of Step #3

In this case, SELECT statement from transaction T1 will try to take a S lock on record 2 B

Because this record is already locked X by T2 and because S and X locks are incompatible, T1 which request a S lock will have to wait till X lock (T2) are released.

In this moment the circle is completed and SQL Server deadlock monitor will automatically detect and end this deadlock:

Code Snippet
Msg 1205, Level 13, State 51, Line 7
 Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In my case, SQL Server selected transaction T2 as deadlock victim. This means that T2 is automatically cancelled (ROLLBACK) and all locks took by this transaction are released.

Note: transaction isolation level used for this example is read committed (default).

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Yet another SQL Server enthusiast

2014-05-05