Ms sql блокировка таблицы

Блокировки в SQL Server

Я думал, что знаю SQL Server достаточно хорошо. Я использую этот продукт уже больше 6 лет, и мне нравится знать об используемых мною инструментах все. Когда я преподавал на курсах программирования SQL Server, я заметил, что в материалах Microsoft представлена таблица совместимости блокировок. Та же таблица была представлена и в MSDN. Рассматривая эту таблицу, я удивился — неужели здесь нет блокировки Intent Update? Это привело меня к исследованию блокировок. Эта статья и есть результат этого исследования. Я написал эту статью для определенного читателя — для того, кто понимает уровни изоляции, блокировки намерения, мертвые блокировки и уровни блокировок. Если вы недостаточно уверенно разбираетесь в этих областях, вам нужно сначала ознакомиться с ними перед чтением этой статьи.

Я надеюсь, что я расширю ваше понимание блокировок в SQL Server и, возможно, научу вас некоторым приемам, которые вы сможете использовать во время программирования на SQL Server.
Должен сказать, что вы можете вполне успешно работать с SQL Server долгое время и не знать, как он блокирует свои ресурсы, и в то же время писать высококачественные код и схемы баз данных. Но если вы похожи на меня и хотите знать внутреннее строение вещей или если вы работаете с системой, которая требует хотя бы небольшого прироста производительности, то я могу научить вас кое-чему полезному.

Я уверен, что вы слышали о мертвых блокировках, когда системный процесс Х блокирует ресурс А и ждет, чтобы заблокировать ресурс B, в то время как системный процесс Y блокирует процесс B и ждет освобождения процесса А. Если не слышали, то поищите дополнительную информацию об этом в MSDN.
Итак, представьте, что типов мертвых блокировок больше. Рассмотрим следующую ситуацию: системный процесс Х имеет shared блокировку на ресурсе А, и системный процесс Y тоже. Это не проблема, так как две shared блокировки совместимы друг с другом. Теперь процесс Х хочет превратить shared блокировку в эксклюзивную блокировку, чтобы обновить ресурс. Для этого процесс Х должен подождать, пока процесс Y снимет shared блокировку, и пока процесс Х ждет, процесс Y решает сделать то же самое, т.е. превратить свою блокировку в эксклюзивную блокировку. Процесс Х ждет процесс Y, а процесс Y ждет процесс Х. Это и есть мертвая блокировка. Такие блокировки называются мертвые блокировки конвертации (conversion deadlocks).
Это довольно обычная ситуация, и чтобы ее избежать, были введены update блокировки. Update блокировки позволяют соединению читать ресурс, но в то же время показывают свое намерение блокировать его позже, чтобы изменить ресурс. Проблема с update блокировками заключается в том, что SQL Server заранее не знает, хочет ли транзакция превратить shared блокировку в эксклюзивную блокировку, если только это не одиночная команда SQL, как команда UPDATE, которая должна сначала прочитать таблицу, прежде чем обновлять ее.
Чтобы использовать update блокировки в команде SELECT, вы должны явно определить, что вы хотите, чтобы SQL Server использовал их, и это можно сделать при помощи хинта блокировки UPDLOCK.

Мне нравится иллюстрировать то, о чем я говорю, примерами кода. В окне QA я запускаю следующий пакет:

Заметьте, что я открываю транзакцию, но не закрываю ее. Это сделано для удержания блокировок.
Если другое соединение попытается наложить другую update блокировку на ту же строку, то эта блокировка будет отложена, пока не будет снята первая блокировка — две update блокировки одного ресурса несовместимы.

Результат SP_LOCK с нужными строками и столбцами показан ниже:

Как и ожидалось, ключ, который мы блокируем, OrderId, заблокирован update блокировкой. Странное значение в столбце Resource (89003da47679) — это хэшированное значение нашего ключа, 10633. SQL Server использует у себя хэш таблицу как таблицу блокировки, и это то, что мы видим здесь.
Страница, содержащая эту строку, как и ожидалось, заблокирована с помощью Intent Update блокировки. Значение столбца Resource (1:242) показывает, что страница размещена в первом файле базы данных и ее номер — 242 в этом файле. И, наконец, хотя и не ожидалось, SQL Server накладывает IX блокировку на уровень таблицы. SQL Server никогда не использует U/IU блокировки на уровне таблицы, так что мы увидим только X/IX блокировки на этом уровне.
Когда вы выполняете обновление с выражением where, SQL Server должен прочитать таблицу и/или индексы, чтобы определить, какие строки требуют изменения. Перед тем, как он прочитает всю информацию из таблицы/индекса, SQL Server блокирует объект, к которому требуется доступ. Т.к. SQL Server знает, что вы выполняете обновление, то он выберет update блокировку, а не shared блокировку. Это нужно для избежания возникновения мертвой блокировки, о которой я говорил ранее — о мертвой блокировке конвертации.
Когда SQL Server нашел строку или строки для обновления, то он превращает update блокировку строки в эксклюзивную блокировку на RID, если таблица является кучей, или на KEY, если таблица имеет кластерный индекс. Это значит, что обычно вы не видите update блокировок, когда выполняете команду UPDATE — они почти сразу превращаются в эксклюзивные блокировки.
Но, конечно, есть некоторые исключения из этого правила. Если SQL использует индекс для поиска строк, то он блокирует страницы и ключи индекса при помощи update блокировок. Если обновление не изменяет никаких столбцов, содержащихся в индексе, то update блокировки не превратятся в эксклюзивные блокировки. Приведу пример:

Region — это таблица-куча с уникальным некластерным первичным ключом по столбцу RegionId. Чтобы заполнить этот запрос, SQL Server просматривает индекс по RegionId и блокирует страницы и ключи индекса при помощи update блокировок. Наконец, он найдет строку, требующую изменения. Т.к. команда обновления не изменяет RegionId, то ей не нужно накладывать эксклюзивную блокировку на индекс. Результат из SP_LOCK показан ниже:

Мы видим IX блокировку на таблице и X блокировку на RID обновляемой строки. KEY блокировка наложена на индекс по RegionId. Это понятно по столбцу Indid. Как видно, у нас все еще сохраняется update блокировка на индексе. Это один из немногих случаев, когда вы можете непосредственно увидеть update блокировку.
И, чтобы закончить, у нас есть две страничные блокировки — одна на странице индекса (1:306) и одна на таблице-куче (1:300). Откуда я это знаю? Посмотрите на столбец Indid. Id индекса, равный 0, всегда обозначает таблицу-кучу.

SQL Server имеет несколько типов блокировок, но он также выбирает и разные уровни блокировок.
Если вы запускали SP_LOCK хотя бы раз или смотрели текущее состояние Enterprise Manager, то вы точно видели по крайней мере 4 или 5 разных типов блокировок. Я быстро пройду по каждому из них.

Database (DB): Это блокировка сессии — т.е. она не относится ни к какой транзакции, а только к пользователю, подключенному к определенной базе данных. Это нужно, чтобы предотвратить удаление базы данных, когда к ней подключены один или более пользователей. Заметьте, что SQL Server знает, что базы данных master и tempdb не могут быть удалены, и не накладывает DB блокировку на эти базы данных.

Table (TAB): Это самая грубая логическая блокировка, которую может использовать SQL Server. Часто вы обнаруживаете intent блокировки на этом уровне. (Не помните точно, что такое intent блокировки? Читайте дальше.)

Extent (EXT): Эти блокировки не используются для блокирования логических строк, а используются, когда SQL Server создает новые таблицы или расширяет существующие, также вы можете их видеть, когда файл увеличивается в размере.

Page (PAG): Когда SQL Server требуется заблокировать одновременно множество строк, а свободные слоты блокировок заканчиваются, то он может использовать страничные блокировки. Чаще всего вы будете видеть intent блокировки на этом уровне. До SQL Server версии 6.5 включительно это была самая лучшая блокировка.

Key (KEY): Лучший уровень блокировки, возможный в SQL Server, вместе с RID блокировкой. KEY блокировки используются в индексах, а RID блокировки — в таблицах-кучах. Изучая в настоящее время поведение блокировок в SQL Server, я обнаружил, что SQL Server в большинстве случаев ценит конкурентность выше, чем скорость. Высокая конкурентность означает, что множество пользователей может работать одновременно. По возможности это достигается путем небольших блокировок, чтобы не блокировать без необходимости данные, нужные другим пользователям. С другой стороны, высокая скорость может быть достигнута при помощи больших блокировок, что быстрее, чем накладывание множества маленьких блокировок.

SQL Server 2000 может также поднимать уровень ваших блокировок, если он заметит, что вы блокируете все больше и больше строк. В этом случае SQL Server применяет табличную блокировку и удаляет все индивидуальные блокировки на страницах, ключах и RID. Заметьте — поднятие уровня блокировки приводит к установлению табличных блокировок. SQL Server не повышает уровень RID/KEY блокировок до страничных блокировок.
Когда же SQL Server поднимает уровень блокировок? Его не интересует, насколько большой процент таблицы заблокирован; единственная вещь, которая имеет значение — это общее количество блокировок в системе. Когда блокировки начинают использовать достаточно большой процент памяти, SQL Server 2000 пытается поднять уровень блокировок всех транзакций во всех соединениях. Также он пытается поднять уровень блокировок, если заканчиваются слоты блокировок. Вы можете сами это попробовать, уменьшив количество слотов блокировок, которые использует SQL Server, при помощи процедуры SP_CONFIGURE.
SQL Server пытается удерживать высокую конкурентность при помощи малых блокировок, насколько это возможно. Но иногда вы знаете о своих данных то, что SQL Server не знает, и эта информация влияет на уровень блокировок, который вы хотите использовать. Например, существует большая таблица для поиска, на которой производится только чтение данных. Вместо накладывания множества key блокировок вы хотите заблокировать целую таблицу за один шаг. В этом случае вы можете использовать хинты блокировки или SP_INDEXOPTION.
Хинты блокировки хорошо известны и хорошо документированы в BOL, так что я не буду повторять их здесь. Системная хранимая процедура SP_INDEXOPTION — это хороший путь для указания SQL Server использовать определенный размер блокировок.
Используя SP_INDEXOPTION, вы можете выключить блокировку уровня строки или уровня страницы. Это значит, что вам не нужны хинты блокировки — все блокировки таблицы/индекса будут иметь заданный вами размер. Хотя BOL и утверждает, что эта процедура используется для выбора уровня блокировки на индексах, она также может использоваться и для таблицы-кучи. Передавайте название таблицы в переменную @IndexNamePattern. Очень хороший и практически неизвестный способ.
Количество влияющих факторов на этом не заканчивается. Если вы используете один из двух высших уровней изоляции и у вас нет ни одного индекса по критерию выборки, то SQL Server вынужден блокировать большую часть, если не всю таблицу, чтобы выполнить запрос. Приведу пример.

Теперь, в другом окне, давайте проверим результат sp_lock. На моем компьютере я отследил 853 блокировки, относящиеся к соединению, на котором я запустил UPDATE. Таблица Orders в базе Northwind имеет 830 строк, так что я заблокировал каждую строку. Откатите транзакцию и попытайтесь снова применить update после создания индекса, как показано ниже.

Теперь результат SP_LOCK показывает всего 25 блокировок. Эта часть производительности часто не замечается. Даже если вы используете READ COMMITTED, уровень изоляции по умолчанию, то вы все равно увидите большую разницу — снижение количества блокировок со 136 до 24 после создания индекса.

Существует несколько флагов трассировки блокировок, которые помогут настроить ваши блокировки и обнаружить проблемы с мертвыми блокировками.
Флаги трассировки используются, чтобы включать или выключать определенное поведение в SQL Server. Вы можете установить флаг трассировки, используя DBCC TRACEON, и если вы хотите, чтобы флаг трассировки включался при каждом запуске SQL Server, то вы можете определить его как стартовый параметр, используя ‘-T’.

1200: Показывает все блокировки по всем соединениям.Очень объемный результат, я рекомендую использовать его в контролируемой среде, где работает только одно соединение.

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

KEY: означает, что ресурс, заблокированный мертвой блокировкой, является ключом индекса. Этот ресурс также может быть любым другим типом блокировки, таким, как страница, RID, таблица, и т.д.

ECID взят из master.dbo.sysprocesses. Он используется для различения блокировок, наложенных разными потоками. Режим — это режим блокировки, приведшей к мертвой блокировке, такой, как S, X или U.

Строка "6:885578193:2" означает: база данных с Id 6, объект с Id 885578193, и индекс с Id 2. Число в скобках — это хэшированное значение, используемое, чтобы определить блокировку. Вы можете увидеть это в столбце rsc_text таблицы master.dbo.syslockinfo. К сожалению, это односторонний хэш, что значит, что вы не можете найти заблокированную строку только при помощи хэшированного значения. Spid — это, конечно, системный процесс, наложивший блокировку.
Чтобы прочитать результат, нужно сначала посмотреть на узлы 1 и 2, которые показывают вам блокировки, уже разрешенные и вовлеченные в мертвую блокировку. После этого вы увидите две блокировки в очереди ожидания, помеченные "Requested By:".

1205: Выводит информацию о работе менеджера блокировок. Каждый раз, когда активизируется поиск мертвых блокировок, этот флаг трассировки заставляет менеджера блокировок выводить информацию о поиске. Работает, только если установлен флаг трассировки 1204.

1211: Отключает все поднятия уровня блокировок. Этот флаг трассировки не дает менеджеру блокировок поднимать уровень блокировок, даже если заканчиваются слоты блокировок.

Как вы знаете, самой маленькой единицей блокировки в SQL Server 2000 является блокировка строки. Блокировка столбцов недоступна напрямую в SQL Server. Я покажу вам, как можно использовать блокировку индекса для имитации блокировки уровня столбца.
Блокировки столбцов часто считаются слишком медленными для практического использования, и SQL Server не является исключением. Но т.к. блокировка строки не означает автоматически, что индекс таблицы заблокирован, то вы всегда можете использовать данные страниц индекса, чтобы работать с заблокированными данными. Опять же, я объясню это, используя таблицу Region базы данных Northwind.
Таблица Region — это таблица-куча с двумя столбцами, RegionDescription и RegionId. Уникальный некластерный индекс существует на столбце RegionId.

Сейчас мы изменим значение RegionDescription у одной из строк при помощи простой команды update, как показано ниже:

Чтобы выполнить этот запрос, SQL Server не может использовать индекс — индекс не покрывает столбец RegionDescription. Поэтому SQL Server использует полное сканирование таблицы, чтобы найти строку, требующую изменения. Когда он находит эту строку, он превращает update блокировку этой строки в эксклюзивную блокировку. Чтобы убедиться в этом, запустите SP_LOCK в другом окне Query Analyzer. Вы должны увидеть эксклюзивную RID блокировку на соответствующем объекте. В том же окне, в котором вы запустили SP_LOCK, вы можете выполнить следующий select:

Если только вы не указали READPAST или не установили уровень изоляции транзакции как read uncommitted, то вы будете ждать, пока первое соединение не снимет блокировку.
Это ожидаемый результат. Но вот где неожиданность:

В этот раз вы ждать не будете. Если вы похожи на меня, то вы любите смотреть на план выполнения, и именно план выполнения объясняет, почему нам не пришлось ждать.

Как видите, SQL Server выбрал сканирование индекса, чтобы получить нужные данные для выполнения запроса. И т.к. вы запросили информацию, которая может быть предоставлена чтением индекса, то SQL Server’у не нужно читать данные из таблицы-кучи. Этот тип запроса называется покрывающим запросом.
Существуют два критерия для этого случая. Первый — это то, что все нужные запросу данные содержатся в индексе. Помните, что если таблица имеет кластерный индекс, то все некластерные индексы содержат столбцы самого индекса и столбцы кластерного индекса.
Второй критерий — это то, что команда UPDATE не должна изменять любой из столбцов, содержащихся в индексе. Если индекс изменяется, то он должен быть заблокирован эксклюзивно и этот трюк не сработает.

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

Я действительно нашел ускользающую intent update блокировку, и еще много чего, когда искал ее. Блокировки и поведение блокировок плохо документированы в BOL, что сделало это исследование гораздо более трудным, чем это должно было быть. Я узнал много нового во время написания это статьи, и надеюсь, что поделился частью этих знаний с вами.

Andres Taylor пытается уделять одинаковое внимание его любимым занятиям: изучению SQL Server, Бразильскому Джиу-Джитсу и его красивой жене Мими. Он сдал 20 экзаменов MCP, является MCT и работает в Dotway в качестве консультанта по .NET/SQL Server.

Для отслеживания sql блокировок в SQL Server существует команда sp_who2. В результате выполнения данной команды мы получим список соединений с SQL Server, но вот находить блокировщиков придется вручную, анализируя колонку BlkBy.

В этой колонке мы увидим блокирующий процесс, который можно принудительно "убить" с помощью команды KILL ПРОБЕЛ SPID убиваемого процесса (напимер, KILL 22).

Скрипт для удобного использования команды sp_who2 при отслеживании sql блокировок

Однако если активных соединений с SQL Server очень много, то sql блокировки отслеживать трудно: приходится прокручивать весь список и вручную выбирать самых-самых "блокировщиков". Гораздо удобно создать временную таблицу и сделать сортировку по полю BlkBy. Готовый скрипт для обнаружения и устранения sql блокировок можно взять ниже.

DECLARE @Table TABLE( SPID INT, Status VARCHAR(MAX), LOGIN VARCHAR(MAX), HostName VARCHAR(MAX), BlkBy VARCHAR(MAX), DBName VARCHAR(MAX), Command VARCHAR(MAX), CPUTime INT, DiskIO INT, LastBatch VARCHAR(MAX), ProgramName VARCHAR(MAX), SPID_1 INT, REQUESTID INT)

INSERT INTO @Table EXEC sp_who2

SELECT DISTINCT BlkBy FROM @Table

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

Как узнать выполняемый блокирующим процессом скрипт или бонус к отслеживанию sql блокировок

Данную статью я решил дополнить очень, на мой взгляд, полезной вещью.

Увидели мы блокирующий процесс. А дальше что? Убить блокировщика, вызвавшего sql блокировку, всегда успеем. Но ведь нужно смотреть в корень и предупредить возникновение блокировок. А для этого надо узнать, какой скрипт выполняется под блокировщиком.

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

CREATE PROCEDURE PrintCurrentCode
@SPID int
AS
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int

SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE sp >

DECLARE @line nvarchar(4000)

SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end — @stmt_start) END) FROM ::fn_get_sql(@sql_handle))

Для запуска процедуры выполняем команду

Exec PrintCurrentCode SPID процесса

Команда выведет выполняемый блокировщиком код на языке TSQL, который мы с легкостью сможем проанализировать.

Удачи в отслеживании sql блокировок и отсутствия долгих транзакций!

Понравилась статья? Не очень? Тогда возможно, что вы напишите свою гораздо лучше. Просто перейдите по ссылке Размещение статей и ознакомьтесь с правилами публикации статей на сайте MultiBlog67.RU .

Чаще всего deadlock описывают примерно следующим образом:
Процесс 1 блокирует ресурс А.
Процесс 2 блокирует ресурс Б.
Процесс 1 пытается получить доступ к ресурсу Б.
Процесс 2 пытается получить доступ к ресурсу А.
В итоге один из процессов должен быть прерван, чтобы другой мог продолжить выполнение.
Но это простейший вариант взаимной блокировки, в реальности приходится сталкиваться с более сложными случаями. В этой статье мы расскажем с какими взаимными блокировками в MS SQL нам приходилось встречаться и как мы с ними боремся.

При использовании транзакций с уровнем изоляции serializable могут происходить любые взаимные блокировки. При использовании уровня изоляции repeatable read некоторые из описанных ниже взаимных блокировок не могут произойти. У транзакций с уровнем изоляции read committed могут возникнуть только простейшие взаимные блокировки. Транзакция с уровнем изоляции read uncommitted практически не влияет на скорость работы других транзакций и в ней не могут возникнуть взаимные блокировки из-за чтения, так как она не накладывает shared блокировки (правда могут быть взаимные блокировки с транзакциями изменяющими схему БД).

Уровни изоляции транзакции сильно влияют на скорость работы системы и без учета взаимных блокировок, поэтому очень важно правильно выбирать уровень изоляции в зависимости от задачи, которую выполняет транзакция. Вот примерные советы:

Если транзакция изменяет данные в БД и при этом проверяет, чтобы эти данные не противоречили уже существующим записям в БД, то для нее скорее всего нужен уровень изоляции serializable. Но если вставка новых записей в параллельных транзакциях никак не может повлиять на результат текущей транзакции то можно использовать уровень изоляции repeatable read.
Для чтения данных обычно достаточно использовать уровень изоляции по умолчанию (read committed) без какой либо транзакции. Однако при чтении агрегатов, части которых могут быть изменены во время чтения, может понадобится использовать транзакцию с уровнем изоляции repeatable read или даже serializable, иначе можно получить из базы агрегат в некорректном состоянии, в котором он может быть только в процессе выполнения транзакции изменения.
Если необходимо отображать real time статистику по постоянно изменяющимся данным, то зачастую лучше использовать уровень изоляции read uncommitted. В этом случае в статистике будет некоторое количество грязных данных (хотя вряд ли это будет заметно), но зато построение отчетов практически не будет влиять на скорость работы системы.

В достаточно сложной системе, насчитывающей десятки разнообразных типов бизнес транзакций, вряд ли получится спроектировать все транзакции таким образом, чтобы deadlock не мог возникнуть ни при каких условиях. Не стоит тратить время на предотвращение взаимных блокировок, вероятность возникновения которых крайне мала. Но, чтобы не портить user experience, в случае, когда операция прерывается из-за взаимной блокировки, ее нужно повторить. Для того, чтобы операцию можно было безопасно повторить, она не должна изменять входные данные и должна быть обернута в одну транзакцию (либо вместо всей операции, надо оборачивать в свой RetryOnDeadlock каждую SQL транзакцию в операции).

Вот пример функции RetryOnDeadlock на C#:

Важно понимать, что функция RetryOnDeadlock всего лишь улучшает user experience при изредка возникающих взаимных блокировках. Если они возникают очень часто, она лишь ухудшит ситуацию, в разы увеличив нагрузку на систему.

Если взаимная блокировка возникает из-за того, что два процесса обращаются к одним и тем же ресурсам но в разном порядке (как это описано в начале статьи), то достаточно поменять порядок блокировки ресурсов. В принципе, если в разных операциях блокируется определенный набор ресурсов, блокироваться первым всегда должен один и тот же ресурс, если это возможно. Этот совет применим не только к реляционным БД, но и вообще к любым системам, в которых возникают взаимные блокировки.

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

Наиболее часто встречаемая взаимная блокировка в нашей практике возникает в транзакциях с уровнем изоляции Repeatable read или Serializable следующим образом:

Транзакция 1 читает запись (накладывается S-блокировка).
Транзакция 2 читает эту же запись (накладывается вторая S-блокировка).
Транзакция 1 пытается изменить запись и ждет, когда транзакция 2 закончится и отпустит свою S-блокировку.
Транзакция 2 пытается изменить эту же запись и ждет, когда транзакция 1 закончится и отпустит свою S-блокировку

.
Для возникновения подобной взаимной блокировки достаточно одного ресурса, за который борются две однотипные транзакции. Борьба может вестись не только за одну запись но и за любой другой ресурс. Например, если две Serializable транзакции считают количество записей с определенным значением, а потом вставляют запись с таким же значением, то ресурсом, освобождение которого они ждут, будет ключ в индексе.

Чтобы избежать такой взаимной блокировки, необходимо, чтобы из двух транзакций, собирающихся изменить запись, прочитать ее могла только одна. Специально для этого была введена update блокировка. Ее можно наложить следующим образом:

Если вы используете ORM и не можете управлять тем, как запрашивается сущность из БД, то вам придется выполнить отдельный запрос на чистом SQL для блокировки записи прежде чем запрашивать ее из БД. Важно, что накладывающий update блокировку запрос должен быть первым запросом, обращающимся к этой записи в данной транзакции, иначе будет возникать все та же взаимная блокировка, но при попытке наложить update блокировку, а не при изменении записи.
Накладывая update блокировку мы заставляем все транзакции, обращающиеся к одному ресурсу, выполняться по очереди, но обычно транзакции изменяющие один и тот же ресурс в принципе нельзя делать параллельно, так что это нормально.
Такая взаимная блокировка может возникнуть в любой транзакции, которая проверяет данные перед их изменением, но для редко изменяющихся сущностей, можно использовать RetryOnDeadlock. Подход с предварительной update блокировкой достаточно использовать только для сущностей, которые часто меняются разными процессами параллельно.

Пример
Пользователи заказывают призы за баллы. Количество призов каждого вида ограниченно. Система не должна позволить заказать больше призов, чем есть в наличии. Из-за особенностей промоакции периодически происходят набеги пользователей, желающих заказать один и тот же приз. Если использовать RetryOnDeadlock в данной ситуации, то во время набега пользователей заказ приза в большинстве случаев будет падать по web таймауту.

Если мы храним количество оставшихся призов в записи о виде приза, то транзакция заказа приза должна выглядеть следующим образом:

Получаем запись о виде приза, накладывая update блокировку.
Проверяем количество оставшихся призов. Если оно равно 0, завершаем транзакцию и возвращаем соответствующий ответ пользователю.
Если призы еще есть, уменьшаем количество оставшихся призов на 1.
Добавляем запись о заказанном призе.

Таким образом мы позволяем в один момент заказывать один и тот же вид приза только одному пользователю. Все запросы пользователей на заказ одного и того же вида приза выстраиваются в очередь, но такой подход дает более хороший user experience, чем RetryOnDeadlock или вывод ошибки потребителю при возникновении взаимной блокировки.
Если не хранить количество оставшихся призов, а высчитывать его на основании количества заказанных призов, то update блокировку можно накладывать при вычислении количества заказанных призов. Выглядеть это будет примерно следующим образом:

Большинство взаимных блокировок, описанных далее, происходят похожим образом — мы пытаемся изменить данные после того как наложили на них Shared блокировку. Но в каждом из этих случаев есть свои нюансы.

Если мы в serializable транзакции ищем запись по полю не входящему ни в один индекс, то shared блокировка будет наложена на всю таблицу. По другому нельзя убедиться, что ни одна другая транзакция не сможет вставить запись с таким же значением до завершения текущей транзакции. В итоге любая транзакция делающая выборку по этому полю, а потом изменяющая эту таблицу, будет взаимно блокироваться с любой подобной же транзакцией.

Если же добавить индекс по этому полю (или индекс по нескольким полям, первым из которых является поле, по которому мы ищем), то блокироваться будет ключ в этом индексе. Так что в serializable транзакциях еще более важно задумываться есть ли индекс по колонкам, по которым вы ищете записи.

Есть еще один нюанс, о котором важно помнить: если индекс уникален, то блокировка накладывается только на запрашиваемый ключ, а если неуникален, то также блокируются cледующее за этим ключом значение. Две транзакции, запрашивающие разные записи по неуникальному индексу, а потом изменяющие их, могут взаимно блокироваться, если запрашиваются соседние значения ключей. Обычно это редкая ситуация и достаточно использовать RetryOnDeadlock, чтобы избежать проблем, но в некоторых случаях может потребоваться накладывать update блокировку при вытаскивании записей по неуникальному ключу.

Пример
Нам необходимо проверить, есть ли в БД пользователь с таким >
У нас эта проблема возникла при параллельном импорте потребителей от новых клиентов (для каждого клиента мы создаем новую пустую БД). Так как нас на данный момент устраивает скорость однопоточного импорта, мы просто отключили параллелизм. Но в принципе проблема решается также как и в выше описанном примере, надо использовать update блокировку:

В этом случае при многопоточном импорте в пустую базу по началу потоки будут простаивать, ожидая пока освободится блокировка, но по мере заполнения базы степень параллелизма будет возрастать. Хотя если импортируемые данные упорядочены по FacebookId, то параллельно импортировать их не получится. При импорте в пустую базу такого упорядочивания стоит избегать (либо не проверять наличие пользователей в БД по FacebookId при первом импорте).

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

Пример
В БД хранится персональные данные потребителя, его идентификаторы в соц сетях, заказы в интернет магазине, записи об отправленных ему письмах.

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

Для избежания взаимных блокировок нужно начинать любую транзакцию со следующего запроса:

В этом случае в один момент только одна транзакция сможет изменять данные, относящиеся к конкретному потребителю и взаимные блокировки не будут возникать в независимости от того насколько сложен агрегат потребителя.
Можно попробовать изменить схему хранения данных так, чтобы транзакции, отправляющие письма и регистрирующие покупки не меняли технические пометки в потребителе. Тогда информацию о заказах и отправленных письмах можно будет изменять параллельно с изменением потребителя. В этом случае мы фактически выносим эти данные за рамки агрегата «потребитель».

Если обобщать, советы по работе с агрегатами можно описать следующим образом:

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

в любой транзакции, изменяющей агрегат, первый запрос, обращающийся к данным агрегата, должен накладывать exclusive или update блокировку на корень агрегата

для увеличения степени параллелизма системы надо делать агрегаты настолько маленькими насколько это возможно

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

Подобные взаимные блокировки возникают при очень специфичных условиях, но пару раз мы с ними все-таки сталкивались, так что о них тоже стоит рассказать.

Пример

При отправке письма из DirectCRM в email-шлюз потребителю выдается действие о факте отправки (одна запись в БД). Id действия это обычный identity, увеличивающийся на один для каждой следующей записи. При успешной отправке письма в почтовый сервер, email-шлюз сообщает об этом DirectCRM, и CRM выдает действие об успешной отправке письма, которое ссылается на действие о факте отправки (ссылка хранится в таблице HierarchicalCustomerActions). Для того, чтобы операция обработки сообщения из email-шлюза была идемпотентной (зачем это нужно можно прочитать в предыдущей статье), мы проверяем не выдалось ли действие об успешной отправке до этого (в serializable транзакции). При такой проверке накладывается shared блокировка на ключ в индексе по RootCustomerActionId, соответствующий факту отправки письма. Но действие об отправке — первое ссылающиеся на факт отправки и в момент его выдачи в таблице HierarchicalCustomerActions нет ни одной записи с таким RootCustomerActionId. Поэтому shared блокировка будет наложена на все ключи между двумя существующими. Так как Id действия — identity, то очень часто проверяемый RootCustomerActionId оказывается больше любого из тех, которые уже есть в таблице и блокировка накладывается на все ключи больше или равные максимальному значению RootCustomerActionId в таблице. В email-шлюзе отправка сообщения происходит в несколько потоков и в итоге очень часто возникает следующая ситуация:

DirectCRM добавляет действия о факте отправки с Id N, N+1, N+2 и т. д.
Email-шлюз параллельно обрабатывает отправку всех этих писем.
В момент выдачи действий об отправке, максимальное значение RootCustomerActionId равняется N-1.
При проверке нет ли записи об успешной отправке письма N, N+1, N+2 и т.д., накладывается shared блокировка на записи с RootCustomerActionId больше или равные N-1.
Каждая из транзакций пытается вставить свою запись об отправке и ждет когда другая транзакция освободит shared блокировку.
В итоге из всех параллельно выполняющихся транзакций выполнится только одна, а остальные будут откачены.

Если накладывать update блокировку при запросе к HierarchicalCustomerActions, то взаимных блокировок не будет, но и параллельной обработки тоже — все транзакции будут ждать пока закончится транзакция наложившая update блокировку.
Есть следующие решения этой проблемы: