Update & Trigger deadlock

Update & Trigger deadlock

Post by Mark A. Nadi » Sun, 11 Feb 2001 02:13:50



Hi Andrew,

Thank you very much for your reply. I'm still digging and will update
the NG when I found out the issue.

Thanks again,

Mark
/\/\/

 
 
 

Update & Trigger deadlock

Post by Mark A. Nadi » Sun, 11 Feb 2001 07:06:48


Hi,

Well, thank you for forcing me to look closer. An easy test showed I
could not reproduce, even with remote views in a simple test. The
issue was the views in the dialog were using different connections.

Thanks again,

Mark
/\/\/

 
 
 

1. Deadlock situation between update and trigger

I have a deadlock situation on a sql server 2000 server.  
The deadlock is occuring between a trigger on an awards
table and an update on the awards table.  There is two
different spids.  This is a website where many users can
be updating so the spInsertOrderDetail stored proc is
executed many times throught out the day.   it doesn't
always deadlock.   Can someone advise me on how to
proceed.  

Here is the trigger Update
-- trgUpdAwardsOnHandStatus
UPDATE Awards SET OnHandStatus = 'AVAILABLE' WHERE AwardID

here is the stored proc update

-- spInsertOrderDetail
UPDATE Awards SET OnHandQuantity = (ISNULL(OnHandQuantity,


Here is the deadlock message

2003-03-16 17:21:36.69 spid2     KEY: 7:1682157088:2
(3b00435f91f9) CleanCnt:1 Mode: U Flags: 0x0
2003-03-16 17:21:36.69 spid2      Grant List 2::
2003-03-16 17:21:36.69 spid2        Owner:0x9f0ae020 Mode:
U        Flg:0x0 Ref:1 Life:00000000 SPID:75 ECID:0
2003-03-16 17:21:36.69 spid2        SPID: 75 ECID: 0
Statement Type: UPDATE Line #: 232
2003-03-16 17:21:36.69 spid2        Input Buf: RPC Event:
spInsertOrderDetail;1
2003-03-16 17:21:36.69 spid2      Requested By:
2003-03-16 17:21:36.69 spid2        ResType:LockOwner
Stype:'OR' Mode: U SPID:66 ECID:0 Ec:(0x9EB8F5C8)
Value:0x2c7c06a0 Cost:(0/21B0)
2003-03-16 17:21:36.69 spid2    
2003-03-16 17:21:36.69 spid2     Node:2
2003-03-16 17:21:36.69 spid2     KEY: 7:1682157088:1
(100051ffe4f4) CleanCnt:1 Mode: X Flags: 0x0
2003-03-16 17:21:36.69 spid2      Grant List 0::
2003-03-16 17:21:36.69 spid2        Owner:0xadf70e40 Mode:
X        Flg:0x0 Ref:0 Life:02000000 SPID:66 ECID:0
2003-03-16 17:21:36.69 spid2        SPID: 66 ECID: 0
Statement Type: UPDATE Line #: 20
2003-03-16 17:21:36.69 spid2        Input Buf: RPC Event:
spInsertOrderDetail;1
2003-03-16 17:21:36.69 spid2      Requested By:
2003-03-16 17:21:36.69 spid2        ResType:LockOwner
Stype:'OR' Mode: S SPID:75 ECID:0 Ec:(0x359AD5C8)
Value:0x9f0ae280 Cost:(0/8B74)
2003-03-16 17:21:36.69 spid2     Victim Resource Owner:
2003-03-16 17:21:36.69 spid2      ResType:LockOwner
Stype:'OR' Mode: U SPID:66 ECID:0 Ec:(0x9EB8F5C8)
Value:0x2c7c06a0 Cost:(0/21B0)

2. order without ordering

3. mass update & update trigger

4. Microsoft Access 2.0

5. puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

6. AP Pro: Hardware flow control? Check line status?

7. Update & Insert Trigger

8. SS7 / update & insert triggers / dynamic query

9. Triggers & IF UPDATE()

10. Triggers & Row updates

11. Triggers & Updates

12. Insert & Update Trigger Problem