Понимание определенного типа тупика

Я пытаюсь понять, как возникает определенный тип тупика.

У меня очень простой тупиковый график с двумя ресурсами и двумя процессами. Один из них выполняет SELECT а другой – оператор UPDATE . Я понимаю, что бывают ситуации, когда это может привести к тупиковой ситуации, но я не понимаю, как это происходит в этом конкретном сценарии.

Запрос жертвы:

 SELECT id_particle, id_event, dt_created, dt_rcvd, tlx_no, from_routename, re, deleted FROM msg_list WHERE to_routename = @P0 AND ((dt_answ IS NULL AND b_complete = 0 AND id_event=6) OR (id_event = 10 AND deleted = 0) OR (from_id_post = 9705 AND deleted = 0)) ORDER BY dt_created ASC 

Запрос победителя:

 UPDATE msg_list SET ID_EVENT=7, STATUS='Answered', DT_ANSW={ts '2017-02-12 05:34:14' WHERE ID_PARTICLE = 46211816 

Полный график взаимоблокировки:

 <deadlock-list> <deadlock victim="process30aa42d468"> <process-list> <process id="process30aa42d468" taskpriority="0" logused="0" waitresource="PAGE: 6:1:155679 " waittime="921" ownerId="427175775" transactionname="SELECT" lasttranstarted="2017-02-12T05:34:48.293" XDES="0x3126fbba40" lockMode="S" schedulerid="4" kpid="10648" status="suspended" spid="184" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2017-02-12T05:34:48.293" lastbatchcompleted="2017-02-12T05:34:48.280" lastattention="1900-01-01T00:00:00.280" clientapp="jTDS" hostname="LNS" hostpid="123" loginname="MsgStore" isolationlevel="read committed (2)" xactid="427175775" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="tempdb.dbo.#jtds000003_____________________________________________________________________________________________________________0042FD9D" line="1" stmtstart="92" stmtend="668" sqlhandle="0x030002005819e1b4efce580018a7000001000000000000000000000000000000000000000000000000000000"> SELECT id_particle, id_event, dt_created, dt_rcvd, tlx_no, from_routename, re, deleted FROM msg_list WHERE to_routename = @P0 AND ((dt_answ IS NULL AND b_complete = 0 AND id_event=6) OR (id_event = 10 AND deleted = 0) OR (from_id_post = 9705 AND deleted = 0)) ORDER BY dt_created AS </frame> </executionStack> <inputbuf> Proc [Database Id = 2 Object Id = -1260316328] </inputbuf> </process> <process id="process319282aca8" taskpriority="0" logused="21256" waitresource="PAGE: 6:1:396658 " waittime="869" ownerId="427175573" transactionname="implicit_transaction" lasttranstarted="2017-02-12T05:34:48.210" XDES="0x30c2dbc408" lockMode="IX" schedulerid="4" kpid="11980" status="suspended" spid="99" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-02-12T05:34:48.430" lastbatchcompleted="2017-02-12T05:34:48.427" lastattention="2017-02-12T03:34:28.130" clientapp="NotesMover" hostname="LNS" hostpid="8828" loginname="Notesmover" isolationlevel="read committed (2)" xactid="427175573" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame procname="adhoc" line="1" stmtstart="364" stmtend="812" sqlhandle="0x020000006d523b316d15563a50b97ce8d56da3cf6d8fc4450000000000000000000000000000000000000000"> unknown </frame> <frame procname="adhoc" line="1" stmtend="554" sqlhandle="0x02000000d8b78713c51b1588947edf24f2d7b69031f4f1d60000000000000000000000000000000000000000"> unknown </frame> <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> UPDATE msg_list SET ID_EVENT=7, STATUS='Answered', DT_ANSW={ts '2017-02-12 05:34:14'WHERE ID_PARTICLE = 46211816 </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="1" pageid="155679" dbid="6" subresource="FULL" objectname="MsgStore.dbo.msg_list" id="lock31683f2400" mode="IX" associatedObjectId="72057594523811840"> <owner-list> <owner id="process319282aca8" mode="IX"/> </owner-list> <waiter-list> <waiter id="process30aa42d468" mode="S" requestType="wait"/> </waiter-list> </pagelock> <pagelock fileid="1" pageid="396658" dbid="6" subresource="FULL" objectname="MsgStore.dbo.msg_list" id="lock2f80c2e300" mode="S" associatedObjectId="72057594523811840"> <owner-list> <owner id="process30aa42d468" mode="S"/> </owner-list> <waiter-list> <waiter id="process319282aca8" mode="IX" requestType="wait"/> </waiter-list> </pagelock> </resource-list> </deadlock> </deadlock-list> 

И графическое представление того же:

График взаимоблокировки

И вот планы запросов, которые генерируются при выполнении этих двух запросов:

Жертва:

Жертва плана выполнения

Победитель:

Победитель плана выполнения

Индекс ncci_rcvd_list используемый жертвой, представляет собой некластеризованный индекс покрытия с одним ключевым столбцом и несколькими включенными столбцами. Один из этих включенных столбцов обновляется победителем. Ключевой столбец не обновляется.

Таблица mgs_list имеет около 500 000 записей. Набор результатов, возвращаемый запросом SELECT обычно составляет всего несколько строк, например, около дюжины (хотя иногда это может быть больше, возможно, до нескольких тысяч).

Может кто-нибудь объяснить, как эта ситуация приводит к тупиковой ситуации?

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

Страница 155679 принадлежит process319282aca8 (UPDATE) в режиме IX и process30aa42d468 методом process30aa42d468 (SELECT) в режиме S. Страница 396658 принадлежит SELECT в режиме S и требуется UPDATE в режиме IX.

Проблема заключается в отсутствии индексов для обоих запросов. UPDATE использует индекс, но не покрывает, поэтому ему нужно искать RID в кластеризованный индекс. SELECT – это сканирование (выделенное блокировками S уровня страницы …). Это гарантированный тупик при параллелизме.

Это еще один пример неправильного использования таблиц в качестве очередей. Чтение Использование таблиц в качестве очередей . Отделите свое состояние от своих событий. Завершение и удаление только событий .

PS. «Жертва» всегда будет транзакцией, у которой меньше работы для отката, а в режиме чтения и записи это всегда будет прочитано. Кроме того, я сомневаюсь, что вы выполнили план выполнения SELECT, который фактически зашел в тупик.

  • DATEDIFF SQL Server 2016 JSON
  • Буферный пул в SQL Server FileStream
  • Способ не повторять выражения в запросе на обработку строки SQL Server
  • Неверный подсчет числа недель в неделю для 1-го января с использованием datepart
  • Как преобразовать функцию AVG () в десятичный в SQL SERVER
  • Как удалить пробелы при конкатенации, в то время как некоторые имеют значения Null?
  • SQL: удалить дубликат на основе критерия
  • Не удается найти работу администрирования TfsVersionControl
  • T-SQL OPENJSON $ AS JSON не работает
  • Ошибка разрешений SSRS 2016
  • T-SQL Join NULL и NOT NULL Records
  • Давайте будем гением компьютера.