SQL Server: @@IDENTITY deadlock problem and fix

This interesting problem occurs only when there is a call to update after the insert and the @@IDENTITY value has to be locked, so there is a deadlock trying to get a hold of this value.

CREATE TABLE [test]
(
[a] [int] IDENTITY (1, 1) NOT NULL ,
[b] [varchar] (10) NULL ,
[c] [int] NULL ,
CONSTRAINT [PK__test] PRIMARY KEY CLUSTERED ( [a] )
)
GO

Here [a] and [c] have to have the same value.

So, this programmer goes ahead and adds a trigger to do this on the insert operation.

CREATE TRIGGER test_update ON dbo.test
FOR INSERT
AS
begin
update dbo.test set c = a
end;

And, the insert statement called by two threads(client processes) simultaneously is:

insert into test(b) VALUES (‘test111’)

This leads to a deadlock and this “Error Message:”
“Exception Transaction Process (PID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
Rerun the transaction”

The fix:
insert into test(b,c) VALUES (‘test111’,@@IDENTITY)

Notes:

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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

%d bloggers like this: