Deadlocks in serializable transactions with SQL Server

There is a little lie about the serializable transaction isolation level. In many places you can read that serialization level gives you the illusion of executing the transactions one behind the other, one at a time. Nothing further from the truth.

http://msdn.microsoft.com/en-en/library/tcbchxcb(v=vs.80).aspx

In that link we can read: SERIALIZABLE is the most restrictive isolation level, because it locks entire ranges of keys and holds the locks until the transaction is complete. It encompasses REPEATABLE READ and adds the restriction that other transactions cannot insert new rows into ranges that have been read by the transaction until the transaction is complete.

As you can see all is about locking, but that locking doesn’t mean that a query has to wait until the locking transaction commits. That “locking” means it is protected.

Let’s give an example with a little test database with a table People (id,name):

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT *
FROM people
GO
EXEC sp_lock
GO
INSERT INTO [TestDB].[dbo].[People]
([Name])
VALUES
('Pablo')
GO
EXEC sp_lock
GO
commit
GO

If we execute that script in two separate windows from the set transaction to the first EXEC sp_lock we see the following:

We have two shares locks in the same data, I have a bad feeling about this :). Let’s execute the insert of the first window and see the locks now…

Upssss… it is trying to get the exclusive lock, notice that CNVT…  looking into the documentation it means:

The Status column shows if the lock has been obtained (GRANT), is blocking on another process (WAIT), or is being converted to another lock (CNVT). A lock being converted to another lock is held in one mode, but is waiting to acquire a stronger lock mode (for example, update to exclusive). When diagnosing blocking issues, a CNVT can be considered similar to WAIT.

So it is trying to get an exclusive lock but it is not able to do it because the second transaction has the shared lock. Now if we execute the second insert it gives the following message in the first one:

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

Ugly, isn’ t it? It has to kill one transaction in order to protect the data selected by the other one. But that is not giving the illusion of executing in serial.

So, if you need to execute those queries as if they were executing one behind the other you have to create a real lock in the first select using the hint: with (xlock,ROWLOCK). That prevents the second query to be able to read.

Other option to mitigate this problem is to reduce the amount of information “locked” by the first select.

Regards.

About these ads

3 comments

  1. It has the do with how the range lock works (it always includes 1 more in the range) for instance if you tried this you would have no deadlock

    window 1 :

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRAN

    SELECT *
    FROM People where Id = 1

    SET IDENTITY_INSERT People ON
    INSERT INTO People
    (Id,[Name])
    VALUES
    (1,’Pablo’)

    SET IDENTITY_INSERT People OFF

    window 2

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRAN

    SELECT *
    FROM Person where Id = 3

    SET IDENTITY_INSERT person ON
    INSERT INTO Person
    (Id,[Name])
    VALUES
    (3,’Pablo’)

    SET IDENTITY_INSERT person OFF

    if the second was id 2 the deadlock would have occurred :)

  2. In previous comment window 2 should have been table people not person

  3. [...] Sometimes we have contention problems in database because of the transaction isolation level when we…. To avoid this, if the business allows it, it is useful to “downgrade” the isolation level of .NET transactions. By default, in the .NET framework, it is set to Serializable, but usually we want to use ReadCommited or anything lower instead. [...]

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 155 other followers

%d bloggers like this: