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:

• Please refer to these sites for further details :-
  http://support.microsoft.com/kb/180101/EN-US/
  http://support.microsoft.com/kb/180102/EN-US/
• Make sure that the primary key is IDENTITY and CLUSTERED
• Most SQL operations should be done atomically as far as possible to save on locking and utilize the atomicity at statement level
• Another commonly used useful atomic operation MAX(ISNULL(a,10)) [ NVL() in Oracle ]