探讨SQL Server并發處理存在就更新七種解決方案

前言

本節我們來講講并發中最常見的情況存在即更新，在并發中若未存在行記錄則插入，此時未處理好極容易出現插入重複鍵情況，本文我們來介紹對并發中存在就更新行記錄的七種方案并且我們來綜合分析最合适的解決方案。

探讨存在就更新七種方案

首先我們來建立測試表

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('Test') IS NOT NULL
    DROP TABLE Test

CREATE TABLE Test
(
    Id int,
    Name nchar(100),
    [Counter] int,primary key (Id),
    unique (Name)
);
GO

探讨SQL Server并發處理存在就更新七種解決方案

解決方案一（開啟事務）

我們統一建立存儲過程通過來SQLQueryStress來測試并發情況，我們來看第一種情況。

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))

    BEGIN TRANSACTION
    IF EXISTS ( SELECT  1
                FROM    Test
                WHERE   Id = @Id )
        UPDATE  Test
        SET     [Counter] = [Counter] + 1
        WHERE   Id = @Id;
    ELSE
        INSERT  Test
                ( Id, Name, [Counter] )
        VALUES  ( @Id, @Name, 1 );
    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

同時開啟100個線程和200個線程出現插入重複鍵的幾率比較少還是存在。

解決方案二（降低隔離級别為最低隔離級别UNCOMMITED）

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    BEGIN TRANSACTION
    IF EXISTS ( SELECT  1
                FROM    Test
                WHERE   Id = @Id )
        UPDATE  Test
        SET     [Counter] = [Counter] + 1
        WHERE   Id = @Id;
    ELSE
        INSERT  Test
                ( Id, Name, [Counter] )
        VALUES  ( @Id, @name, 1 );
    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

此時問題依舊和解決方案一無異（如果降低級别為最低隔離級别，如果行記錄為空，前一事務如果未進行送出，目前事務也能讀取到該行記錄為空，如果目前事務插入進去并進行送出，此時前一事務再進行送出此時就會出現插入重複鍵問題）

解決方案三（提升隔離級别為最進階别SERIALIZABLE）

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
    IF EXISTS ( SELECT  1
                FROM    dbo.Test
                WHERE   Id = @Id )
        UPDATE  dbo.Test
        SET     [Counter] = [Counter] + 1
        WHERE   Id = @Id;
    ELSE
        INSERT  dbo.Test
                ( Id, Name, [Counter] )
        VALUES  ( @Id, @Name, 1 );
    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

在這種情況下更加糟糕，直接到會導緻死鎖

此時将隔離級别提升為最高隔離級别會解決插入重複鍵問題，但是對于更新來擷取排它鎖而未送出，而此時另外一個程序進行查詢擷取共享鎖此時将造成程序間互相阻塞進而造成死鎖，是以從此知最高隔離級别有時候能夠解決并發問題但是也會帶來死鎖問題。

解決方案四（提升隔離級别+良好的鎖）

此時我們再來在添加最高隔離級别的基礎上增添更新鎖，如下：

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
    IF EXISTS ( SELECT  1
                FROM    dbo.Test WITH(UPDLOCK)
                WHERE   Id = @Id )
        UPDATE  dbo.Test
        SET     [Counter] = [Counter] + 1
        WHERE   Id = @Id;
    ELSE
        INSERT  dbo.Test
                ( Id, Name, [Counter] )
        VALUES  ( @Id, @Name, 1 );
    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

運作多次均未發現出現什麼異常，通過查詢資料時使用更新鎖而非共享鎖，這樣的話一來可以讀取資料但不阻塞其他事務，二來還確定自上次讀取資料後資料未被更改，這樣就解決了死鎖問題。貌似這樣的方案是可行得，如果是高并發不知是否可行。

解決方案五（提升隔離級别為行版本控制SNAPSHOT）

探讨SQL Server并發處理存在就更新七種解決方案

ALTER DATABASE UpsertTestDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE UpsertTestDatabase
SET READ_COMMITTED_SNAPSHOT ON
GO 

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))

    BEGIN TRANSACTION
    IF EXISTS ( SELECT  1
                FROM    dbo.Test
                WHERE   Id = @Id )
        UPDATE  dbo.Test
        SET     [Counter] = [Counter] + 1
        WHERE   Id = @Id;
    ELSE
        INSERT  dbo.Test
                ( Id, Name, [Counter] )
        VALUES  ( @Id, @Name, 1 );
    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

上述解決方案也會出現插入重複鍵問題不可取。

解決方案六（提升隔離級别+表變量）

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
    DECLARE @updated TABLE ( i INT );

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION
    UPDATE  Test
    SET     [Counter] = [Counter] + 1
    OUTPUT  DELETED.Id
            INTO @updated
    WHERE   Id = @Id;

    IF NOT EXISTS ( SELECT  i
                    FROM    @updated )
        INSERT  INTO Test
                ( Id, Name, counter )
        VALUES  ( @Id, @Name, 1 );
    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

經過多次認證也是零錯誤，貌似通過表變量形式實作可行。

解決方案七（提升隔離級别+Merge）

通過Merge關鍵來實作存在即更新否則則插入，同時我們應該注意設定隔離級别為 SERIALIZABLE 否則會出現插入重複鍵問題，代碼如下：

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
    SET TRAN ISOLATION LEVEL SERIALIZABLE 
    BEGIN TRANSACTION
    MERGE Test AS [target]
    USING
        ( SELECT    @Id AS Id
        ) AS source
    ON source.Id = [target].Id
    WHEN MATCHED THEN
        UPDATE SET
               [Counter] = [target].[Counter] + 1
    WHEN NOT MATCHED THEN
        INSERT ( Id, Name, [Counter] )
        VALUES ( @Id, @Name, 1 );
    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

多次認證無論是并發100個線程還是并發200個線程依然沒有異常資訊。

總結

本節我們詳細讨論了在并發中如何處理存在即更新，否則即插入問題的解決方案，目前來講以上三種方案可行。

解決方案一（最高隔離級别 + 更新鎖）

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))

    BEGIN TRANSACTION;

    UPDATE  dbo.Test WITH ( UPDLOCK, HOLDLOCK )
    SET     [Counter] = [Counter] + 1
    WHERE   Id = @Id;

    IF ( @@ROWCOUNT = 0 )
        BEGIN
            INSERT  dbo.Test
                    ( Id, Name, [Counter] )
            VALUES  ( @Id, @Name, 1 );
        END

    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

解決方案二（最高隔離級别 + 表變量）

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
    DECLARE @updated TABLE ( i INT );

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION
    UPDATE  Test
    SET     [Counter] = [Counter] + 1
    OUTPUT  DELETED.id
            INTO @updated
    WHERE   id = @id;

    IF NOT EXISTS ( SELECT  i
                    FROM    @updated )
        INSERT  INTO Test
                ( Id, Name, counter )
        VALUES  ( @Id, @Name, 1 );
    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

解決方案三（最高隔離級别 + Merge）

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
    SET TRAN ISOLATION LEVEL SERIALIZABLE 
    BEGIN TRANSACTION
    MERGE Test AS [target]
    USING
        ( SELECT    @Id AS Id
        ) AS source
    ON source.Id = [target].Id
    WHEN MATCHED THEN
        UPDATE SET
               [Counter] = [target].[Counter] + 1
    WHEN NOT MATCHED THEN
        INSERT ( Id, Name, [Counter] )
        VALUES ( @Id, @Name, 1 );
    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

暫時隻能想到這三種解決方案，個人比較推薦方案一和方案三，請問您有何高見，請留下您的評論若可行，我将進行後續補充。

2017-06-03更新

本博文的評論非常精彩，同時對于小菜的我又重新學習了下存在即更新反之則插入的解決方案。本文重新更新已經過了兩天，期間我是一直在看這方面的東西更加深入的了解有些基礎方面的東西還是說的太籠統并且是我自身不是很了解而導緻，菜不可怕，可怕的是還不深入學習自認為自己的是對的，你說呢。

首先我們得了解UPDLOCK和HOLDLOCK鎖的作用是什麼，HOLDLOCK類似于SERIALIZABLE隔離級别，對于共享鎖我們是可以讀，但是不能進行更新和删除和插入直到目前并發事務完成，而UPDLOCK園中博文的解釋：是允許您讀取資料（不阻塞其它事務）并在以後更新資料，同時確定自從上次讀取資料後資料沒有被更改。當我們用它來讀取記錄時可以對取到的記錄加上更新鎖，進而加上鎖的記錄在其它的線程中是不能更改的隻能等本線程的事務結束後才能更改。通俗易懂點說，它不會阻塞并發的查詢和插入操作，但是會阻塞更新或者删除對于目前事務查詢出的資料，當查詢到該資料存在時則有更新鎖切換到排它鎖。是以對于上述結尾總結的三種解決方案，我們再來闡述下。

解決方案一（HOLDLOCK）

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))

    BEGIN TRANSACTION;

    UPDATE  dbo.Test WITH ( HOLDLOCK )
    SET     [Counter] = [Counter] + 1
    WHERE   Id = @Id;

    IF ( @@ROWCOUNT = 0 )
        BEGIN
            INSERT  dbo.Test
                    ( Id, Name, [Counter] )
            VALUES  ( @Id, @Name, 1 );
        END

    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

如果我們未加上HOLDLOCK鎖提示，雖然UPDATE會擷取排它鎖，但是排它鎖不會持續到事務結束一直保持着是以會導緻插入重複鍵的問題，當我們加上HOLDLOCK鎖提示上述也說到類似悲觀并發中的最高隔離級别，該鎖提示一直會持續到事務結束，當有并發請求過來時，若此時查詢到資料存在則會進行更新操作但是事務還未進行送出，此時其他請求将會也查到該行記錄存在，但是會被目前的事務更新操作鎖阻塞，若此時查詢到資料不存在時同理如此。

解決方案二（UPDLOCK + HOLDLOCK）

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))

    BEGIN TRANSACTION
    IF EXISTS ( SELECT  1
                FROM    dbo.Test WITH(UPDLOCK, HOLDLOCK)
                WHERE   Id = @Id )
        UPDATE  dbo.Test
        SET     [Counter] = [Counter] + 1
        WHERE   Id = @Id;
    ELSE
        INSERT  dbo.Test
                ( Id, Name, [Counter] )
        VALUES  ( @Id, @Name, 1 );
    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

對于上述查詢對比第一種解決方案我們加上了UPDLOCK更新鎖代替SELECT的共享鎖，目的是當所傳遞的變量Id所查詢的行記錄不存在時不會導緻阻塞，讓其進行插入，也就是說不阻塞其他事務的插入并確定自上次以來行記錄未被修改過，對于HOLDLOCK為了確定一直到事務釋放鎖，進而達到我們的期望。總結起來一句話，如果查詢期間行記錄存在則鎖定的資源則查詢存在的行記錄上，如果查詢期間行記錄不存在，那麼通過HOLDLOCK來擷取主鍵上的範圍鎖來防止在釋放鎖之前插入重複鍵，是以UPDLOCK為了解決并發更新不阻塞其他事務查詢，HOLDLOCK防止并發插入重複鍵。

解決方案三（SERIALIZABLE + Merge）

探讨SQL Server并發處理存在就更新七種解決方案

IF OBJECT_ID('TestPro') IS NOT NULL
    DROP PROCEDURE TestPro;
GO

CREATE PROCEDURE TestPro ( @Id INT )
AS
    DECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))
    BEGIN TRANSACTION
    MERGE Test WITH(SERIALIZABLE ) AS [target]
    USING
        ( SELECT    @Id AS Id
        ) AS source
    ON source.Id = [target].Id
    WHEN MATCHED THEN
        UPDATE SET
               [Counter] = [target].[Counter] + 1
    WHEN NOT MATCHED THEN
        INSERT ( Id, Name, [Counter] )
        VALUES ( @Id, @Name, 1 );
    COMMIT
GO

探讨SQL Server并發處理存在就更新七種解決方案

探讨存在就更新七種方案

解決方案一（開啟事務）

解決方案二（降低隔離級别為最低隔離級别UNCOMMITED）

解決方案三（提升隔離級别為最進階别SERIALIZABLE）

解決方案四（提升隔離級别+良好的鎖）

解決方案五（提升隔離級别為行版本控制SNAPSHOT）

解決方案六（提升隔離級别+表變量）

解決方案七（提升隔離級别+Merge）

總結

解決方案一（最高隔離級别 + 更新鎖）

解決方案二（最高隔離級别 + 表變量）

解決方案三（最高隔離級别 + Merge）

2017-06-03更新

解決方案一（HOLDLOCK）

解決方案二（UPDLOCK + HOLDLOCK）

解決方案三（SERIALIZABLE + Merge）

結論

繼續閱讀

【Ubuntu報錯】ModuleNotFoundError: No module named 'gdbm'

mysql5.7的sql優化

線程通信和程序通信差別（線程程序差別）

Matlab随機波動率SV、GARCH用MCMC馬爾可夫鍊蒙特卡羅方法分析匯率時間序列

win8離線和線上均無法安裝.net framework 3.5 問題解決方案1 Win8/8.1無法安裝.net framework3.5的解決方法2 使用dism指令安裝時存在的問題解決方案

微信小程式前端解密擷取使用者資訊

Spring MVC 自學雜記（五） -- SpringMVC與前台的json資料互動

《MySQL技術内幕：InnoDB存儲引擎》筆記

擴容TIKV節點遇到的坑

PHP輔導代做程式設計：CS353 Database System

自學Zabbix3.10.2-事件通知Notifications upon events-Actions報警配置點選傳回：自學zabbix集錦

HDU 5678 ztr loves trees

拓端tecdat|R語言彈性網絡Elastic Net正則化懲罰回歸模型交叉驗證可視化

二叉樹及其應用--二叉樹建立

智慧文旅數字孿生解決方案

詳解STM32單片機的堆棧