Заблокировка SQL Server 2005 с некластеризованным индексом

Может ли кто-нибудь помочь мне в тупике в SQL Server 2005?

Для простого теста у меня есть таблица "Книга", которая имеет первичный ключ (id) и имя столбца. Индекс по умолчанию этого первичного ключа некластеризованный.

Тупик происходит, когда два сеанса работают одновременно. Монитор активности показывает первый сеанс "//шаг 1" блокирует строку (блокировку блокировки) с блокировкой X. Второй сеанс блокирует блокировку строки U и блокировку ключа U. Изображение тупика показывает, что "//step2" для первого сеанса требует блокировки ключа U.

Если индекс кластеризованный, в этом случае нет взаимоблокировки. "//шаг 1" будет поддерживать блокировку строк и клавиш одновременно, поэтому проблем нет. Я понимаю, что блокировка строки также блокирует индекс, так как leaf node кластерного индекса - это данные строки.

Но почему именно некластеризованный индекс? Если второй сеанс содержит блокировку ключа U, почему "шаг 1" первого сеанса не удерживает эту блокировку, поскольку они являются тем же самым оператором обновления.

--// first session
BEGIN TRAN
  update Book set name = name where id = 1 //step 1
  WaitFor Delay '00:00:20'
  update Book set name = 'trans' where id = 1 //step2
COMMIT

--// second session
BEGIN TRAN
--// this statement will keep both RID(U lock) and KEY(U lock) if first session did not use HOLDLOCK
  update Book set name = name where id = 1
COMMIT

Ответы

Ответ 1

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

  • Найти строки для обновления, используя блокировки U на затронутых данных
  • Обновить строки, используя блокировки X для измененных данных

После завершения выполнения инструкции (по умолчанию READ COMMITTED) блокировки U освобождаются, но блокировки X удерживаются до конца транзакции для поддержания изоляции.

В вашей некластеризованной индексной ситуации SQL Server ищет индекс по идентификатору и использует его для поиска фактической строки. Блокировка выглядит следующим образом:

  • (сеанс 1, шаг 1) U-блокировка, взятая за значение индексного ключа для id = 1
  • (сеанс 1, шаг 1) Х-блокировка, принятая для RID для строки с id = 1
  • (Сессия 1, шаг 1) Блокировка U
  • (Сессия 2) Блокировка U, взятая за значение индексного ключа для id = 1
  • (сеанс 2) Блокировка X заблокирована для RID для строки с id = 1
  • (сеанс 1, шаг 2) Блокировка U заблокирована по значению ключа ключа для id = 1 - DEADLOCK

Однако, когда индекс является кластеризованным индексом, не существует отдельного шага для преобразования ключа индекса в строку - значение кластерного индекса является идентификатором строки. Поэтому блокировка заканчивается следующим образом:

  • (сеанс 1, шаг 1) U-блокировка, взятая за значение индексного ключа для id = 1
  • (сеанс 1, шаг 1) Блокировка U обновлена ​​до блокировки X
  • (сеанс 2) Блокировка U заблокирована по значению ключа ключа для id = 1
  • (сеанс 1, шаг 2) блокировка уже сохранена по значению ключа ключа для id = 1
  • (сеанс 1, фиксация) заблокирован
  • (Сессия 2) Предохранитель U
  • (сеанс 2) U-блокировка обновлена ​​до блокировки X
  • (сеанс 2) заблокирован

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

Ответ 2

В этой ссылке есть много полезных предложений: Тупики SQL Server между выбором/обновлением или множественными выборами.

Вот несколько моментов, которые следует учитывать, чтобы помочь людям ответить на ваш вопрос:

  • Какой уровень изоляции транзакций вы используете?
  • Разрешена ли эскалация блокировки (например, от строки к странице)?
  • Есть ли индекс в столбце 'name'?

Ответ 3

Ваше первое обновление ничего не меняет:

update Book set name = name where id = 1

Thy команда, которая фактически изменяет ваш столбец, тогда в строке будет удерживаться исключительная блокировка.