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 purposes
go
create 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 output
as
update 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 output
as
update dbo.seq set @nextval = val = val + 1
from 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 output
as
update 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…
If you don't have a username & password, please
register now.
Reader Comments
Hi Itzik,
Wonderful article, as always.
I wanted to avoid using the hint and decide giving a try to changing the table structure and adding a primary key. Then use it in the update statement.
create table dbo.seq(pk int not null primary key, val int not null); insert into dbo.seq values(1, 0); go
create proc dbo.usp_getseq @nextval as int output as
update dbo.seq set @nextval = val = val + 1 where pk = 1; go
I ran the script you provided, at the same time in four different connections, two of them running under serializable isolation and the other two under read committed, and seems that SQL Server use same pattern of locking in all of them, no matter the isolation level being used.
EventClass Type Mode Lock:Acquired 5 - OBJECT 8 - IX Lock:Acquired 6 - PAGE 8 - IX Lock:Acquired 7 - KEY 5 - X
update data
Lock:Released 7 - KEY 5 - X Lock:Released 6 - PAGE 8 - IX Lock:Released 5 - OBJECT 8 - IX
The first two adquired locks are of the same mode (IX) but different type (OBJECT and PAGE). The final lock adquired, before updating the data, is on a lower grain (KEY) and it is not compatible with any of the locks adquired before. It seems to work.
I wonder what could happend if we increment the number of simultaneous connections, will SQL Server try to adquire an exclusive lock at a higher grain than KEY, before updating the data?. I will try later, may be if I can use OSTRESS tool with SQL Server 2005 (I have not tried before).
I was also thinking to try using a UNIQUE constraint on column [val], but did not try because even if it works, the performance of the update operation will be affected because SQL Server wil have to update also the key of the index related to the constraint.
