Дедлоки при update, insert

Как снизить риск дедлоков при INSERT / UPDATE в SQL Server

Дедлок (взаимная блокировка) возникает, когда две или более транзакций удерживают блокировки, необходимые друг другу, и ни одна не может завершиться. Для операций INSERT и UPDATE дедлоки – обычное явление, особенно при параллельной работе с одними и теми же диапазонами данных или индексами.

Основные причины дедлоков для INSERT/UPDATE

• Разный порядок доступа к ресурсам (таблицам, индексам, страницам).
• Блокировки на некластеризованных индексах – при INSERT/UPDATE блокируются все затронутые индексы, порядок их обновления может различаться между транзакциями.
• Обновление ключевых столбцов – может вызывать перемещение строки на другую страницу и блокировки сразу двух страниц.
• Слишком большие/долгие транзакции – удерживают блокировки дольше нужного.
• Повышенный уровень изоляции (SERIALIZABLE, REPEATABLE READ).

Практические способы снижения риска

1. Обеспечьте одинаковый порядок доступа ко всем ресурсам

Золотое правило: внутри всех транзакций обращайтесь к таблицам и индексам в одном и том же порядке.

❌ Плохо: одна транзакция обновляет Orders → OrderItems, другая – OrderItems → Orders.

✅ Хорошо: везде сначала Orders, потом OrderItems.

2. Используйте подсказки блокировок (только если уверены)

• UPDLOCK – при выборке строк, которые планируете обновить, захватывайте обновляющие блокировки, а не разделяемые. Это предотвращает классический deadlock «read-update».
• HOLDLOCK (или SERIALIZABLE) расширяет диапазон блокировок – используйте с осторожностью.
• NOLOCK (READ UNCOMMITTED) – только для чтения, для обновлений не применим.

BEGIN TRAN
SELECT * FROM Inventory WITH (UPDLOCK, ROWLOCK)
WHERE ProductId = 123;
-- обновляем на основе прочитанного
UPDATE Inventory SET Quantity = Quantity - 10 WHERE ProductId = 123;
COMMIT

3. Сократите время транзакции

• Не держите транзакцию открытой во время ввода-вывода или взаимодействия с пользователем.
• Выносите операции, не требующие атомарности, за пределы BEGIN TRAN...COMMIT.
• Разбивайте большие пакеты INSERT/UPDATE на порции (например, по 1000 строк) с короткими транзакциями.

4. Правильно проектируйте индексы

• Добавьте некластеризованные индексы по столбцам, используемым в WHERE для UPDATE или для поиска обновляемых строк.
• Для INSERT – избегайте избыточных индексов (каждый индекс требует блокировок при вставке).
• Используйте FILLFACTOR для кластеризованных индексов, чтобы уменьшить разбиение страниц.

5. Снизьте уровень изоляции (если допускают бизнес-требования)

Включите READ COMMITTED SNAPSHOT ISOLATION (RCSI) на уровне базы данных. Читающие не блокируют пишущих, а пишущие не блокируют читающих. Это сильно снижает дедлоки.

ALTER DATABASE YourDB SET READ_COMMITTED_SNAPSHOT ON;

Используйте SNAPSHOT ISOLATION для длинных отчётов – они не будут конфликтовать с обновлениями.

6. Обрабатывайте дедлоки на уровне приложения (retry)

Обязательно реализуйте повтор (retry) с ожиданием:

int retries = 3;
while (retries-- > 0)
{
    try
    {
        ExecuteCommand();
        break;
    }
    catch (SqlException ex) when (ex.Number == 1205) // deadlock victim
    {
        Thread.Sleep(new Random().Next(10, 200));
    }
}

7. Оптимизируйте сами запросы

• Для UPDATE используйте WHERE с индексированными столбцами.
• Старайтесь не обновлять столбцы, входящие в кластеризованный ключ.
• Избегайте UPDATE или INSERT внутри триггеров, которые делают дополнительные выборки из тех же таблиц.

8. Для массовых INSERT используйте меньше блокировок

INSERT INTO Target WITH (TABLOCK) (Col1, Col2)
SELECT ...

Используйте INSERT ... SELECT ... ORDER BY, чтобы вставка шла в порядке кластеризованного ключа.

9. Мониторинг и анализ

• Расширенные события (Extended Events) – ловят deadlock graph.
• sys.dm_tran_locks – посмотреть текущие блокировки.
• Флаг трассировки 1222 или 1204 – выводят deadlock graph в ERRORLOG.
• SET DEADLOCK_PRIORITY LOW для неважных транзакций.

Пример: снижение дедлока при UPDATE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
    DECLARE @qty int
    SELECT @qty = Quantity FROM Inventory WITH (UPDLOCK, ROWLOCK)
    WHERE ProductId = 123 AND WarehouseId = 456

    UPDATE Inventory SET Quantity = @qty - 10
    WHERE ProductId = 123 AND WarehouseId = 456
COMMIT

Памятка (кратко)

Самый эффективный и простой способ – включить RCSI (ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON;). Он решает большинство дедлоков между чтением и записью и не требует изменения кода приложений.

-- Посмотреть все активные соединения с вашей БД
SELECT session_id, login_name, status 
FROM sys.dm_exec_sessions 
WHERE database_id = DB_ID('aichat');

-- Узнать, что именно выполняет блокирующий процесс (замените 55 на реальный SPID)
DBCC INPUTBUFFER(55);

ALTER DATABASE aichat SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

SELECT name, is_read_committed_snapshot_on 
FROM sys.databases 
WHERE name = 'aichat';  -- или используйте DB_NAME() для текущей БД