Ошибка SQL Server 1205: взаимная блокировка, транзакция выбрана жертвой
Две сессии ожидали блокировки, удерживаемой другой. SQL Server откатил эту транзакцию, чтобы другая могла завершиться. Попробуйте выполнить ее повторно.
- Номер ошибки
1205- Уровень серьёзности
- 13
- Категория
- Транзакции и блокировки
Что это значит
Взаимная блокировка — это цикл ожидания блокировок: сеанс A удерживает блокировку, которая нужна B, в то время как B удерживает блокировку, которая нужна A. Монитор взаимных блокировок SQL Server обнаруживает цикл и завершает его, выбирая жертву, откатывая всю транзакцию этого сеанса и вызывая ошибку 1205. Жертвой становится сеанс с наименьшим DEADLOCK_PRIORITY, а среди сеансов с равными приоритетами — тот, откат которого будет наиболее экономичным.
Другой сеанс продолжается, как будто ничего не произошло. Взаимные блокировки — это обычное явление при параллельной работе; цель — сделать их редкими и повторять попытку при их возникновении.
Распространённые причины
- Два пути выполнения кода обновляют одни и те же таблицы или строки в противоположном порядке.
- Чтение при уровне изоляции по умолчанию read committed, ожидающее процесс записи, который, в свою очередь, ожидает общую блокировку читателя (часто встречается при поиске через некластеризованные индексы).
- Отсутствие индексов, из-за которых обновления и удаления сканируют и блокируют гораздо больше строк, чем изменяют.
- Долгие транзакции или взаимодействие пользователя в середине открытой транзакции.
- Более высокие уровни изоляции, такие как
SERIALIZABLEудерживающие блокировки диапазонов.
Как исправить
-
Повторите транзакцию в приложении, когда она завершится ошибкой 1205. В T-SQL цикл повторных попыток выглядит так:
DECLARE @tries int = 0; WHILE @tries < 3 BEGIN BEGIN TRY BEGIN TRANSACTION; UPDATE dbo.Accounts SET Balance -= 100 WHERE AccountId = 1; UPDATE dbo.Accounts SET Balance += 100 WHERE AccountId = 2; COMMIT; BREAK; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; IF ERROR_NUMBER() <> 1205 OR @tries = 2 THROW; SET @tries += 1; WAITFOR DELAY '00:00:00.100'; END CATCH END; -
Прочитайте граф взаимоблокировок. Встроенная
system_healthсеанс «Расширенных событий» записывает каждую взаимоблокировку.SELECT xed.value('@timestamp', 'datetime2') AS occurred_at, xed.query('.') AS deadlock_graph FROM (SELECT CAST(target_data AS xml) AS target_data FROM sys.dm_xe_session_targets AS t JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address WHERE s.name = N'system_health' AND t.target_name = N'ring_buffer') AS data CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed); -
Обращайтесь к таблицам и строкам в одном и том же порядке в каждой транзакции и делайте транзакции короткими.
-
Добавьте индексы, чтобы записи затрагивали только те строки, которые они изменяют; граф взаимоблокировок показывает, на каком индексе была каждая блокировка.
-
Подумайте об изоляции READ COMMITTED SNAPSHOT: читающие транзакции будут использовать версии строк вместо общих блокировок и перестанут вставать во взаимоблокировку с пишущими.
ALTER DATABASE SalesDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
-
Отмечайте менее важную работу как предпочтительную жертву.
SET DEADLOCK_PRIORITY LOW;
Безопасное воспроизведение
Выполните шаги в двух отдельных соединениях с тестовой базой данных в показанном порядке, затем откатите оставшуюся сессию и удалите таблицу.
-- Setup (once): CREATE TABLE dbo.DL (id int PRIMARY KEY, v int); INSERT INTO dbo.DL VALUES (1, 0), (2, 0); -- Session 1: BEGIN TRAN; UPDATE dbo.DL SET v = 1 WHERE id = 1; -- Session 2: BEGIN TRAN; UPDATE dbo.DL SET v = 2 WHERE id = 2; UPDATE dbo.DL SET v = 2 WHERE id = 1; -- waits -- Session 1: UPDATE dbo.DL SET v = 1 WHERE id = 2; -- one session gets Msg 1205
Вопросы
Пропадут ли мои данные после взаимоблокировки?
Вся транзакция жертвы откатывается, поэтому ни одно из её изменений не сохраняется. Остальные данные не затрагиваются. Запустите транзакцию снова.
Исправляет ли NOLOCK взаимоблокировки?
Он может помешать читателям брать общие блокировки, но допускает «грязное» и неконсистентное чтение (пропущенные или продублированные строки). Изоляция read committed snapshot устраняет блокировки читателей и писателей без этих рисков.
Связанные ошибки
1222Превышен период ожидания запроса блокировки3960Конфликт обновления в режиме изоляции снимка266Несоответствие количества транзакций после EXECUTE40P01PostgreSQL: зависимые объекты всё ещё существуют1213MySQL: Обнаружена взаимная блокировка при попытке получить блокировкуORA-00060Oracle: обнаружена взаимоблокировка при ожидании ресурса
Больше ошибок транзакций и блокировок
3930Транзакция не может быть зафиксирована3902COMMIT не имеет соответствующего BEGIN3903Для ROLLBACK нет соответствующего BEGIN3609Транзакция завершилась в триггере3616Ошибка, возникшая во время выполнения триггера6401Транзакция или точка сохранения не найдены3998Транзакция, которую невозможно зафиксировать, откачена3621Выполнение инструкции прервано
Код и текст сообщения взяты из Справочник по событиям и ошибкам Microsoft Database Engine. Смотрите весь Список ошибок SQL Server или поиск по всем ошибкам баз данных.
Работа с SQL Server в SQLly
Нативный SQL-клиент для macOS, Windows и Linux с IntelliSense, который знает вашу схему, предохранителями для рискованных запросов и понятными сообщениями об ошибках — для SQL Server и других баз данных, которые вы используете.