December 6, 2007 at 5:09 pm
I have a client getting the following deadlock error:
Transaction (Process ID 83) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I turned on Trace Flag 1204 and isolated the SQL Query's involved with the deadlock.
Wait-for graph
Node:1
PAG: 8:1:5782430 CleanCnt:2 Mode: SIX Flags: 0x2
Grant List 0::
Owner:0xa03db060 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:93 ECID:0
SPID: 93 ECID: 0 Statement Type: UPDATE Line #: 10
Input Buf: Language Event: insert into CashList (TELLER, DATE, TRANS, LOCATION,
Requested By:
ResType:LockOwner Stype:'OR' Mode: U SPID:272 ECID:0 Ec:(0x85A5F520) Value:0x83
Node:2
Port: 0x800c0880 Xid Slot: 0, EC: 0x99d91520, ECID: 0 (Coordinator), Exchange
Coordinator: EC = 0x99d91520, SPID: 93, ECID: 0, Not Blocking
Consumer List::
Consumer: Xid Slot: 0, EC = 0x99d91520, SPID: 93, ECID: 0, Not Blocking
Producer List::
Producer: Xid Slot: 1, EC = 0x8ff3e0c0, SPID: 93, ECID: 2, Blocking
Producer: Xid Slot: 2, EC = 0x8ffb00c0, SPID: 93, ECID: 1, Blocking
Node:3
PAG: 8:1:5761766 CleanCnt:3 Mode: IX Flags: 0x2
Wait List:
Owner:0x842bade0 Mode: S Flg:0x0 Ref:1 Life:00000001 SPID:93 ECID:1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:2 Ec:(0x8FF3E0C0) Value:0x842
Node:4
PAG: 8:1:5761766 CleanCnt:3 Mode: IX Flags: 0x2
Grant List 1::
Owner:0x2c252d20 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:272 ECID:0
SPID: 272 ECID: 0 Statement Type: UPDATE Line #: 10
Input Buf: Language Event: insert into CashList (TELLER, DATE, TRANS, LOCATION,
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:1 Ec:(0x8FFB00C0) Value:0x842
-- next branch --
Node:2
Port: 0x800c0880 Xid Slot: 0, EC: 0x99d91520, ECID: 0 (Coordinator), Exchange
Coordinator: EC = 0x99d91520, SPID: 93, ECID: 0, Not Blocking
Consumer List::
Consumer: Xid Slot: 0, EC = 0x99d91520, SPID: 93, ECID: 0, Not Blocking
Producer List::
Producer: Xid Slot: 1, EC = 0x8ff3e0c0, SPID: 93, ECID: 2, Blocking
Producer: Xid Slot: 2, EC = 0x8ffb00c0, SPID: 93, ECID: 1, Blocking
-- next branch --
Node:2
Port: 0x800c0880 Xid Slot: 0, EC: 0x99d91520, ECID: 0 (Coordinator), Exchange
Coordinator: EC = 0x99d91520, SPID: 93, ECID: 0, Not Blocking
Consumer List::
Consumer: Xid Slot: 0, EC = 0x99d91520, SPID: 93, ECID: 0, Not Blocking
Producer List::
Producer: Xid Slot: 1, EC = 0x8ff3e0c0, SPID: 93, ECID: 2, Blocking
Producer: Xid Slot: 2, EC = 0x8ffb00c0, SPID: 93, ECID: 1, Blocking
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:1 Ec:(0x8FFB00C0) Value:0x842
The table that the insert is happening on has a trigger that updates a column of the row that was just inserted.
create trigger trans_id on [dbo].[cashlist]
for INSERT
as
DECLARE @key int
select @key = cashlist_key from INSERTED
update cashlist set trans_id =
case
when trans = 'Add2VaultFBank' then 1
when trans = 'ADDSVC2DRAWER' then 2
when trans = 'CheckDeposit' then 3
when trans = 'Drawer2PurchGoods' then 4
when trans = 'Drawer2Vault' then 5
when trans = 'OPEN ADDSVC2DRAWER' then 6
[more omitted]
end
where cashlist_key = @key
The table has 7 indexes: 1 Clustered Non Unique, 1 PK Non Clustered on Identity Field, and 5 covering indexes for some of the other columns.
My question to you is this, can the trigger be causing the deadlock issues? Or a combination of the indexing and trigger?
-Frank
December 6, 2007 at 10:26 pm
happens a lot.
It is most likely trying to update a the row that was inserted with in the same transaction as the insert. Which will not work. The row is not committed to the database until the transaction is complete. The update must be done in a separate transaction.
December 6, 2007 at 11:41 pm
That's not a very nice trigger.
Frank Lain (12/6/2007)
select @key = cashlist_key from INSERTED
What happens if more than one row is inserted in a batch?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2007 at 8:35 am
JDixon (12/6/2007)
happens a lot.It is most likely trying to update a the row that was inserted with in the same transaction as the insert. Which will not work. The row is not committed to the database until the transaction is complete. The update must be done in a separate transaction.
Actually - it works 99% of the time. Out of 1200 inserts, only 13 had deadlocks.
December 7, 2007 at 8:54 am
GilaMonster (12/6/2007)
That's not a very nice trigger.Frank Lain (12/6/2007)
select @key = cashlist_key from INSERTED
What happens if more than one row is inserted in a batch?
There will never be more than 1 insert to this table in a batch. Contention seems to happen occasionally when 2 different batches are executed at the exact same time. We will probably just have to muddle through the code and add this column to the inserts and remove the trigger. It was a new column added in haste to facilitate indexing. But I would like to figure out the cause as this trigger type is well within the abilities of SQL Server. Like I said above, it works without issue 99% of the time.
For all I know, it may have nothing to do with the trigger and have to do with the way the indexes are created. I read on a different thread that if you do not have a Unique Clustered index, inserts and updates happen on the heap which can lead to page locks.
December 8, 2007 at 1:37 pm
I'm not sure why this is deadlocking, as statements within a transaction cannot block each other.
If you look through the deadlock graph, can you find any reference to a second process? I'm interested in knowing what the insert is deadlocking with.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply