Waitresource Format Explanation in Deadlock XML for SQL Server

deadlocksql server

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?

Best Answer

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"
Related Question