Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Deadlock graph shredding (скорее вопрос с XQuery и XPath)  [new]
x1sf
Member

Откуда:
Сообщений: 10
Пытаюсь сделать скрипт который бы парсил deadlock graph.
Возникла проблема с получением имен заблокированных ресурсов - из <process-list> получается вынуть имя ресурса только в случае блокировки ключа. Нужно получить имя заблокированного ресурса из другой части графа - <resource-list>.
Кто может помочь с постороением правильного пути для метода XQuery value?

Спасибо.
SELECT 
	X.x.value('(../@id)','sysname') as processid,																							
	CASE 
		WHEN Deadlockgraph.value('(/TextData/deadlock-list/deadlock/@victim)[1]','sysname') = X.x.value('(../@id)','sysname') 
		THEN 1		--1 if victim process
		ELSE 0
	END AS vicid,
	Deadlockgraph.value('count(/TextData/deadlock-list/deadlock/process-list/process)', 'int') as NumOfProcInv,
	DB_NAME(X.x.value('(../@currentdb)','sysname')) as databasename,																	
	X.x.value('(../@waitresource)','sysname') as resourceid,																			
	CASE 
		WHEN X.x.value('(../@waitresource)','sysname') like 'KEY:%'
		THEN
			(SELECT CONCAT(sc.name, so.name, si.name)
			FROM sys.partitions AS p
			JOIN sys.objects as so on 
				p.object_id=so.object_id
			JOIN sys.indexes as si on 
				p.index_id=si.index_id and 
				p.object_id=si.object_id
			JOIN sys.schemas AS sc on 
				so.schema_id=sc.schema_id
			WHERE hobt_id = convert(bigint, RTRIM(SUBSTRING(X.x.value('(../@waitresource)','sysname'), CHARINDEX(':', X.x.value('(../@waitresource)','sysname'), 
			CHARINDEX(':', X.x.value('(../@waitresource)','sysname')) + 1) + 1, CHARINDEX('(', X.x.value('(../@waitresource)','sysname')) - CHARINDEX(':', X.x.value('(../@waitresource)','sysname'), 
			CHARINDEX(':', X.x.value('(../@waitresource)','sysname')) + 1) - 1))))
		ELSE
		'N/A'
	END AS resourcename,
	X.x.value('(frame/@procname)[1]','sysname') as procname,																		
	X.x.query('data(frame)') as frame,																									
	X.x.query('data(../inputbuf)') as inputnbuffer,																							 
        X.x.value('(../@lastbatchstarted)','sysname') as lastbatchstarted,
	X.x.value('(../@lastbatchcompleted)','sysname') as lastbatchcompleted,
	X.x.value('(../@hostname)','sysname') as hostname,																							
	X.x.value('(../@loginname)','sysname') as loginname,																						
	X.x.value('(../@clientapp)','sysname') as clientapp,																						
	X.x.value('(../@isolationlevel)','sysname') as isolationlevel,
	X.x.value('(../@lockMode)','sysname') as lockmode,
	X.x.value('(frame/@sqlhandle)[1]','varchar(max)') as sqlhandle,																				
	(SELECT [Text] FROM  sys.dm_exec_sql_text(CONVERT ( varbinary(64), X.x.value('(frame/@sqlhandle)[1]','varchar(max)'), 1))) as sqltext		
FROM DeadlockEvents
CROSS APPLY Deadlockgraph.nodes('/TextData/deadlock-list/deadlock/process-list/process/executionStack') AS X(x) 
ORDER BY  lastbatchstarted DESC
25 июн 18, 18:46    [21519576]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock graph shredding (скорее вопрос с XQuery и XPath)  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
x1sf
Нужно получить имя заблокированного ресурса из другой части графа - <resource-list>.
Что для вас есть "имя ресурса"?
25 июн 18, 21:56    [21519989]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock graph shredding (скорее вопрос с XQuery и XPath)  [new]
x1sf
Member

Откуда:
Сообщений: 10
В приложенном скриншоте выдели атрибуты по которым нужно связать ноды <process list> и <resource list>.

К сообщению приложен файл. Размер - 149Kb
25 июн 18, 22:01    [21519998]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock graph shredding (скорее вопрос с XQuery и XPath)  [new]
x1sf
Member

Откуда:
Сообщений: 10
invm,
Атрибут <object name> из ноды <resource list>, выше скриншот повесил.
25 июн 18, 22:03    [21520002]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock graph shredding (скорее вопрос с XQuery и XPath)  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
x1sf,

declare @x xml = N'...';

select
 a.n.value('local-name(.)', 'nvarchar(100)'),
 a.n.value('@objectname', 'nvarchar(100)'),
 c.pid,
 d.n.query('.')
from
 @x.nodes('//resource-list/*[@objectname]') a(n) cross apply
 a.n.nodes('owner-list/owner') b(n) cross apply
 (select b.n.value('@id', 'nvarchar(100)')) c(pid) cross apply
 @x.nodes('//process[@id = sql:column("c.pid")]') d(n);
?
25 июн 18, 22:39    [21520076]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock graph shredding (скорее вопрос с XQuery и XPath)  [new]
x1sf
Member

Откуда:
Сообщений: 10
invm,
Я туплю как это можно встроить в ветвь ELSE
CASE 
		WHEN X.x.value('(../@waitresource)','sysname') like 'KEY:%'
		THEN
			(SELECT CONCAT(sc.name, so.name, si.name)
			FROM sys.partitions AS p
			JOIN sys.objects as so on 
				p.object_id=so.object_id
			JOIN sys.indexes as si on 
				p.index_id=si.index_id and 
				p.object_id=si.object_id
			JOIN sys.schemas AS sc on 
				so.schema_id=sc.schema_id
			WHERE hobt_id = convert(bigint, RTRIM(SUBSTRING(X.x.value('(../@waitresource)','sysname'), CHARINDEX(':', X.x.value('(../@waitresource)','sysname'), 
			CHARINDEX(':', X.x.value('(../@waitresource)','sysname')) + 1) + 1, CHARINDEX('(', X.x.value('(../@waitresource)','sysname')) - CHARINDEX(':', X.x.value('(../@waitresource)','sysname'), 
			CHARINDEX(':', X.x.value('(../@waitresource)','sysname')) + 1) - 1))))
		ELSE
		'N/A'
25 июн 18, 23:11    [21520161]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock graph shredding (скорее вопрос с XQuery и XPath)  [new]
x1sf
Member

Откуда:
Сообщений: 10
invm,
Я туплю как это можно встроить в ветвь ELSE - что то в таком духе
CASE 
		WHEN X.x.value('(../@waitresource)','sysname') like 'KEY:%'
		THEN
	
		ELSE
		select objectname
                from <resource-list>
                where id.<process list> = id.<resource list>


P.S. а сообщения тут редактировать нельзя?
25 июн 18, 23:22    [21520190]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock graph shredding (скорее вопрос с XQuery и XPath)  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
x1sf, к сожалению, я не нашел способа использовать XPath функцию current(), чтобы сделать такой подзапрос, поэтому единственный вариант, который вижу, это такое извращение:
declare @graph xml = '<deadlock-list>
 <deadlock victim="process20f511868">
  <process-list>
   <process id="process20f511868" taskpriority="0" logused="0" waitresource="KEY: 5:72057598246125568 (18dfaa420d8a)" waittime="1656" ownerId="146507673519" 
transactionname="user_transaction" lasttranstarted="2018-01-21T11:15:31.693" XDES="0xc6cb93d6a8" lockMode="U" schedulerid="44" kpid="25224" status="suspended" 
spid="2118" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-01-21T11:15:31.693" lastbatchcompleted="2018-01-21T11:15:31.693" 
lastattention="1900-01-01T00:00:00.693" clientapp="..." hostpid="1074123192" loginname="..." isolationlevel="read committed (2)" xactid="146507673519" 
currentdb="5" lockTimeout="4294967295" clientoption1="673384544" clientoption2="128056">
    <executionStack>...
    </executionStack>
    <inputbuf>
Proc [Database Id = 5 Object Id = 1841165411]    </inputbuf>
   </process>
   <process id="process20f550cf8" taskpriority="0" logused="4736" waitresource="KEY: 5:72057598343053312 (e1a8fd993334)" waittime="1579" ownerId="146507673191" 
transactionname="user_transaction" lasttranstarted="2018-01-21T11:15:31.680" XDES="0x9cc01636a8" lockMode="U" schedulerid="52" kpid="12328" status="suspended" 
spid="983" sbid="0" ecid="0" priority="0" trancount="3" lastbatchstarted="2018-01-21T11:15:31.680" lastbatchcompleted="2018-01-21T11:15:31.680" 
lastattention="1900-01-01T00:00:00.680" clientapp="..." hostpid="1074123192" loginname="..." isolationlevel="read committed (2)" xactid="146507673191" 
currentdb="5" lockTimeout="4294967295" clientoption1="673253472" clientoption2="128056">
    <executionStack>
    ...
    </executionStack>
    <inputbuf>
Proc [Database Id = 5 Object Id = 1841165411]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057598246125568" dbid="5" objectname="Table1" indexname="Index1" id="lock360fce5400" mode="X" associatedObjectId="72057598246125568">
    <owner-list>
     <owner id="process20f550cf8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process20f511868" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057598343053312" dbid="5" objectname="Table2" indexname="Index2" id="lock36280ac900" mode="U" associatedObjectId="72057598343053312">
    <owner-list>
     <owner id="process20f511868" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process20f550cf8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
'

select t.processid
       , X.x.value('(keylock[owner-list/owner/@id = sql:column("processid")]/@objectname)[1]', 'sysname') as objectname
       , X.x.value('(keylock[owner-list/owner/@id = sql:column("processid")]/@indexname)[1]', 'sysname') as indexname
from 
        (
            select  X.x.value('(../@id)','sysname') as processid
            from    @graph.nodes('/deadlock-list/deadlock/process-list/process/executionStack') AS X(x) 
            ) as t
        cross apply @graph.nodes('/deadlock-list/deadlock/resource-list') AS X(x) 
26 июн 18, 11:13    [21521215]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock graph shredding (скорее вопрос с XQuery и XPath)  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5619
Minamoto
я не нашел способа использовать XPath функцию current()

И не найдете, т.к. current() - это функция XSLT, и в чистом XPath (вне XSLT) использована быть не может (т.к. выражение выбора должно быть инвариантно относительно пути обработки).
26 июн 18, 12:44    [21521650]     Ответить | Цитировать Сообщить модератору
 Re: Deadlock graph shredding (скорее вопрос с XQuery и XPath)  [new]
x1sf
Member

Откуда:
Сообщений: 10
Minamoto,
получилось не очень красиво, но работает. Только procname возвращается как NULL, frame, sqlhandle просто пустые. Не понимаю как путь для них прописать.
SELECT t.processid,t.vicid,t.NumOfProcInv,t.databasename,t.resourceid
	   ,X.x.value('(keylock[owner-list/owner/@id = sql:column("processid")]/@objectname)[1]', 'sysname') AS objectname
           ,X.x.value('(keylock[owner-list/owner/@id = sql:column("processid")]/@indexname)[1]', 'sysname') AS indexname
	   ,X.x.value('(pagelock[owner-list/owner/@id = sql:column("processid")]/@objectname)[1]', 'sysname') AS objectname1
	   ,t.procname,t.frame,t.inputnbuffer,t.spid,t.sbid,t.trancount,t.logused,t.lasttranstarted,t.lastbatchstarted
	   ,t.lastbatchcompleted,t.timet,t.hostname,t.loginname,t.clientapp,t.isolationlevel,t.lockmode,t.locktimeout
	   ,t.sqlhandle,t.sqltext
FROM
        (
        SELECT  X.x.value('(@id)','sysname') AS processid
		,CASE 
			WHEN @graph.value('(deadlock-list/deadlock/@victim)[1]','sysname') = X.x.value('(@id)','sysname') 
			THEN 1		--1 if victim process
			ELSE 0
		END AS vicid
		,@graph.value('count(/deadlock-list/deadlock/process-list/process)', 'int') as NumOfProcInv
		,DB_NAME(X.x.value('(@currentdb)','sysname')) as databasename																	
		,X.x.value('(@waitresource)','sysname') as resourceid																			
		,X.x.value('(executionstack/frame/@procname)[1]','sysname') as procname																--stored procedure name
		,X.x.query('data(/executionstack/frame)') as frame																									--T-SQL string
		,X.x.query('data(inputbuf)') as inputnbuffer																						--T-SQL string
		,X.x.value('(@spid)','sysname') as spid																							    --SQL Server session id
		,X.x.value('(@sbid)','sysname') as sbid
		,X.x.value('(@trancount)','sysname') as trancount																				
		,X.x.value('(@logused)','sysname') as logused
		,X.x.value('(@lasttranstarted)','sysname') as lasttranstarted
		,X.x.value('(@lastbatchstarted)','sysname') as lastbatchstarted
		,X.x.value('(@lastbatchcompleted)','sysname') as lastbatchcompleted
		,CASE
			WHEN X.x.value('(@lasttranstarted)','sysname') IS NULL 
			THEN DATEDIFF(MILLISECOND, X.x.value('(@lastbatchstarted)','sysname'), X.x.value('(@lastbatchcompleted)','sysname'))
			ELSE DATEDIFF(MILLISECOND, X.x.value('(@lasttranstarted)','sysname'), X.x.value('(@lastbatchcompleted)','sysname'))
		END AS timet
		,X.x.value('(@hostname)','sysname') as hostname																						    
		,X.x.value('(@loginname)','sysname') as loginname																						
		,X.x.value('(@clientapp)','sysname') as clientapp																						 
		,X.x.value('(@isolationlevel)','sysname') as isolationlevel
		,X.x.value('(@lockMode)','sysname') as lockmode
		,X.x.value('(@lockTimeout)','sysname') as locktimeout
		,X.x.value('(frame/@sqlhandle)[1]','varchar(max)') as sqlhandle																				--SQL handle
		,(SELECT [Text] FROM  sys.dm_exec_sql_text(CONVERT ( varbinary(64), X.x.value('(frame/@sqlhandle)[1]','varchar(max)'), 1))) as sqltext		--SQL query text
FROM @graph.nodes('/deadlock-list/deadlock/process-list/process') AS X(x) 
) as t
CROSS APPLY @graph.nodes('/deadlock-list/deadlock/resource-list') AS X(x)
26 июн 18, 19:21    [21523147]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить