Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как узнать кто второй участник блокировки?  [new]
BERSERC
Member

Откуда:
Сообщений: 442
Есть таблица , A, B,C , каждая имеет вторичный ключе предыдущей. И при удалении из A идет удаление из всех трех таблиц через каскадное удаление.


параллельно работают запросы merge таблиц A,B,C

и запрос на удаление из таблицы A по первичному ключу.

Собственно запрос на удаление часто вылетает с текстом "Transaction (Process ID 359) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

И не могу понять какой именно megre и на какой таблице блокирует удаление. Можно ил отследить кто был вторым участником блокировки?
5 апр 16, 15:02    [19019306]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
BERSERC,

https://msdn.microsoft.com/ru-ru/library/ms190465(v=sql.120).aspx
5 апр 16, 15:05    [19019324]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
o-o
Guest
Можно графы в еррорлог сохранять, TF 1222,
может, даже уже сохраняются, проверьте startup-параметры
5 апр 16, 15:18    [19019395]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
BERSERC,

в XE сессию system_health пишутся deadlock'и

+ deadlock graph from system_health
declare
	@file_path nvarchar(max);

select
	@file_path = cast(xet.target_data as xml).value('(/EventFileTarget/File/@name)[1]', 'nvarchar(max)')
from 
	sys.dm_xe_sessions xe
		inner join
	sys.dm_xe_session_targets xet on xe.[address] = xet.event_session_address
where
	xe.name = N'system_health'
	and xet.target_name = N'event_file';

with deadlock_events as
(
	select
		cast(event_data as xml) as event_data
	from 
		sys.fn_xe_file_target_read_file
		(
			@file_path, 
			null, null, null
		) xef
	where
		xef.[object_name] = N'xml_deadlock_report'
)
select
	de.event_data.value('(/event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime') as time_stamp,
	de.event_data.query('/event[@name="xml_deadlock_report"]/data[@name = "xml_report"]/value/deadlock') as graph
from
	deadlock_events de
order by
	time_stamp desc;

5 апр 16, 15:49    [19019575]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
BERSERC
Member

Откуда:
Сообщений: 442
спасибо, картинку отловить получилось.

А что обозначает направление стрелок? Правильно ли я понял что это чтение(select) и запись(insert,update,delete)?
5 апр 16, 16:28    [19019759]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
BERSERC
Member

Откуда:
Сообщений: 442
и еще вопрос, у меня все запросы идут на уровни чтения read committed но в графе вижу дедлок по "блокировка ключа"
, из-за чего ключ может оставаться заблокированным после завершения операции ?
5 апр 16, 16:46    [19019846]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
Glory
Member

Откуда:
Сообщений: 104751
BERSERC
и еще вопрос, у меня все запросы идут на уровни чтения read committed но в графе вижу дедлок по "блокировка ключа"
, из-за чего ключ может оставаться заблокированным после завершения операции ?

Граф взаимоблокииовки содержит всю необходимую информацию
Включая исполняемый код
5 апр 16, 16:47    [19019851]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
BERSERC
Member

Откуда:
Сообщений: 442
Glory
BERSERC
и еще вопрос, у меня все запросы идут на уровни чтения read committed но в графе вижу дедлок по "блокировка ключа"
, из-за чего ключ может оставаться заблокированным после завершения операции ?

Граф взаимоблокииовки содержит всю необходимую информацию
Включая исполняемый код



у меня почему-то только код одной из 3х инструкцией показывается, в двух других просто "инструкция: " и все.
5 апр 16, 16:51    [19019865]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
Glory
Member

Откуда:
Сообщений: 104751
BERSERC
у меня почему-то только код одной из 3х инструкцией показывается, в двух других просто "инструкция: " и все.

Вы открыли свою модификацию Поля чудес - угадай картинку по описанию ?
5 апр 16, 16:53    [19019873]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
BERSERC
Member

Откуда:
Сообщений: 442
Glory
BERSERC
у меня почему-то только код одной из 3х инструкцией показывается, в двух других просто "инструкция: " и все.

Вы открыли свою модификацию Поля чудес - угадай картинку по описанию ?


К сообщению приложен файл. Размер - 77Kb
5 апр 16, 17:03    [19019926]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
Glory
Member

Откуда:
Сообщений: 104751
А теперь этот самый граф записываете в xml формате.
Или вы думаете, что по вашей скриншоту можно что-то узнать ?
5 апр 16, 17:05    [19019935]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
BERSERC
Member

Откуда:
Сообщений: 442
Glory, сейчас сохраню его в xml и попробую разобраться, спасибо!!
5 апр 16, 17:07    [19019945]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
BERSERC
Member

Откуда:
Сообщений: 442
Вообщем кое-что понятно стало, что процессы на merge вообще не причем, блочатся друг с другом процессы на удаление.
т.е. выполянется 3 запроса удаления из таблицы A , после которого каскадно должны удалиться данные из B и C , при удалении которых и возникает блокировка.

Предполагаю что проблема в том, что в таблицах B и C составной первичный ключ, и похоже во время удаления лочится вся таблица...


<deadlock-list>
 <deadlock victim="process9155d5b88">
  <process-list>
   <process id="process9155d5b88" taskpriority="0" logused="7620" waitresource="KEY: 27:72057594098745344 (ebb3025f8e58)" waittime="3105" ownerId="11507366277" transactionname="user_transaction" lasttranstarted="2016-04-05T17:10:08.103" XDES="0x11a01a7950" lockMode="RangeS-U" schedulerid="10" kpid="7900" status="background" spid="21" sbid="0" ecid="0" priority="0" trancount="3">
    <executionStack>
     <frame procname="CMDLIS.dbo.InsOfDeleteProbe" line="27" stmtstart="1254" stmtend="1520" sqlhandle="0x03001b000782d654d0453001a8a500000000000000000000">
delete probe where probe_id in (select probe_id from #fdd)

	--print convert(varchar(50),getdate(),108) + ' InsOfDeleteProbe 03'     </frame>
     <frame procname="adhoc" line="1" stmtstart="308" stmtend="444" sqlhandle="0x02000000b022d12a975a421cbced4259a992ca980ed79d3d">
delete dbo.Probe where ORDER_ID in (select order_id from #deleted)     </frame>
     <frame procname="CMDLIS.dbo.trDelOrders" line="16" stmtstart="1308" stmtend="1334" sqlhandle="0x03001b00e94d585389222c01eda200000000000000000000">
exec (@sql)     </frame>
     <frame procname="CMDLIS.dbo.IMPORT_XML" line="128" stmtstart="11376" stmtend="11740" sqlhandle="0x03001b001465fe67ef5dd300dfa500000100000000000000">
delete cmdlis.dbo.orders where order_number in (select ORDER_NUMBER from #orders where nullif(ORDER_NUMBER,'') is not null )
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!---     </frame>
     <frame procname="CMDLIS.dbo.SaveOrderBrokerProcedure" line="70" stmtstart="4162" stmtend="4230" sqlhandle="0x03001b007b8f0d5cf5bfd800dea500000100000000000000">
exec [dbo].[IMPORT_XML] @xml     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>
   <process id="processb7103048" taskpriority="0" logused="153944" waitresource="KEY: 27:72057594094419968 (ffffffffffff)" waittime="3766" ownerId="11507373322" transactionname="user_transaction" lasttranstarted="2016-04-05T17:10:08.673" XDES="0xac4c81950" lockMode="RangeS-U" schedulerid="10" kpid="6164" status="background" spid="29" sbid="0" ecid="0" priority="0" trancount="3">
    <executionStack>
     <frame procname="CMDLIS.dbo.InsOfDeleteProbe" line="27" stmtstart="1254" stmtend="1520" sqlhandle="0x03001b000782d654d0453001a8a500000000000000000000">
delete probe where probe_id in (select probe_id from #fdd)

	--print convert(varchar(50),getdate(),108) + ' InsOfDeleteProbe 03'     </frame>
     <frame procname="adhoc" line="1" stmtstart="308" stmtend="444" sqlhandle="0x02000000b022d12a975a421cbced4259a992ca980ed79d3d">
delete dbo.Probe where ORDER_ID in (select order_id from #deleted)     </frame>
     <frame procname="CMDLIS.dbo.trDelOrders" line="16" stmtstart="1308" stmtend="1334" sqlhandle="0x03001b00e94d585389222c01eda200000000000000000000">
exec (@sql)     </frame>
     <frame procname="CMDLIS.dbo.IMPORT_XML" line="128" stmtstart="11376" stmtend="11740" sqlhandle="0x03001b001465fe67ef5dd300dfa500000100000000000000">
delete cmdlis.dbo.orders where order_number in (select ORDER_NUMBER from #orders where nullif(ORDER_NUMBER,'') is not null )
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!---     </frame>
     <frame procname="CMDLIS.dbo.SaveOrderBrokerProcedure" line="70" stmtstart="4162" stmtend="4230" sqlhandle="0x03001b007b8f0d5cf5bfd800dea500000100000000000000">
exec [dbo].[IMPORT_XML] @xml     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>
   <process id="process2dfaa8e08" taskpriority="0" logused="259904" waitresource="KEY: 27:72057594098745344 (1bb6a0fde0ff)" waittime="4027" ownerId="11507365461" transactionname="user_transaction" lasttranstarted="2016-04-05T17:10:08.053" XDES="0x2ffac9950" lockMode="RangeS-U" schedulerid="1" kpid="8148" status="background" spid="22" sbid="0" ecid="0" priority="0" trancount="3">
    <executionStack>
     <frame procname="CMDLIS.dbo.InsOfDeleteProbe" line="27" stmtstart="1254" stmtend="1520" sqlhandle="0x03001b000782d654d0453001a8a500000000000000000000">
delete probe where probe_id in (select probe_id from #fdd)

	--print convert(varchar(50),getdate(),108) + ' InsOfDeleteProbe 03'     </frame>
     <frame procname="adhoc" line="1" stmtstart="308" stmtend="444" sqlhandle="0x02000000b022d12a975a421cbced4259a992ca980ed79d3d">
delete dbo.Probe where ORDER_ID in (select order_id from #deleted)     </frame>
     <frame procname="CMDLIS.dbo.trDelOrders" line="16" stmtstart="1308" stmtend="1334" sqlhandle="0x03001b00e94d585389222c01eda200000000000000000000">
exec (@sql)     </frame>
     <frame procname="CMDLIS.dbo.IMPORT_XML" line="128" stmtstart="11376" stmtend="11740" sqlhandle="0x03001b001465fe67ef5dd300dfa500000100000000000000">
delete cmdlis.dbo.orders where order_number in (select ORDER_NUMBER from #orders where nullif(ORDER_NUMBER,'') is not null )
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!---     </frame>
     <frame procname="CMDLIS.dbo.SaveOrderBrokerProcedure" line="16" stmtstart="978" stmtend="1042" sqlhandle="0x03001b007b8f0d5cf5bfd800dea500000100000000000000">
exec [dbo].[IMPORT_XML] @xml     </frame>
    </executionStack>
    <inputbuf>
    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594098745344" dbid="27" objectname="CMDLIS.dbo.OrderServParts" indexname="IX_ORDER_SERV_ID" id="lock7264e4e80" mode="RangeX-X" associatedObjectId="72057594098745344">
    <owner-list>
     <owner id="processb7103048" mode="RangeX-X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process9155d5b88" mode="RangeS-U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594094419968" dbid="27" objectname="CMDLIS.dbo.ResultsFiles" indexname="IX_ResultsFiles" id="locka2cc9ca00" mode="RangeS-U" associatedObjectId="72057594094419968">
    <owner-list>
     <owner id="process2dfaa8e08" mode="RangeS-U"/>
    </owner-list>
    <waiter-list>
     <waiter id="processb7103048" mode="RangeS-U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594098745344" dbid="27" objectname="CMDLIS.dbo.OrderServParts" indexname="IX_ORDER_SERV_ID" id="lock753f2b900" mode="RangeS-U" associatedObjectId="72057594098745344">
    <owner-list>
     <owner id="process9155d5b88" mode="RangeS-U"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2dfaa8e08" mode="RangeS-U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>
5 апр 16, 17:45    [19020134]     Ответить | Цитировать Сообщить модератору
 Re: Как узнать кто второй участник блокировки?  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
BERSERC
Предполагаю что проблема в том, что в таблицах B и C составной первичный ключ, и похоже во время удаления лочится вся таблица...
Это вы в графе увидели?

Сделайте для #fdd ПК или индекс по probe_id.
5 апр 16, 18:47    [19020360]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить