Как избежать взаимоблокировок – как заблокировать на уровне строки

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

Вот график блокировки, предоставленный SQL Server. По идентификатору объекта можно сделать вывод, что оба процесса пытаются получить доступ к одной и той же таблице.

Таблица выглядит следующим образом. (Для тестирования я использовал глобальную временную таблицу)

CREATE TABLE ##tMyTable(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Fk_1_Id] [int] NOT NULL,
    [Fk_2_Id] [int] NULL,
    [Fk_3_Id] [int] NULL,
    [Day] [date] NOT NULL,
    [Quantity] [decimal](19, 3) NOT NULL,
 CONSTRAINT [Pk_MyTable_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

+ foreign key stuff. 

Столбцы с префиксом Fk_ являются внешними ключами.

Также в таблице есть индекс:

CREATE NONCLUSTERED INDEX [Idx_IndexName] ON ##tMyTable
(
    [Fk_1_Id] ASC,
    [Fk_2_Id] ASC--,
    [Day] ASC,
    [Id] ASC
)
INCLUDE([Fk_3_Id],[Quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Прерванный процесс был оператором удаления:

delete from ##tMyTable where Fk_2_Id = 86416

Процесс, которому было позволено жить, был вставкой. (даты указаны в немецком формате).

insert into ##tMyTable (Fk_1_Id, Fk_2_Id, Day, Quantity) select Fk_1_Id, 86415, Day, Quantity from tOtherTableWithSimilarColumns where Fk_4_Id = 22 and Day >= '01.07.2024' and Day < '01.08.2024'

Для тестирования я установил два сеанса со следующим кодом.

Begin transaction

Select @@SPID as FirstTransactionProcessID

Query I want to test

commit

Если я выполняю код без включения commit, транзакции блокируют свои ресурсы, и я могу смоделировать ситуацию тупика.

С помощью exec sp_lock я могу потом проверить замки.

Прежде чем начать, я вставил в таблицу следующее:

insert into ##tMyTable (Fk_1_Id, Fk_2_Id, Fk_3_Id, Tag, Menge)
Select 1, 1, NULL, '2024-01-01', 22.404

В сеансе 1 я выполнил это:

insert into ##tMyTable (Fk_1_Id, Fk_2_Id, Fk_3_Id, Tag, Menge)
Select 2, 2, NULL, '2024-01-01', 22.404

И затем во втором сеансе я выполнил это:

delete from ##tMyTable where Fk_2_Id = 1

Результат был такой:

SPID 77 — это вставка, а SPID 69 — удаление.

Как вы можете видеть, команда удаления пытается получить U-блокировку ключа, но не получает ее, поскольку вставка X блокирует клавишу. Однако я ожидал увидеть, что вставка блокирует страницу с помощью блокировки IX, в то время как удаление требует той же страницы для блокировки U.

Вопрос: Что насчет моей тестовой настройки не так? Почему я получаю другие блокировки по сравнению с исходной тупиковой ситуацией? И почему удаление просто не устанавливает блокировку IX на ключ и не работает на уровне строки? Две блокировки IX совместимы, и запросы не должны мешать друг другу. В конце концов, они нацелены на разные идентификаторы и, следовательно, на разные строки.

Что, похоже, решает мою проблему: если я создаю индекс, который включает только Fk_2_Id, в моей тестовой настройке не возникает конфликтов блокировок. Однако я бы хотел избежать «исправления» чего-либо без понимания того, почему мое решение работает.

XML тупика:

<deadlock>
 <victim-list>
  <victimProcess id = "process1f4f20e5468" />
 </victim-list>
 <process-list>
  <process id = "process1f4f20e5468" taskpriority = "0" logused = "0" waitresource = "PAGE: 8:1:11652007 " waittime = "2865" ownerId = "213325556" transactionname = "DELETE" lasttranstarted = "2024-08-04T10:02:44.943" XDES = "0x1f4e33c3aa0" lockMode = "U" schedulerid = "2" kpid = "9392" status = "suspended" spid = "56" sbid = "0" ecid = "2" priority = "0" trancount = "0" lastbatchstarted = "2024-08-04T10:02:44.937" lastbatchcompleted = "2024-08-04T10:02:44.933" lastattention = "1900-01-01T00:00:00.933" clientapp = ".Net SqlClient Data Provider" hostname = "Our-Host" hostpid = "11672" isolationlevel = "read committed (2)" xactid = "213325556" currentdb = "8" currentdbname = "Our_DB" lockTimeout = "4294967295" clientoption1 = "671088672" clientoption2 = "128056">
   <executionStack>
    <frame procname = "adhoc" line = "1" stmtend = "152" sqlhandle = "0x02000000cff49033589c5bbc920f9e3e6c20dd52ec7b2a6b0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname = "adhoc" line = "1" stmtend = "152" sqlhandle = "0x02000000a28e5e12abc21669e1bfc6fb7c33f25bfee40b1c0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
delete from tMyTable where Fk_2_Id = 86416   </inputbuf>
  </process>
  <process id = "process1f4f5011c28" taskpriority = "0" logused = "9944" waitresource = "PAGE: 8:1:11631539 " waittime = "4125" ownerId = "213327344" transactionname = "INSERT" lasttranstarted = "2024-08-04T10:02:46.687" XDES = "0x1f4e45e4460" lockMode = "IX" schedulerid = "4" kpid = "11516" status = "suspended" spid = "68" sbid = "0" ecid = "0" priority = "0" trancount = "2" lastbatchstarted = "2024-08-04T10:02:46.657" lastbatchcompleted = "2024-08-04T10:02:46.653" lastattention = "1900-01-01T00:00:00.653" clientapp = ".Net SqlClient Data Provider" hostname = "Our-Host" hostpid = "11672" loginname = "loginname" isolationlevel = "read committed (2)" xactid = "213327344" currentdb = "8" currentdbname = "Our_DB" lockTimeout = "4294967295" clientoption1 = "671088672" clientoption2 = "128056">
   <executionStack>
    <frame procname = "adhoc" line = "1" stmtend = "568" sqlhandle = "0x02000000679aaf081f4d1ffe0710dbf9b238a8db3e6adb6b0000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
       insert into tMyTable (Fk_1_Id, Fk_2_Id, Day, Quantity) select Fk_1t_Id, 86415, Day, Quantity from tOtherTableWithSimilarColumns where Fk_4_Id = 22 and Day &gt;= '01.07.2024' and Day &lt; '01.08.2024'   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid = "1" pageid = "11652007" dbid = "8" subresource = "FULL" objectname = "Our_DB.dbo.tMyTable" id = "lock1f7bf84a200" mode = "IX" associatedObjectId = "72057661408477184">
   <owner-list>
    <owner id = "process1f4f5011c28" mode = "IX" />
   </owner-list>
   <waiter-list>
    <waiter id = "process1f4f20e5468" mode = "U" requestType = "wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid = "1" pageid = "11631539" dbid = "8" subresource = "FULL" objectname = "Our_DB.dbo.tMyTable" id = "lock1f4e3647700" mode = "U" associatedObjectId = "72057661408477184">
   <owner-list>
    <owner id = "process1f4f20e5468" mode = "U" />
   </owner-list>
   <waiter-list>
    <waiter id = "process1f4f5011c28" mode = "IX" requestType = "wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>