spscorp
asked on
Sql Server Deadlock
Our application has started experiencing a deadlock situation and this is what we got from sql server trace log.
Can somebody please help us interpreting it and finding the query which is responsible for the deadlock?
Can somebody please help us interpreting it and finding the query which is responsible for the deadlock?
2008-07-23 13:17:07.55 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:62 ECID:0 Ec:(0x5CF73528) Value:0x638
2008-07-23 13:17:07.55 spid4 Victim Resource Owner:
2008-07-23 13:17:07.55 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:62 ECID:0 Ec:(0x5CF73528) Value:0x638
2008-07-23 13:17:07.55 spid4 Requested By:
2008-07-23 13:17:07.55 spid4 Input Buf: Language Event: INSERT INTO SalesTransaction (FKGolfCourseID,UniqueS
2008-07-23 13:17:07.55 spid4 SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 7
2008-07-23 13:17:07.55 spid4 Owner:0x1c4d1180 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:58 ECID:0
2008-07-23 13:17:07.55 spid4 Grant List 1::
2008-07-23 13:17:07.55 spid4 KEY: 8:940582439:27 (080069c32651) CleanCnt:2 Mode: U Flags: 0x0
2008-07-23 13:17:07.55 spid4 Node:2
2008-07-23 13:17:07.55 spid4
2008-07-23 13:17:07.55 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:58 ECID:0 Ec:(0x1B021528) Value:0x1a8
2008-07-23 13:17:07.55 spid4 Requested By:
2008-07-23 13:17:07.55 spid4 Input Buf: Language Event: INSERT INTO SalesTransaction (FKGolfCourseID,UniqueS
2008-07-23 13:17:07.55 spid4 SPID: 62 ECID: 0 Statement Type: UPDATE Line #: 7
2008-07-23 13:17:07.55 spid4 Owner:0x1b1e1380 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:62 ECID:0
2008-07-23 13:17:07.55 spid4 Grant List 0::
2008-07-23 13:17:07.55 spid4 KEY: 8:940582439:27 (080093cd6cd5) CleanCnt:2 Mode: X Flags: 0x0
2008-07-23 13:17:07.55 spid4 Node:1
2008-07-23 13:17:07.55 spid4
2008-07-23 13:17:07.55 spid4 Wait-for graph
2008-07-23 13:17:07.55 spid4
2008-07-23 13:17:07.55 spid4 ...
2008-07-23 13:17:05.04 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:115 ECID:0 Ec:(0x6A9FD528) Value:0x61
2008-07-23 13:17:05.04 spid4 Victim Resource Owner:
2008-07-23 13:17:05.04 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:115 ECID:0 Ec:(0x6A9FD528) Value:0x61
2008-07-23 13:17:05.04 spid4 Requested By:
2008-07-23 13:17:05.04 spid4 Input Buf: Language Event: INSERT INTO SalesTransaction (FKGolfCourseID,UniqueS
2008-07-23 13:17:05.04 spid4 SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 7
2008-07-23 13:17:05.04 spid4 Owner:0x1c4d1180 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:58 ECID:0
2008-07-23 13:17:05.04 spid4 Grant List 1::
2008-07-23 13:17:05.04 spid4 KEY: 8:940582439:27 (080069c32651) CleanCnt:2 Mode: U Flags: 0x0
2008-07-23 13:17:05.04 spid4 Node:2
2008-07-23 13:17:05.04 spid4
2008-07-23 13:17:05.04 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:58 ECID:0 Ec:(0x1B021528) Value:0x1b0
2008-07-23 13:17:05.04 spid4 Requested By:
2008-07-23 13:17:05.04 spid4 Input Buf: Language Event: INSERT INTO SalesTransaction (FKGolfCourseID,UniqueS
2008-07-23 13:17:05.04 spid4 SPID: 115 ECID: 0 Statement Type: UPDATE Line #: 7
2008-07-23 13:17:05.04 spid4 Owner:0x74d82360 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:115 ECID:0
2008-07-23 13:17:05.04 spid4 Grant List 0::
2008-07-23 13:17:05.04 spid4 KEY: 8:940582439:27 (08000dcdc619) CleanCnt:2 Mode: X Flags: 0x0
2008-07-23 13:17:05.04 spid4 Node:1
2008-07-23 13:17:05.04 spid4
2008-07-23 13:17:05.04 spid4 Wait-for graph
2008-07-23 13:17:05.04 spid4
2008-07-23 13:17:05.04 spid4 ...
2008-07-23 13:17:00.05 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:62 ECID:0 Ec:(0x5CF73528) Value:0x74b
2008-07-23 13:17:00.05 spid4 Victim Resource Owner:
2008-07-23 13:17:00.05 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:62 ECID:0 Ec:(0x5CF73528) Value:0x74b
2008-07-23 13:17:00.05 spid4 Requested By:
2008-07-23 13:17:00.05 spid4 Input Buf: Language Event: INSERT INTO SalesTransaction (FKGolfCourseID,UniqueS
2008-07-23 13:17:00.05 spid4 SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 7
2008-07-23 13:17:00.05 spid4 Owner:0x1be0cae0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:58 ECID:0
2008-07-23 13:17:00.05 spid4 Grant List 1::
2008-07-23 13:17:00.05 spid4 KEY: 8:940582439:27 (080014c40313) CleanCnt:2 Mode: X Flags: 0x0
2008-07-23 13:17:00.05 spid4 Node:2
2008-07-23 13:17:00.05 spid4
2008-07-23 13:17:00.05 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:58 ECID:0 Ec:(0x1B021528) Value:0x1c4
2008-07-23 13:17:00.05 spid4 Requested By:
2008-07-23 13:17:00.05 spid4 Input Buf: Language Event: INSERT INTO SalesTransaction (FKGolfCourseID,UniqueS
2008-07-23 13:17:00.05 spid4 SPID: 62 ECID: 0 Statement Type: UPDATE Line #: 7
2008-07-23 13:17:00.05 spid4 Owner:0x74177be0 Mode: X Flg:0x0 Ref:0 Life:02000001 SPID:62 ECID:0
2008-07-23 13:17:00.05 spid4 Grant List 0::
2008-07-23 13:17:00.05 spid4 KEY: 8:940582439:27 (080069c32651) CleanCnt:2 Mode: X Flags: 0x0
2008-07-23 13:17:00.05 spid4 Node:1
2008-07-23 13:17:00.05 spid4
2008-07-23 13:17:00.05 spid4 Wait-for graph
2008-07-23 13:17:00.05 spid4
2008-07-23 13:17:00.05 spid4 ...
Last Comment
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
what version/service pack of sql server?
what indexes (normal, clustered) are there on that table?
what indexes (normal, clustered) are there on that table?
Is there anyway you could post the entire statement (you'll have to find it in your code)
ASKER
We are using sql server 2000 SP 4 . The id in the query is identity and a clustered index.
After that insert statement, after we get the inserted id, we have a transaction with a couple of queries. The queries are created at run time based on the type of transaction. But the main thing is, they update the inserted row in the salestransaction table, then insert in other two tables which have one to many relationship with salestransaction table, using the fkid.(Just a simple insert and update statement using identity column(clustered index) in the where clause)
All the tables have a trigger which updates a field in the inserted rows.
After that insert statement, after we get the inserted id, we have a transaction with a couple of queries. The queries are created at run time based on the type of transaction. But the main thing is, they update the inserted row in the salestransaction table, then insert in other two tables which have one to many relationship with salestransaction table, using the fkid.(Just a simple insert and update statement using identity column(clustered index) in the where clause)
All the tables have a trigger which updates a field in the inserted rows.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
INSERT INTO SalesTransaction (FKGolfCourseID,UniqueS