sql-server – 如何将SQL Server死锁报告中的密钥转换为值？

我有一个死锁报告,告诉我有一个涉及waitresource =“KEY：9：72057632651542528(543066506c7c)”的冲突,我可以看到：

<keylock hobtid="72057632651542528" dbid="9" objectname="MyDatabase.MySchema.MyTable" indexname="MyPrimaryKeyIndex" id="locka8c6f4100" mode="X" associatedObjectId="72057632651542528">

在< resource-list>内我希望能够找到密钥的实际值(例如,id = 12345).我需要使用什么SQL语句来获取该信息？

解决方法

来自@ Kin,@ AaronBertrand和@DBAFromTheCold的答案非常棒,非常有帮助.我在测试期间发现的另一个重要信息是,在查找%% lockres %%时(通过索引查询提示),您需要使用sys.partitions为给定HOBT_ID返回的索引. .此索引并不总是PK或聚簇索引.

例如：

--Sometimes this does not return the correct results.
SELECT lockResKey = %%lockres%%,* 
FROM [MyDB].[dbo].[myTable]  
WHERE %%lockres%% = @lockres
;
--But if you add the index query hint,it does return the correct results
SELECT lockResKey = %%lockres%%,* 
FROM [MyDB].[dbo].[myTable] WITH(NOLOCK INDEX([IX_MyTable_NonClustered_index]))  
WHERE %%lockres%% = @lockres
;

这是使用来自每个答案的片段修改的示例脚本.

declare @keyValue varchar(256);
SET @keyValue = 'KEY: 5:72057598157127680 (92d211c2a131)' --Output from deadlock graph: process-list/process[waitresource] -- CHANGE HERE !
------------------------------------------------------------------------
--Should not have to change anything below this line: 
declare @lockres nvarchar(255),@hobbitID bigint,@dbid int,@databaseName sysname;
--.............................................
--PARSE @keyValue parts:
SELECT @dbid = LTRIM(SUBSTRING(@keyValue,CHARINDEX(':',@keyValue) + 1,@keyValue,@keyValue) + 1) - (CHARINDEX(':',@keyValue) + 1) ));
SELECT @hobbitID = convert(bigint,RTRIM(SUBSTRING(@keyValue,@keyValue) + 1) + 1,CHARINDEX('(',@keyValue) - CHARINDEX(':',@keyValue) + 1) - 1)));
SELECT @lockRes = RTRIM(SUBSTRING(@keyValue,@keyValue) + 0,CHARINDEX(')',@keyValue) - CHARINDEX('(',@keyValue) + 1));
--.............................................
--Validate DB name prior to running dynamic SQL
SELECT @databaseName = db_name(@dbid);  
IF not exists(select * from sys.databases d where d.name = @databaseName)
BEGIN
    RAISERROR(N'Database %s was not found.',16,1,@databaseName);
    RETURN;
END

declare @objectName sysname,@indexName sysname,@schemaName sysname;
declare @ObjectLookupSQL as nvarchar(max) = '
SELECT @objectName = o.name,@indexName = i.name,@schemaName = OBJECT_SCHEMA_NAME(p.object_id,@dbid)
FROM ' + quotename(@databaseName) + '.sys.partitions p
JOIN ' + quotename(@databaseName) + '.sys.indexes i ON p.index_id = i.index_id AND p.[object_id] = i.[object_id]
JOIN ' + quotename(@databaseName)+ '.sys.objects o on o.object_id = i.object_id
WHERE hobt_id = @hobbitID'
;
--print @ObjectLookupSQL
--Get object and index names
exec sp_executesql @ObjectLookupSQL,N'@dbid int,@objectName sysname OUTPUT,@indexName sysname OUTPUT,@schemaName sysname OUTPUT',@dbid = @dbid,@hobbitID = @hobbitID,@objectName = @objectName output,@indexName = @indexName output,@schemaName = @schemaName output
;

DECLARE @fullObjectName nvarchar(512) = quotename(@databaseName) + '.' + quotename(@schemaName) + '.' + quotename(@objectName);
SELECT fullObjectName = @fullObjectName,lockIndex = @indexName,lockRes_key = @lockres,hobt_id = @hobbitID,waitresource_keyValue = @keyValue;

--Validate object name prior to running dynamic SQL
IF OBJECT_iD( @fullObjectName) IS NULL 
BEGIN
    RAISERROR(N'The object "%s" was not found.',@fullObjectName);
    RETURN;
END

--Get the row that was blocked
--NOTE: we use the NOLOCK hint to avoid locking the table when searching by %%lockres%%,which might generate table scans.
DECLARE @finalResult nvarchar(max) = N'SELECT lockResKey = %%lockres%%,* 
FROM ' + @fullObjectName
+ ISNULL(' WITH(NOLOCK INDEX(' + QUOTENAME(@indexName) + ')) ','')  
+ ' WHERE %%lockres%% = @lockres'
;

--print @finalresult
EXEC sp_executesql @finalResult,N'@lockres nvarchar(255)',@lockres = @lockres;