Оптимизация операций INSERT OR UPDATE в SQL Server

Быстрый ответ

Для выполнения операций INSERT и UPDATE в SQL Server рекомендуется применять оператор MERGE. Он даёт возможность определить, следует ли обновить уже существующую запись или добавить новую.

Пример кода:

MERGE INTO Target AS T
USING Source AS S
ON T.ID = S.ID
WHEN MATCHED THEN
    UPDATE SET T.Data = S.Data
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Data) VALUES (S.ID, S.Data);

Таким образом, если в таблице Target имеется запись с соответствующим ID, оператор обновляет столбец Data. В противном случае добавляется новая строка.

Транзакции для гарантии атомарности и потокобезопасности

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

Пример использования транзакций:

BEGIN TRANSACTION;
BEGIN TRY
    IF EXISTS (SELECT 1 FROM Target WHERE ID = @ID)
    BEGIN
        UPDATE Target SET Data = @Data WHERE ID = @ID;
    END
    ELSE
    BEGIN
        INSERT INTO Target (ID, Data) VALUES (@ID, @Data);
    END;

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO Target (ID, Data) VALUES (@ID, @Data);
    END;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
END CATCH;

Альтернативный подход: IF EXISTS вместо MERGE

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

Пример замены оператора MERGE:

IF EXISTS (SELECT 1 FROM Target WHERE ID = @ID)
BEGIN
    UPDATE Target SET Data = @Data WHERE ID = @ID;
END
ELSE
BEGIN
    INSERT INTO Target (ID, Data) VALUES (@ID, @Data);
END

Визуализация

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

Текущий плейлист 🎧: [Песня А, Песня Б]
Новые треки 🎶: [Песня Б*, Песня В]

Процедура INSERT OR UPDATE ведёт себя как диджей:

Обновлённый плейлист 🎧🔄🎶: [Песня А, Песня Б*🍎, Песня В]
# Существующие произведения обновляются (*🍎), новые добавляются в коллекцию

Как справиться с конкуренцией: Использование замков

Конкуренция может вызвать конфликты и взаимные блокировки. Применяйте updlock и holdlock (или serializable) для явного блокирования ресурсов в течение транзакции, чтобы предотвратить их изменение другими операциями до окончания блокировки.

Пример кода с блокировками:

MERGE INTO Target WITH (HOLDLOCK) AS T
USING Source AS S
ON T.ID = S.ID
WHEN MATCHED THEN
    UPDATE SET T.Data = S.Data
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Data) VALUES (S.ID, S.Data);

Использование HOLDLOCK сокращает риски, связанные с параллельным выполнением операций при объединении данных.

Подробнее о подходе UPSERT

Несмотря на гибкость оператора MERGE, важно применять его с осторожностью. Для комбинации UPDATE и INSERT следует использовать шаблон UPSERT, основанный на проверке @@ROWCOUNT.

Пример подхода UPSERT:

UPDATE Target SET Data = @Data WHERE ID = @ID;
IF @@ROWCOUNT = 0
BEGIN
    INSERT INTO Target (ID, Data) VALUES (@ID, @Data);
END

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

Полезные материалы

1. Осторожно, оператор MERGE в SQL Server — обзор лучших практик и внимательного использования MERGE.
2. MERGE (Transact-SQL) – SQL Server | Microsoft Learn — официальная документация Microsoft по оператору MERGE.
3. 6 методов блокировки в .NET (пессимистичные и оптимистичные) – CodeProject — обзор различных подходов к блокировке в .NET для работы с SQL Server.
4. Реализация UPSERT и MERGE в SQL Server 2008 — пошаговая инструкция для реализации UPSERT и MERGE в SQL Server.
5. Введение в транзакции SQL Server — основы управления транзакциями в SQL Server.