2

I have a deadlock xml where two transactions are deadlocked on two resources. waitresource attributes of the process elements have values "OBJECT: 8:1269579561:4" and "OBJECT: 8:1269579561:0".

It looks like table objects locks but what are the third parts in the object description: ":4" and ":0"? Where can I find detailed explanation of the waitresource attribute?

2

It's the Lock Partition

For large computer systems, locks on frequently referenced objects can become a performance bottleneck as acquiring and releasing locks place contention on internal locking resources. Lock partitioning enhances locking performance by splitting a single lock resource into multiple lock resources. This feature is only available for systems with 16 or more CPUs, and is automatically enabled and cannot be disabled. Only object locks can be partitioned.

You can see this by comparing the deadlock message in the errorlog with the deadlock xml in the system_health XEvents session, which you can query like this:

SELECT 
    CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
    timestamp_utc, datediff(s,timestamp_utc,GETUTCDATE()) sec_ago
FROM sys.fn_xe_file_target_read_file(
        cast(SERVERPROPERTY('ErrorLogFileName') as varchar(max)) + 
        '\..\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report';

From the errorlog:

 Message OBJECT: 2:1013578649:15        CleanCnt:2 Mode:IS Flags: 0x1

from the deadlock xml:

 <objectlock lockPartition="15" objid="1013578649" 
  • I thought so but then found similar thread here: social.msdn.microsoft.com/Forums/sqlserver/en-US/… so now not sure – yurish Nov 1 '18 at 15:38
  • 1
    Since it was Kalen Delaney who didn't think it was the lock partition, I provisioned a 16 core VM in Azure and tested by generating a IS/Sch-M deadlock. It is. – David Browne - Microsoft Nov 1 '18 at 18:00
  • That thread has a lot of misinformation then yes? The accepted answer and the previous accepted answer on the msdn link both are apparently incorrect. – Jacob H Nov 1 '18 at 18:04
  • Yep. I added another answer there. – David Browne - Microsoft Nov 1 '18 at 18:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.