Проблема взаимоблокировки SQL Server — как решить?

У нас есть задание SQL (2005 г.), которое время от времени дает сбой из-за взаимоблокировки. Ошибка следующая;

Транзакция (идентификатор процесса 52) заблокирована в потоке | ресурсы буфера связи с другим процессом и был выбран в качестве жертвы взаимоблокировки. Повторите транзакцию. [SQLSTATE 40001] (ошибка 1205). Шаг не удался.

Это ночная работа, которая эффективно загружает базу данных отчетов и выполняет различные исправления данных. Никакие другие операторы DML не выполняются одновременно. Ошибка возникает примерно раз в неделю в разные дни и в разные моменты процесса загрузки данных. Поэтому я предполагаю, что это не из-за операторов DML.

Включение флага трассировки 1222 предоставляет практически бесполезную информацию (записи журнала ниже). Большинство статей, которые я читал, связаны с транзакциями и запросами и, следовательно, блокировки связаны со страницами/таблицами данных, но не многие из них связаны с памятью/потоками/ресурсами буфера.

Я запустил PerfMon со всеми счетчиками, которые казались важными (связанными с сетью, доступными потоками, доступными рабочими элементами, ожиданием буфера журнала, а также обычными счетчиками, такими как процессор и память), но не было ничего очевидного.

Любая помощь будет оценена по достоинству, поскольку никто, похоже, не знает, как решить эту проблему.

Ваше здоровье

Прит

Может ли эта работа заблокировать себя? Может быть, он запускает асинхронные задачи, и что-то идет не так.

06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process7f9f18 taskpriority=0 logused=20059 waittime=78 schedulerid=2 kpid=6284 status=suspended spid=52 sbid=0 ecid=13 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process7f9d38 taskpriority=0 logused=20063 waittime=62 schedulerid=2 kpid=4760 status=suspended spid=52 sbid=0 ecid=7 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process7f8898 taskpriority=0 logused=20068 waittime=46 schedulerid=2 kpid=6772 status=suspended spid=52 sbid=0 ecid=12 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process7ebf18 taskpriority=0 logused=20059 waittime=78 schedulerid=1 kpid=5052 status=suspended spid=52 sbid=0 ecid=14 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,inputbuf 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=1 sqlhandle=0x01001000004b623470f7920c000000000000000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=270 stmtstart=17844 stmtend=17912 sqlhandle=0x030010007d8c3a4087c11d01109d00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,unknown 06/21/2010 08:17:30,spid5s,Unknown,frame procname=unknown line=10 stmtstart=228 sqlhandle=0x03001000f053dc3ec1f80101269a00000100000000000000 06/21/2010 08:17:30,spid5s,Unknown,executionStack 06/21/2010 08:17:30,spid5s,Unknown,process id=process7ebd38 taskpriority=0 logused=20072 waittime=31 schedulerid=1 kpid=6208 status=suspended spid=52 sbid=0 ecid=15 priority=0 transcount=0 lastbatchstarted=2010-06-21T06:29:24.740 lastbatchcompleted=2010-06-21T06:29:24.740 clientapp=SQLAgent - TSQL JobStep (Job 0x7405EAEC43ECFD4EABFBB72FC12B3F18 : Step 1) hostname=Server1 hostpid=5536 isolationlevel=read committed (2) xactid=61047212 currentdb=16 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056 06/21/2010 08:17:30,spid5s,Unknown,process-list 06/21/2010 08:17:30,spid5s,Unknown,deadlock victim=process7fea78 06/21/2010 08:17:30,spid5s,Unknown,deadlock-list


multithreading communication buffer sql-server-2005
person Preet    schedule 30.06.2010    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Если нет, то это должно быть конфликтом с другим процессом. Возможно, вы могли бы установить для вашей базы данных однопользовательский режим перед запуском обновлений.

Обычно такого рода взаимоблокировка происходит из-за транзакции, использующей параллелизм. SQL Server делит запрос на несколько потоков, а затем они блокируют друг друга. Немедленное исправление — добавить _1_ в конец многопоточного запроса. Однако лучше оптимизировать запрос и/или добавить соответствующие индексы.

person burnall    schedule 30.06.2010
comment
Если происходит взаимоблокировка, это означает, что 2 процесса SQL-сервера пытаются получить доступ к одной и той же таблице, индексу и т. д. Поэтому в первую очередь вы должны обнаружить эти 2 процесса. Я считаю, что мои вопросы в первом посте могут помочь найти проблему - person Preet; 30.06.2010
comment
Что ж, я думал, что Trace Flag 1222 обеспечит это. Как видите, это не так. Обнаружение процессов, запущенных во время взаимоблокировки, может быть отвлекающим маневром, поскольку загрузка данных завершается сбоем в разных местах, и если это происходит из-за узкого места в ресурсах, это может быть не зафиксировано. Думаю, единственное, что я могу сделать, это запускать SQL Profiler каждую ночь с различными событиями и надеяться, что будет собрана какая-то полезная информация. - person burnall; 30.06.2010
comment
21.06.2010 08:17:30, spid5s, неизвестно, событие ожидания = e_waitPortOpen type = идентификатор потребителя = process7862208 21.06.2010 08:17:30, spid5s, неизвестно, событие ожидания = e_waitPortOpen type = идентификатор потребителя = process5caf63e8 21.06.2010 08:17:30, spid5s, неизвестно, событие ожидания = e_waitPortOpen type = идентификатор потребителя = process55a38118 21.06.2010 08:17:30, spid5s, неизвестно, событие ожидания = e_waitPortOpen type = идентификатор потребителя = process4e95a2f8 21.06.2010 08:17:30,spid5s,неизвестно,лист ожидания 21.06.2010 08:17:30,spid5s,неизвестно,владелец event=pending id=process55a384d8 21.06.2010 08:17:30 ,spid5s,неизвестно,событие владельца=ожидание id=process4e95a6b8 21/06/2010 08:17:30,spid5s,неизвестно,событие владельца=e_waitNone тип=идентификатор производителя=process78622f8 21/6/2010 08:17:30,spid5s, Неизвестно, список владельцев 21.06.2010 08:17:30, spid5s, неизвестно, exchangeEvent id = porta152d80 nodeId = 76 21.06.2010 08:17:30, spid5s, неизвестно, событие ожидания = e_waitPortOpen type = идентификатор потребителя =process5caf64d8 21/06/2010 08:17:30,spid5s,неизвестно,waiter event=e_waitPortOpen type=consumer id=process4e95a3e8 21/06/2010 08:17:30, spid5s, неизвестно, событие ожидания = e_waitPortOpen type = идентификатор потребителя = process55a38208 21.06.2010 08:17:30, spid5s, неизвестно, событие ожидания = e_waitPortOpen type = идентификатор потребителя = process78622f8 21.06.2010 08:17:30, spid5s, неизвестно, список ожидания 21.06.2010 08:17:30, spid5s, неизвестно, событие владельца = ожидание id = process55a384d8 21.06.2010 08:17:30, spid5s, неизвестно, событие владельца = ожидание id = process4e95a6b8 21/06/2010 08:17:30,spid5s,неизвестно,owner event=e_waitNone type=producer id=process55a382f8 21/06/2010 08:17:30,spid5s,неизвестно,owner-list 21/06/2010 08 :17:30,spid5s,неизвестно,exchangeEvent id=porta152b80 nodeId=79 21/06/2010 08:17:30,spid5s,неизвестно,waiter event=e_waitPortOpen type=consumer id=process4e95a4d8 21/06/2010 08:17: 30,spid5s,неизвестно,waiter event=e_waitPortOpen type=consumer id=process78623e8 21.06.2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf65c8 21.06.2010 08:17: 30,spid5s,неизвестно,waiter event=e_waitPortOpen type=consumer id=process55a382f8 21.06.2010 08:17:30,spid5s,неизвестно,waite r-list 21.06.2010 08:17:30,spid5s,неизвестно,событие владельца=ожидание id=process55a384d8 21.06.2010 08:17:30,spid5s,неизвестно,событие владельца=ожидание id=process4e95a6b8 21.06 /2010 08:17:30,spid5s,неизвестно,владелец event=e_waitNone type=producer id=process78624d8 21.06.2010 08:17:30,spid5s,неизвестно,owner-list 21.06.2010 08:17:30 ,spid5s,Unknown,exchangeEvent id=porta152980 nodeId=81 21/06/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process5caf6208 21/06/2010 08:17:30,spid5s, Неизвестно,waiter event=e_waitPortOpen type=consumer id=process7ffe28 21/06/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ebd38 21/06/2010 08:17:30,spid5s, Неизвестно, событие ожидания = e_waitPortOpen type = идентификатор потребителя = process4e95a118 21.06.2010 08:17:30, spid5s, неизвестно, список ожидания 21.06.2010 08:17:30, spid5s, неизвестно, событие владельца = идентификатор ожидания =process55a384d8 21/06/2010 08:17:30,spid5s,неизвестно,событие владельца=ожидание id=process4e95a6b8 21/06/2010 08:17:30,spid5s,неизвестно,событие владельца=e_waitNone ty pe=producer id=process55a38118 21/06/2010 08:17:30,spid5s,Unknown,owner-list 21/06/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152380 nodeId=72 21/06/ 2010 08:17:30,spid5s,неизвестно,waiter event=e_waitPortOpen type=consumer id=process7fea78 21.06.2010 08:17:30,spid5s,неизвестно,waiter-list 21.06.2010 08:17:30, spid5s, неизвестно, событие владельца = ожидание id = process55a384d8 21/06/2010 08:17:30, spid5s, неизвестно, событие владельца = ожидание id = process4e95a6b8 21/06/2010 08:17:30, spid5s, неизвестно, событие владельца =e_waitNone type=producer id=processd75d38 21/06/2010 08:17:30,spid5s,Unknown,owner-list 21/06/2010 08:17:30,spid5s,Unknown,exchangeEvent id=porta152780 nodeId=2 06/ 21/2010 08:17:30,spid5s,неизвестно,waiter event=e_waitPortOpen type=consumer id=process7ebf18 21/06/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7f9f18 06/ 21/2010 08:17:30,spid5s,неизвестно,waiter event=e_waitPortOpen type=consumer id=process7fed48 21/06/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=co nsumer id=processd75d38 21/06/2010 08:17:30,spid5s,неизвестно,список ожидания 21/06/2010 08:17:30,spid5s,неизвестно,событие владельца=ожидание id=process55a384d8 21/06/2010 08 :17:30,spid5s,неизвестно,событие владельца=ожидание id=process4e95a6b8 21/06/2010 08:17:30,spid5s,неизвестно,событие владельца=e_waitNone тип=идентификатор производителя=processd74e38 21/06/2010 08:17: 30,spid5s,неизвестно,список-владельцев 21/06/2010 08:17:30,spid5s,неизвестно,exchangeEvent id=porta152200 nodeId=59 21/06/2010 08:17:30,spid5s,неизвестно,waiter event=e_waitPortOpen type=consumer id=processd74e38 21/06/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7f9d38 21/06/2010 08:17:30,spid5s,Unknown,waiter event=e_waitPortOpen type=consumer id=process7ff2e8 21/06/2010 08:17:30,spid5s,Unknown,waiter-list 21/06/2010 08:17:30,spid5s,Unknown,owner event=pending id=process55a384d8 21/06/ 2010 08:17:30, spid5s, неизвестно, событие владельца = ожидание id = process4e95a6b8 21.06.2010 08:17:30, spid5s, неизвестно, событие владельца = e_waitNone type = идентификатор производителя = - person Preet; 02.07.2010

arrow_upward
0
arrow_downward

На самом деле не уверен, так как он терпит неудачу в разных местах. Я думал, что это, вероятно, связано с какой-то настройкой конфигурации или аппаратным узким местом, поскольку оно жалуется на ресурсы потока или буфера связи. Если бы я точно знал, о чем идет речь (сетевые адаптеры, внутренние структуры?), это было бы началом.

person Bar    schedule 08.07.2020