Deadlocks with Custom Sequence

There are cases where the identity column property is not the ideal option
for an auto-numbering mechanism. A common practice around using identity
is to create your own custom sequencing mechanism. You create a table
with a single row and a single column holding the last used sequence value:

set nocount on;use tempdb; -- use tempdb for test purposesgocreate table dbo.seq(val int not null);insert into dbo.seq values(0);

You also create a stored procedure invoking a specialized UPDATE
statement that both increments the value and sets it to a variable (output
parameter of the stored procedure in this case):

create proc dbo.usp_getseq @nextval as int outputasupdate dbo.seq set @nextval = val = val + 1;go

Whenever you need a new sequence value, you invoke the stored
procedure:

declare @i as int;exec dbo.usp_getseq @i output;print @i;

Recently someone posted a question in a public SQL Server programming
newsgroup. He got deadlocks when multiple sessions running concurrently
were executing a similar stored procedure while working with the
serializable isolation level, and was wondering how could this happen.

It’s easy to reproduce such deadlocks, and if you trace the locks acquired
and released during the activity, you can figure out how the deadlocks
evolve.
In order to reproduce the deadlock, run the following code from two
sessions concurrently:

set nocount on;use tempdb;set transaction isolation level serializable;declare @i as int;while 1 = 1  exec dbo.usp_getseq @i output;

The sequence of lock acquired and lock released events when a single
session runs the procedure under the serializable isolation level is:

TS1: Acquire IX lock on table - granted
TS2: Promote IX lock to X lock on table - granted
TS3: Update data
TS4: Release X lock from table

An IX (Intent Exclusive) lock is compatible with another IX lock, but an X
(exclusive) lock is incompatible with an IX lock. This is actually what allows
a deadlock to evolve here.
If two transactions working under the serializable isolation level execute the
procedure at the same time, this is the sequence of events that leads to a
deadlock:

TS1: Tran 1: Acquire IX lock on table - granted
TS2: Tran 2: Acquire IX lock on table - granted (IX lock is compatible with IX lock)
TS3: Tran 1: Attempt to promote IX lock to X lock on table - wait: conflict with Tran 2’s IX lock
TS4: Tran 2: Attempt to promote IX lock to X lock on table - wait: conflict with Tran 1’s IX lock
***Deadlock***

Assuming you have your own reasons to run the code using the serializable
isolation level, there’s a simple workaround; use a hint in the UPDATE
statement to lock the whole table:

alter proc dbo.usp_getseq @nextval as int outputasupdate dbo.seq set @nextval = val = val + 1from dbo.seq with (tablock);go

Here’s the sequence of lock acquired and lock released events that takes
place when you run the revised stored procedure:

TS1: Acquire SIX lock on table - granted
TS2: Promote SIX lock to X lock on table - granted
TS3: Update data
TS4: Release X lock from table

The key point here that prevents a deadlock is that an SIX (Shared with
Intent Exclusive) lock is incompatible with another SIX lock. Therefore,
once one transaction acquires an SIX lock, another transaction will not be
able to acquire one as well, and will have to wait. So the transaction that got
the SIX lock will be able to promote it to an X lock, update the row, then
release the X lock. The other transaction can now obtain the SIX lock it was
waiting for and proceed.

Here’s an example of the sequence of events that take place when two
sessions run the stored procedure at the same time:

TS1: Tran 1: Acquire SIX lock on table - granted
TS2: Tran 2: Attempt to acquire SIX lock on table - wait (SIX lock is incompatible with SIX lock)
TS3: Tran 1: Promote SIX lock to X lock on table - granted
TS4: Tran 1: Update data
TS5: Tran 1: Release X lock from table
TS6: Tran 2: Now that Tran 1 released the lock, Tran 2 can acquire the SIX lock it has been waiting for - granted
TS7: Tran 2: Promote SIX lock to X lock on table - granted
TS8: Tran 2: Update data
TS9: Tran 2: Release X lock from table

As you can realize, a deadlock cannot occur. To verify this, you can run the
code provided earlier from two sessions concurrently and wait a while:

set nocount on;use tempdb;set transaction isolation level serializable;declare @i as int;while 1 = 1  exec dbo.usp_getseq @i output;

You will experience no deadlocks.
If you don’t have to use the serializable isolation level, there’s another
workaround. If you use the default read committed isolation, you can use
the original version of the stored procedure (without the hint), and you will
not get deadlocks.
Revise the stored procedure back to its original form:

alter proc dbo.usp_getseq @nextval as int outputasupdate dbo.seq set @nextval = val = val + 1;go

If you run the stored procedure using the default read committed isolation,
the following sequence of lock acquired and released events takes place:

TS1: Acquire IX lock on table
TS2: Acquire IU lock on page
TS3: Acquire U lock on row
TS4: Promote lock on page to IX lock
TS5: Promote lock on row to X lock
TS6: Update row
TS7: Release X lock from row
TS8: Release IX lock from page
TS9: Release IX lock from table

Also here, there’s no situation that can evolve where both sessions are
holding one type of lock, and are blocked when requesting the next type of
lock in the above sequence. For example, suppose that one session went
through the first three events in the above sequence and is now holding an
update lock; by that time the other session went through the first two events,
and is asking for an update lock; this request will be blocked since one
update lock is incompatible with another.
To verify that there are no deadlocks when using the read committed
isolation, you can run the following code from two sessions concurrently:

set nocount on;use tempdb;set transaction isolation level read committed;declare @i as int;while 1 = 1  exec dbo.usp_getseq @i output;

You can wait a while, but you will get no deadlocks…

Cheers,
--
BG