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

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

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

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

Если индекс является кластеризованным, то в этом случае взаимоблокировки нет. "//Шаг 1 " будет держать строку и ключ блокировки в то же время, так что нет никаких проблем. Я могу понять, что блокировка строки также блокирует индекс, поскольку конечным узлом кластеризованного индекса являются данные строки.

Но, почему некластеризованный индекс находится таким образом? Если вторая сессия держит ключ U lock, почему "Шаг 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
3 10
sql-server sql-server-2005 deadlock

3 ответа:

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

  1. найти строки для обновления, принимая U блокировки на затронутых данных
  2. обновление строк, принимая X блокировок на измененных данных

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

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

  1. (Сессия 1, Шаг 1) u блокировка принята по значению ключа индекса для id = 1
  2. (Сессия 1, Шаг 1) x блокировка взята на RID для строки с id = 1
  3. (Сессия 1, Шаг 1) u блокировка снята
  4. (Сессия 2) U блокировка взята по значению ключа индекса для id = 1
  5. (Сессия 2) x блокировка заблокирована для RID для строки с id = 1
  6. (Сессия 1, Шаг 2) U блокировка заблокирована по значению ключа индекса для id = 1 -- DEADLOCK
Однако, когда индекс является кластеризованным индексом, нет отдельного шага для преобразования ключа индекса в строку - значение кластеризованного индекса является идентификатором строки. Поэтому блокировка заканчивается следующим образом:
  1. (Сессия 1, Шаг 1) u блокировка принята по значению ключа индекса для id = 1
  2. (Сессия 1, Шаг 1) U lock обновлен до X lock
  3. (Сессия 2) U блокировка заблокировано по значению ключа индекса для id = 1
  4. (Сессия 1, Шаг 2) блокировка уже проведена по значению ключа индекса для id = 1
  5. (Сессия 1, фиксация) блокировка снята
  6. (Сессия 2) U блокировка предоставлена
  7. (Сессия 2) U lock обновлен до X lock
  8. (Сессия 2) блокировка снята

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

11

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

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

  1. Какой уровень изоляции транзакций вы используете?
  2. допускается ли эскалация блокировки (например, от строки к странице)?
  3. есть ли индекс в столбце "имя"?
0

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

update Book set name = name where id = 1

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

0