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] )
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
update dbo.test set c = a
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”
insert into test(b,c) VALUES (‘test111’,@@IDENTITY)
- Please refer to these sites for further details :-
- 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 ]