Trace 1204, SQL 2008, finding objects in deadlock
-
Wednesday, August 29, 2012 12:03 AM
In decoding the information in my error log many web sites suggest using the key value to determine one of the objects and object to determine the other one. Using trace 1222 is not an option
The object info is not problem: OBJECT: 7:1415676091:4 the object id is obvious
According to the web sites the key should be no problem either; but, in my error log the key value is not like a standard object id. It is like
KEY: 7:72057594119454720
This is not any object in my database. How do I find out what the object is?
Deadlock encountered .... Printing deadlock information
2012-08-27 17:16:24.58 spid8s Wait-for graph
2012-08-27 17:16:24.58 spid8s
2012-08-27 17:16:24.58 spid8s Node:1
2012-08-27 17:16:24.58 spid8s KEY: 7:72057594119454720 (341cb463bd2a) CleanCnt:2 Mode:X Flags: 0x1
2012-08-27 17:16:24.58 spid8s Grant List 1:
2012-08-27 17:16:24.58 spid8s Owner:0x000000012F7F6140 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:106 ECID:0 XactLockInfo: 0x000000011C0D03F0
2012-08-27 17:16:24.58 spid8s SPID: 106 ECID: 0 Statement Type: INSERT Line #: 1
2012-08-27 17:16:24.58 spid8s Input Buf: Language Event: (@p0 int,@p1 datetime,@p2 varchar(8000),@p3 varchar(8000),@p4 varchar(8000),@p5 varchar(8000),@p6 varchar(8000),@p7 varchar(8000),@p8 varchar(8000),@p9 varchar(8000),@p10 varchar(8000),@p11 varchar(8000),@p12 int,@p13 varchar(8000),@p14 varchar(8000),@p15
2012-08-27 17:16:24.58 spid8s Requested by:
2012-08-27 17:16:24.58 spid8s ResType:LockOwner Stype:'OR'Xdes:0x000000016A4EA1C0 Mode: S SPID:73 BatchID:0 ECID:0 TaskProxy:(0x0000000173F2E540) Value:0x27a05200 Cost:(0/0)
2012-08-27 17:16:24.58 spid8s
t:(0/0)
All Replies
-
Wednesday, August 29, 2012 7:16 AM
Ron,
Please try SELECT DB_NAME(7)
USE <your db name>;
GO
SELECT name from sys.objects WHERE object_id = 72057594119454720
SELECT name from sys.indexes WHERE object_id = 72057594119454720
It seem your select statement (SPID 73) lock the insert statement (SPID 106)
There are some rarely cases that select statement could lock insert statement,
- Share Lock has been grant on TABLE level locking
It may came from many reason, including no proper indexes or high level of transaction isolation level. -
Wednesday, August 29, 2012 7:19 AMWould you please copy entire trace information or involved statements.
-
Wednesday, August 29, 2012 7:24 AM
HI
KEY. Identifies the key range within an index on which a lock is held or requested.
KEY is represented as KEY: db_id:hobt_id (index key hash value). For example, KEY: 6:72057594057457664 (350007a4d329).Hope this helps..
For more info ..
http://msdn.microsoft.com/en-us/library/ms178104%28v=sql.105%29.aspx
vt
Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
-
Wednesday, August 29, 2012 7:29 AMModeratorWhy not use 1222 which can give you better details? SQL Server Product team has been reported that 1204 is not easy to understand and that's why they added 1222 trace flag.
Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
--------------------------------------------------------------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------------------------------------------------------------------
My Blog | Team Blog | @Twitter -
Wednesday, August 29, 2012 4:28 PMThe select statements returned no value
-
Wednesday, August 29, 2012 4:48 PM
Identifying the KEY: value as a hobt_id gave me enough info to Google the answer.
Thanks V.VT
use [db name]
go
select o.name from
sys.partitions p
join sys.objects o on P.object_id = o.object_id
where hobt_id = [insert hobt here]
.png)
