Board index MS SQL Server

Deadlock trace: help needed

Deadlock trace: help needed

Post by bartv » Fri, 30 Jun 2000 04:00:00



Hi,

I have following information from my log files, but I don't know
well what to with the information. It seems strange to me to see
that a select query requests an exclusive lock on a table.
Both queries are executed from an ADO application.

Any help is welcome,

Bart

**** Starting deadlock search: SPID 16 ****
spid2    --- Searching deadlock
spid2    RID: 8:1:284:44                CleanCnt:2 Mode: U
Flags: 0x2
spid2     Grant List::
spid2       Mode: U        Flg:0x0 Ref:1 Life:00000000 SPID:16
ECID:0
spid2       Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:18
ECID:0
spid2     Convert List:
spid2       Mode: X        Flg:0x2 Ref:1 Life:02000000 SPID:16
ECID:0
spid2    --- Searching deadlock
spid2    RID: 8:1:284:45                CleanCnt:2 Mode: X
Flags: 0x2
spid2     Grant List::
spid2       Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:16
ECID:0
spid2     Wait List:
spid2       Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:18
ECID:0
spid2    *** Deadlock Detected ***
spid2     ==> Process 18 chosen as deadlock victim
spid2     == Deadlock Detected at: 2000-06-29 09:11:00.92
spid2     == Session participant information:
spid2     SPID: 16 ECID: 0 Statement Type: UPDATE Line #: 1
spid2     Input Buf: U P D A T E   M a i n L o g   S E T   M s g
P e n d i n g   =   0   W H E R E   M s g I D   =   1 3 2 6   A
N D   C o p e r n i c u s I D   =   2
2000-06-29 09:11:00.94 spid2     SPID: 18 ECID: 0 Statement
Type: EXECUTE Line #: 0
2000-06-29 09:11:00.96 spid2     Input Buf:   s p _ c u r s o r
o p e n     &    ? S E L E C T   T O P   3 0 0   M a i n L o
g . M s g I D ,   M s g P e n d i n g ,   M s g L e v e l T M
N ,   G e n T i m e ,   S o u r c e N a m e ,   M s g T e x
t ,   M s g T y p e T M N ,   M s g S u b j e c t ,   R e g T i
m e ,   A c k S o u r c e ,   A c k T i m e ,   S o u r c e L o
c a t i o n ,   A t t a c h m e n t s . I n f o ,   S o????
m??????????5??????????????????????????????????????/?
??????????????????6??????????????????5????????????2000-06-29
09:11:00.99 spid2
 spid2     == Deadlock Lock participant information:
 spid2     == Lock: RID: 8:1:284:45
 spid2     Database: CopLog
 spid2     Table: MainLog
 spid2      - Held by: SPID 16 ECID 0 Mode "X"
 spid2      - Requested by: SPID 18 ECID 0 Mode "S"
 spid2     == Lock: RID: 8:1:284:44
 spid2     Database: CopLog
 spid2     Table: MainLog
 spid2      - Held by: SPID 18 ECID 0 Mode "S"
 spid2      - Requested by: SPID 16 ECID 0 Mode "X"
 spid2
**** Starting deadlock search: SPID 18 ****
spid2    --- Searching deadlock
spid2    RID: 8:1:284:45                CleanCnt:2 Mode: X
Flags: 0x2
spid2     Grant List::
spid2       Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:16
ECID:0
spid2     Wait List:
spid2       Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:18
ECID:0
spid2    --- Searching deadlock
spid2    RID: 8:1:284:44                CleanCnt:2 Mode: U
Flags: 0x2
spid2     Grant List::
spid2       Mode: U        Flg:0x0 Ref:1 Life:00000000 SPID:16
ECID:0
spid2       Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:18
ECID:0
spid2     Convert List:
spid2       Mode: X        Flg:0x2 Ref:1 Life:02000000 SPID:16
ECID:0

-----------------------------------------------------------

Got questions?  Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.keen.com

 
 
 
Top

Deadlock trace: help needed

Post by Bob Pfeif » Fri, 30 Jun 2000 04:00:00


The following excerpt from the deadlock info indicates that the select
statement is holding the shared lock and the update is holding the exclusive
lock:

spid2     Table: MainLog
 spid2      - Held by: SPID 16 ECID 0 Mode "X"
 spid2      - Requested by: SPID 18 ECID 0 Mode "S"

the update statement is on spid 16 based on this excerpt from the deadlock
info:

spid2     == Session participant information:
spid2     SPID: 16 ECID: 0 Statement Type: UPDATE Line #: 1
spid2     Input Buf: U P D A T E   M a i n L o g   S E T   M s g
P e n d i n g   =   0   W H E R E   M s g I D   =   1 3 2 6   A
N D   C o p e r n i c u s I D   =   2

How are you executing the update from ADO?  Your select statement is using a
server side cursor as indicated by the sp_cursoropen call, what kind of
locking option do you have set for the select?

 
 
 
Top

1. Need help in troubleshooting deadlock trace

Hi

I'm trying to troubleshoot a deadlock in our application. I'm using
SQL server 2k with SP2. I've switched on the trace flags 1204 and 1205
in SQL server. I got the trace below.
Now, the trace information gives the DBID:ObjectID:FileName (Resource
Name).

The "Resource Name" is an internal hash of the data in SQL server. Is
it possible to find out which page this data actually points to. I've
scoured Kalen's Inside Sql 2K for any way on how to decipher this
value but haven't been able to find any solution.

Is it possible to figure out the data page and the exact row in that
field so that i can look at the data that in the page using the DBCC
PAGE ? I need to do this to confirm my suspicion as to why the
deadlock is occurring.

TIA
Rahul

Deadlock encountered .... Printing deadlock information

Wait-for graph

Node:1
KEY: 7:309576141:1 (3300c1880c84) CleanCnt:1 Mode: U Flags: 0x0
 Grant List::
   Owner:0x42bd4740 Mode: S        Flg:0x0 Ref:1 Life:00000000
SPID:170 ECID:0
   SPID: 170 ECID: 0 Statement Type: SELECT INTO Line #: 41
   Input Buf: RPC Event: pr_GM_UpdGuestPrioritization;1
 Requested By:
   ResType:LockOwner Stype:'OR' Mode: X SPID:160 ECID:0
Ec:(0x534a1518) Value:0x42bdb8e0 Cost:(0/660)

Node:2
KEY: 7:789577851:1 (3101f1e8d0e6) CleanCnt:1 Mode: X Flags: 0x0
 Grant List::
   Owner:0x49e381c0 Mode: X        Flg:0x0 Ref:0 Life:02000000
SPID:160 ECID:0
   SPID: 160 ECID: 0 Statement Type: UPDATE Line #: 397
   Input Buf: RPC Event: pr_GM_UpdGuestAllocation;1
 Requested By:
   ResType:LockOwner Stype:'OR' Mode: S SPID:170 ECID:0
Ec:(0x52b43528) Value:0x49e398c0 Cost:(0/0)
Victim Resource Owner:
 ResType:LockOwner Stype:'OR' Mode: S SPID:170 ECID:0 Ec:(0x52b43528)
Value:0x49e398c0 Cost:(0/0)

End deadlock search 1179 ... a deadlock was found.
----------------------------------

2. encoding: ODBC, createdb

3. Deadlock situtation (deadlock victim) - help needed!!

4. DBPowerSuite for Postgres, MySQL, Sybase and Oracle

5. help reading deadlock trace

6. Urgen Urgent query on ADO <-> Oracle 8 databases

7. Help reading trace (deadlock) output

8. high availability solutions

9. Tracing deadlocks in SQL 7.0

10. Not getting information in trace when deadlock!

11. Deadlock trace flag 1204 dump

12. investigating deadlocks without using trace flag 1204 ?

13. Enabling trace flag didn't give Complete 1204 Report on Deadlock


All times are UTC
Board index