June 23, 2009 at 10:22 pm
I'm quite new to deadlocks.
Can lock mode U and X co-exists on same object by different process? In the below screen shot, it seems a same index is held X and U by different process. But according to the Lock Compatibility table, this should not happen.
And is there something I can do to avoid this kind of deadlocks?
The DML is a simply insert, but the table has a AFTER INSERT trigger which updates the whole table.
Thanks.
June 24, 2009 at 4:26 am
Sheng (6/23/2009)
In the below screen shot, it seems a same index is held X and U by different process. But according to the Lock Compatibility table, this should not happen.And is there something I can do to avoid this kind of deadlocks?
The DML is a simply insert, but the table has a AFTER INSERT trigger which updates the whole table.
Thanks.
Hey Sheng,
The deadlock graph shows a couple of key things: the lock that a process already has (owner mode) and the lock it is trying to acquire (request mode). You should be able to see that one process is requesting a lock which is incompatible with that already held by a second process, and in turn, the second process already holds a lock which is incompatiable with one already held by the first process. This is a deadlock - the only way for either process to make progress is for the other to be rolled back. More than two processes with locks can combine to produce a deadlock situation.
There is a great guide to interpreting deadlock graphs here[/url]
It seems unusual to have a trigger that would update the entire table - can you provide some more detail on this?
In the meantime, the Books Online entry entitled Minimizing Deadlocks can be found here
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 24, 2009 at 4:37 am
Thank you Paul for your interpretion.
If an resource is held X by process A, how can it be held U by process B again? That's my confusion.
This trigger is used to deal a common seen scenario. There's a column storing a string need to be sort semantically:
"A1B1"
"A2B1"
...
"A9B1"
"A10B1"
"A10B2"
...
"A10B10"
The trigger caculate the right order and materialize it into the table, because caculate it on the fly exceeds the response time requirement.
Without the trigger, there's no deadlock anymore:)
June 24, 2009 at 5:34 am
The deadlock graph you provided shows only the HoBt (heap or B-tree) ID involved - which for SQL 2005 will be the same as the partition ID in sys.partitions. The HoBt here is the same as the associated object ID which is the table (or partition) containing the lock - it does not uniquely identify the row lock!
BTW. you can use the following query to find object details from the HoBt:
SELECT OBJECT_NAME(p.[object_id]) AS ObjectName
,p.[object_id] AS ObjectID
,i.name AS IndexName
,hobt_id AS HoBTID
,partition_id AS PartitionID
,partition_number AS PartitionNo
FROM sys.partitions p
JOIN sys.indexes i
ON p.index_id = i.index_id
AND p.[object_id] = i.[object_id]
WHERE hobt_id = {insert HoBt value here};
Essentially, the deadlock graph is missing the lock resource hash value and wait resource string - these would identify the row locks involved. If you save the deadlock graph as an .xdl file (it's just xml) the information will be in there. This occasional lack of information is one reason why some people prefer to diagnose deadlocks with trace flags.
If you post full DDL for the table, indexes, and the trigger, one of us would be happy to look at ways of preventing the deadlock, or possibly improving the process. Same data creation statements and expected output will maximize your chances of people choosing to spend their spare time on it 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 24, 2009 at 5:46 am
Oh, while I think of it, one thing you might find interesting is that the 'wait resource' associated with a lock - a string like (0801c4e1f5e4) for example - can be looked up fairly directly using the undocumented lockres function:
DECLARE @a TABLE (A INT PRIMARY KEY);
INSERT @a (A) VALUES (1), (2), (3), (4), (5);
SELECT %%lockres%% AS lock_hash_value, A
FROM @a
ORDER BY %%lockres%%
Output on my system:
[font="Courier New"](010086470766)1
(020068e8b274)2
(03000d8f0ecc)3
(0400b4b7d951)4
(0500d1d065e9)5[/font]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 24, 2009 at 5:48 am
Over here you know the HOBT id , object id and index name .but you do not know the rowid or page id etc .So you know that update is waiting for update and update is waiting for X-clusive.Thats it.These locks can be on the same extent or page but on different rows or might be on the entire table .You need use trace flag 1222 to get the deadlock graph in the errorlogs.
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 24, 2009 at 6:17 am
MSDN: Detecting and Ending Deadlocks contains full information on the trace flags mentioned.
Unsure where Abhay has seen a table-level UPDATE lock.
Also unsure how extent locks (which are only used for new allocations) might participate in your deadlock scenario.
😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply