Lost deadlocks

[Matt White]

We've found deadlocks in the trace file that were not captured by our
Powerbuilder application. Some deadlocks are trapped or, at least,
reported to the user as a db error, and others are completely silent.
We've also seen evidence of strange data that would be explained by
unprocessed deadlocks, although we've not yet proven that the
unreported deadlocks are killing updates to the db.

Putting a raiserror into various parts of the same code (and code
review) appears to prove that we are error checking after each db
update. That is, it looks like we're checking, and a raiserror always
bubbles up to the app.

Can anyone shed some light on a.) How this could happen and b.) What
Should We Do?

Some of one of the traces below (with minor anotations.

Thanks

Deadlock encountered .... Printing deadlock information
2004-11-11 10:33:57.77 spid4
2004-11-11 10:33:57.77 spid4 Wait-for graph
2004-11-11 10:33:57.77 spid4
2004-11-11 10:33:57.77 spid4 Node:1
2004-11-11 10:33:57.77 spid4 TAB: 6:1739869265 (cbo1023p) []
CleanCnt:2 Mode: X Flags: 0x0
2004-11-11 10:33:57.77 spid4 Wait List:
2004-11-11 10:33:57.77 spid4 Owner:0x60e085e0 Mode: IS
Flg:0x0 Ref:1 Life:00000000 SPID:88 ECID:0
2004-11-11 10:33:57.77 spid4 SPID: 88 ECID: 0 Statement Type:
SELECT Line #: 123
2004-11-11 10:33:57.77 spid4 Input Buf: Language Event: select
cbord.cbo1000p_item.longname as itemname,
cbord.cbo4002p_itemevent.eventdate,
cbord.cbo4002p_itemevent.eventstatus,
cbord.cbo4002p_itemevent.unitid,
cbord.cbo4004p_eventlist.itembin_intid,
cbord.cbo4004p_eventlist.itemu
2004-11-11 10:33:57.77 spid4 Requested By:
2004-11-11 10:33:57.77 spid4 ResType:LockOwner Stype:'OR' Mode:
IS SPID:84 ECID:0 Ec:(0x4F9B3A00) Value:0x4a0e9400 Cost:(0/0)
2004-11-11 10:33:57.77 spid4
2004-11-11 10:33:57.77 spid4 Node:2
2004-11-11 10:33:57.77 spid4 TAB: 6:1739869265 (cbo1023p) []
CleanCnt:2 Mode: X Flags: 0x0
2004-11-11 10:33:57.77 spid4 Grant List 2::
2004-11-11 10:33:57.77 spid4 Owner:0x4de9a8a0 Mode: X
Flg:0x0 Ref:742 Life:02000000 SPID:121 ECID:0
2004-11-11 10:33:57.77 spid4 SPID: 121 ECID: 0 Statement Type:
UPDATE Line #: 14
2004-11-11 10:33:57.77 spid4 Input Buf: RPC Event:
cbord.p_pur002_replacecost;1
2004-11-11 10:33:57.77 spid4 Requested By:
2004-11-11 10:33:57.77 spid4 ResType:LockOwner Stype:'OR' Mode:
IS SPID:88 ECID:0 Ec:(0x4F259A70) Value:0x60e085e0 Cost:(0/0)
2004-11-11 10:33:57.77 spid4
2004-11-11 10:33:57.77 spid4 Node:3
2004-11-11 10:33:57.77 spid4 KEY: 6:2134298663 (cbo1000p_item):1
(96006e2bf95f) CleanCnt:1 Mode: U Flags: 0x0
2004-11-11 10:33:57.77 spid4 Grant List 2::
2004-11-11 10:33:57.77 spid4 Grant List 3::
2004-11-11 10:33:57.77 spid4 Owner:0x4dc088a0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:84 ECID:0
2004-11-11 10:33:57.77 spid4 SPID: 84 ECID: 0 Statement Type:
CONDITIONAL Line #: 63
2004-11-11 10:33:57.77 spid4 Input Buf: Language Event: select
cbord.cbo1000p_item.longname as itemname,
cbord.cbo4002p_itemevent.eventdate,
cbord.cbo4002p_itemevent.eventstatus,
cbord.cbo4002p_itemevent.unitid,
cbord.cbo4004p_eventlist.itembin_intid,
cbord.cbo4004p_eventlist.itemu
2004-11-11 10:33:57.77 spid4 Requested By:
2004-11-11 10:33:57.77 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:121 ECID:0 Ec:(0x5F719A70) Value:0x48286aa0 Cost:(0/B9654)
2004-11-11 10:33:57.77 spid4 Victim Resource Owner:
2004-11-11 10:33:57.77 spid4 ResType:LockOwner Stype:'OR' Mode:
IS SPID:88 ECID:0 Ec:(0x4F259A70) Value:0x60e085e0 Cost:(0/0)
2004-11-11 10:34:02.77 spid4

[Erland Sommarskog]

Matt White (mj*@cbord.com) writes:

We've found deadlocks in the trace file that were not captured by our
Powerbuilder application. Some deadlocks are trapped or, at least,
reported to the user as a db error, and others are completely silent.
We've also seen evidence of strange data that would be explained by
unprocessed deadlocks, although we've not yet proven that the
unreported deadlocks are killing updates to the db.

Putting a raiserror into various parts of the same code (and code
review) appears to prove that we are error checking after each db
update. That is, it looks like we're checking, and a raiserror always
bubbles up to the app.

Can anyone shed some light on a.) How this could happen and b.) What
Should We Do?

It may be more of a PowerBuilder issue than an SQL Server issue.
PowerBuilder may be doing something "smart" with deadlocks. The
deadlocks may also come in a situation where PowerBuilder is not prepared
for them.

To tell a war story, we had problem with RDO in Visual Basic and SQL 6.5.
We had a customer who occasionally would get half-baked transactions,
and strangely only the latter part of it, but we could not figure out
why. Eventually another customer went live, and they had a lot more
deadlocks. Eventually we found that when you sent bare SQL statements to
SQL Server, RDO would generate temporary stored procedures for this, and
often the dropping of these procedures caused a deadlock that rolled
back the transaction. But RDO failed to raise an error to the VB code,
so we just jogged along merrily, now without a transaction.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp