Locking - это механизм, который SQL Server использует для синхронизации одновременного доступа к данным несколькими клиентами. Как правило, приложения не устанавливают локи явно. Это делает SQL Server Database Engine на основании T-SQL выражений запросов.

Иерархия локов

Основные ресурсы, на которые SQL Server может установить лок:

  • RID / KEY
  • PAGE
  • TABLE
  • DATABASE и нескольких других. Меньший размер залоченых данных приводит к лучшей concurrency, но требует больше накладных расходов ввиду большего числа локов. И наоборот. Обычно SQL Server устанавливает локи сразу на нескольких уровнях:

Lock Hierarchy

Lock Modes

Основные режимы:

  • Shared (S) для операций, которые не меняют данные. Позволяет читать, не позволяет менять.
  • Update (U) для операций, которые могут менять данные. Непосредственно перед изменением данных, конвертируется в Exclusive. До этого - позволяет читать. Позволяет избежать дедлока вызванного попыткой сконвертировать S -> X.
  • Exclusive (X) для операций, которые меняют данные. Не позволяет менять, читить - только с NOLOCK hint или read uncommitted isolation level.
  • Intent (I) для построения иерархии локов. Устанавливается на более высоких уровнях, чтобы защитить локи на более низких.
  • Schema modification (Sch-M) для изменения схемы. Блокирует все внешние операции на таблице.

Lock compatibility

Если ресурс заблокирован транзакцией А, транзакция B сможет получить новый лок (и доступ к ресурсу) только если режим запрашиваемого лока совместим с существующим. Иначе транзация будет заблокирована до тех пор, пока лок на освободится, либо не наступит тайм-аут. Например, если на ресурсе установлен X лок, другие транзакции не смогут получить новые локи (за исключением NOLOCK и read uncommitted):

Lock Compatibility

Deadlocks

Дэдлок возникает когда две или более транзакции блокирую друг друга имея локи на ресурсах, к котором хочет получить доступ другая транзакция. Наиболее распространенным сценарием является Reverse Object Order Deadlock:

Reverse Object Order Deadlock

Deadlock Detection

Для выявления дэдлоков существует специальный lock monitor thread. Он просыпается периодически и проверяет все запущенные задачи на предмет дэдлока. По умолчанию этот процесс запускается раз в 5 секунд, однако, если дэдлоки находятся, интервал уменьшается вплоть до 100мс. Если число найденных дэдлоков сокращается - интервал увеличивается.

Когда дэдлок находится, SQL Server определяет транзакцию(-ции), которую будет откатывать. Такая транзакция называется deadlock victim. Определение происходит по слудущиму правилу:

  1. Сравниваются DEADLOCK_PRIORITY. Транзакция с меньшим приоритетом признается deadlock victim. Можно устанавливать руками, но очень аккуратно.
  2. Если приоритеты равны, определяется какую из транзакций легче откатить. Делается это на основании числа записей в логе. SELECT почти всегда проиграет INSERT/UPDATE/DELETE по этому показателю.

Deadlock victim транзакция откатывается, её клиенту отправляется 1205 ошибка, локи выжевшей транзакции разблокируются:

Deadlock victim

Handling Deadlocks

Обычно дедлоки возникают на непродолжительное время (порядка нескольких мс). Поэтому хорошей практикой является

  1. Отловить ошибку с кодом 1205.
  2. Залогировать ее для дальнейшего анализа.
  3. Отправить запрос на повторное исполнение. При этом все равно выбрасывать ошибку случае, когда дэдлок не уходит.

Ресурсы