THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Reproducing a Conversion Deadlock

Even if two processes compete on only one resource, they still can embrace in a deadlock. The following scripts reproduce such a scenario. In one tab, run this:

 

CREATE TABLE dbo.Test INT ) ;
GO
INSERT  INTO dbo.Test
        
)
VALUES  ) ;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
BEGIN TRAN
SELECT  
i
FROM    dbo.Test ;

--UPDATE dbo.Test SET i=2 ;

After this script has completed, we have an outstanding transaction holding a shared lock. In another tab, let us have that another connection have a shared lock on the same resource:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ;
BEGIN TRAN
SELECT  
i
FROM    dbo.Test ;

--UPDATE dbo.Test SET i=2 ;
 

 This script completes and renders a result set, just like the first script did. Now let us highlight and execute the commented update commands in both tabs. To perform an update, each connection needs an exclusive lock. Neither connection can acquire that exclusive lock, because the other one is holding a shared lock. Although both connections are competing on only one resource, they have embraced in a conversion deadlock:

Msg 1205, Level 13, State 56, Line 1
Transaction (Process ID 59) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
 

 

 

 

Published Wednesday, June 02, 2010 9:39 AM by Alexander Kuznetsov
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Lana Goldenberg said:

Thanks, Alex. This is a good example of concurrency side effects. With the Serializable isolation level other transaction cannot modify data that has been read by the current transaction until the current transaction completes.

June 3, 2010 3:13 PM
 

Manoj said:

Thanks Alex. It is indeed a Good Example of reproducing. How to solve this kind of deadlock?

April 7, 2014 9:38 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement