Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Sql Server Deadlock Object IDs are too large

Tags:

sql-server

deadlock

I am trying to trace a deadlock that is occurring in our SQL 2005 database (64-bit). We do not currently have snapshot isolation enabled.

I turned on tf-1204 and received the output below.

From this output I am able to determine that Node 1 is a stored procedure that selects data and only modifies values in #temp tables.

Node 2 is another stored procedure does a simple primary key based update on a single row of data.

What I can't determine is the actual resource that was in contention here. The Keys of 10:72057594060734464 and 10:72057594038910976 allow me to determine the database, but these object ids cannot be resolved with object_name. In fact, they should be int values, so I'm not sure where these large numbers are coming from.

In researching the problem I was able to get similar values out of Activity Monitor for Object ID as well.

How do I resolve these object identifiers?

Here is the deadlock tf-1204 output:

2008-12-05 07:48:28.19 spid4s      ----------------------------------
2008-12-05 07:48:28.19 spid4s      Starting deadlock search 634
2008-12-05 07:48:28.19 spid4s      Target Resource Owner:
2008-12-05 07:48:28.19 spid4s       ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980
2008-12-05 07:48:28.19 spid4s      0:Insert new node: Node:1     ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980
2008-12-05 07:48:28.19 spid4s      1:SearchOR Considering new blocker - task: 0000000000EC5198, Worker 00000000C89881C0
2008-12-05 07:48:28.19 spid4s      2:Insert new node: Node:2     ResType:LockOwner Stype:'OR'Xdes:0x00000000808F1A80 Mode: S SPID:79 BatchID:0 ECID:0 TaskProxy:(0x0000000129E82598) Value:0x1063d000
2008-12-05 07:48:28.19 spid4s      3:SearchOR Considering new blocker - task: 0000000000C3FC18, Worker 00000000F847C1C0
2008-12-05 07:48:28.19 spid4s      4:InsertKnown Cycle found between old res owner: [ ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980] and new res owner [ ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980]
2008-12-05 07:48:28.19 spid4s      4:InsertKnown search result: Deadlock found (blocking owner is on a stack)
2008-12-05 07:48:28.19 spid4s      3:SearchOR search result: Deadlock found (cycle on this level or before)
2008-12-05 07:48:28.19 spid4s      1:SearchOR search result: Deadlock found (cycle on this level or before)
2008-12-05 07:48:28.19 spid4s      
2008-12-05 07:48:28.19 spid4s      Deadlock cycle was encountered .... verifying cycle
2008-12-05 07:48:28.19 spid4s      0:Insert new node: Node:1     ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980 Cost:(0/1544)
2008-12-05 07:48:28.19 spid4s      1:SearchOR Considering new blocker - task: 0000000000EC5198, Worker 00000000C89881C0
2008-12-05 07:48:28.19 spid4s      2:Insert new node: Node:2     ResType:LockOwner Stype:'OR'Xdes:0x00000000808F1A80 Mode: S SPID:79 BatchID:0 ECID:0 TaskProxy:(0x0000000129E82598) Value:0x1063d000 Cost:(0/0)
2008-12-05 07:48:28.19 spid4s      3:SearchOR Considering new blocker - task: 0000000000C3FC18, Worker 00000000F847C1C0
2008-12-05 07:48:28.19 spid4s      4:InsertKnown Cycle found between old res owner: [ ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980 Cost:(0/1544)] and new res owner [ ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980 Cost:(0/1544)]
2008-12-05 07:48:28.19 spid4s      4:InsertKnown search result: Deadlock found (blocking owner is on a stack)
2008-12-05 07:48:28.19 spid4s      3:SearchOR search result: Deadlock found (cycle on this level or before)
2008-12-05 07:48:28.19 spid4s      1:SearchOR search result: Deadlock found (cycle on this level or before)
2008-12-05 07:48:28.19 spid4s      Deadlock encountered .... Printing deadlock information
2008-12-05 07:48:28.19 spid4s      Wait-for graph
2008-12-05 07:48:28.19 spid4s      
2008-12-05 07:48:28.19 spid4s      Node:1

2008-12-05 07:48:28.19 spid4s      KEY: 10:72057594060734464 (c80089667602) CleanCnt:3 Mode:S Flags: 0x0
2008-12-05 07:48:28.19 spid4s       Grant List 1:
2008-12-05 07:48:28.19 spid4s         Owner:0x000000011063CDC0 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:79 ECID:0 XactLockInfo: 0x00000000808F1AB8
2008-12-05 07:48:28.19 spid4s         SPID: 79 ECID: 0 Statement Type: INSERT Line #: 220
2008-12-05 07:48:28.19 spid4s         Input Buf: RPC Event: Proc [Database Id = 10 Object Id = 1751794144]
2008-12-05 07:48:28.19 spid4s       Requested By: 
2008-12-05 07:48:28.19 spid4s         ResType:LockOwner Stype:'OR'Xdes:0x00000000813B8700 Mode: X SPID:77 BatchID:0 ECID:0 TaskProxy:(0x00000000CE6D8598) Value:0x1043f980 Cost:(0/1544)
2008-12-05 07:48:28.19 spid4s      
2008-12-05 07:48:28.19 spid4s      Node:2

2008-12-05 07:48:28.19 spid4s      KEY: 10:72057594038910976 (0c0092f62b82) CleanCnt:2 Mode:X Flags: 0x0
2008-12-05 07:48:28.19 spid4s       Grant List 0:
2008-12-05 07:48:28.19 spid4s         Owner:0x000000011043F300 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0 XactLockInfo: 0x00000000813B8738
2008-12-05 07:48:28.19 spid4s         SPID: 77 ECID: 0 Statement Type: UPDATE Line #: 23
2008-12-05 07:48:28.19 spid4s         Input Buf: RPC Event: Proc [Database Id = 10 Object Id = 1791462302]
2008-12-05 07:48:28.19 spid4s       Requested By: 
2008-12-05 07:48:28.19 spid4s         ResType:LockOwner Stype:'OR'Xdes:0x00000000808F1A80 Mode: S SPID:79 BatchID:0 ECID:0 TaskProxy:(0x0000000129E82598) Value:0x1063d000 Cost:(0/0)
2008-12-05 07:48:28.19 spid4s      
2008-12-05 07:48:28.19 spid4s      Victim Resource Owner:
2008-12-05 07:48:28.19 spid4s       ResType:LockOwner Stype:'OR'Xdes:0x00000000808F1A80 Mode: S SPID:79 BatchID:0 ECID:0 TaskProxy:(0x0000000129E82598) Value:0x1063d000 Cost:(0/0)
2008-12-05 07:48:28.19 spid4s      End deadlock search 634 ... a deadlock was found.
2008-12-05 07:48:28.19 spid4s      ----------------------------------
like image 916
user44724 Avatar asked Dec 09 '08 20:12

user44724

People also ask

How can SQL Server reduce deadlocks?

Useful ways to avoid and minimize SQL Server deadlocksTry to keep transactions short; this will avoid holding locks in a transaction for a long period of time. Access objects in a similar logical manner in multiple transactions. Create a covering index to reduce the possibility of a deadlock.

How do you fix a deadlock?

Deadlock frequency can sometimes be reduced by ensuring that all applications access their common data in the same order - meaning, for example, that they access (and therefore lock) rows in Table A, followed by Table B, followed by Table C, and so on.

What causes deadlock in SQL Server?

A SQL Server deadlock occurs when exclusive locks are held on resources required by multiple processes and those processes cannot continue to completion.

How do I monitor SQL Server deadlocks?

View a graphical representation of the deadlock eventOpen the deadlock event in Event Viewer. On the General tab, go to the SQL Server deadlock xml report section, and then copy the text in the deadlock tag (including the start and end tag) to a text editor such as Notepad or Visual Studio Code. Save the file as a .

1 Answers

These IDs actually refer to hobts (Heap Or Binary Tree), found in sys.partitions. Try the following query inside database 10, and you will find which object and which index is affected.

SELECT hobt_id, object_name(p.[object_id]), index_id 
FROM sys.partitions p 
WHERE hobt_id = 72057594060734464
like image 100
Jim McLeod Avatar answered Oct 06 '22 00:10

Jim McLeod
Sign in to Comment

Related questions

How to use regular expression in TSQL?
How to get Enterprise Manager installed in SQL Server 2008 Express?
SQL Server: Cannot open database requested by the login
How to rotate a table horizontally in sql server
Database Function VS Case Statement
upsize_ts column in legacy database - what does it mean?
Language which compiles to t-sql
Inserting into table with a Default column value
BCP returns no errors, but also doesn't copy any rows
Select Query from 3 tables with foreign keys
Managing user profiles with SimpleMembership/Sql Server CE, MongoDB
FIFO Implementation in Inventory using SQL
mysqli extension is missing debian
Converting Boolean to Integer SQl Server
SQL Server, combining % and [] wildcards in LIKE statement
Does the order of columns in an multicolumn non-clustered index matter in SQL Server?
Is it possible to commit/rollback SqlTransaction in asynchronous?
Drop indexes or alter
SQL Server Round Robin Tournament
How to get a table of dates between x and y in sql server 2005

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!