資料庫存在即更新的並發處理 - 轉

標籤：基礎   隔離   else   不能   ges   前言   ups   object_id   exist   

前言

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

探討存在就更新七種方案

首先我們來建立測試表

IF OBJECT_ID(‘Test‘) IS NOT NULL    DROP TABLE TestCREATE TABLE Test(    Id int,    Name nchar(100),    [Counter] int,primary key (Id),    unique (Name));GO

解決方案一（開啟事務） 

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

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 );    COMMITGO

 

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

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

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 );    COMMITGO

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

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

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 );    COMMITGO

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

 

此時將隔離等級提升為最高隔離等級會解決插入重複鍵問題，但是對於更新來擷取排它鎖而未提交，而此時另外一個進程進行查詢擷取共用鎖定此時將造成進程間相互阻塞從而造成死結，所以從此知最高隔離等級有時候能夠解決並發問題但是也會帶來死結問題。

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

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

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 );    COMMITGO

 

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

解決方案五（提升隔離等級為資料列版本設定SNAPSHOT） 

ALTER DATABASE UpsertTestDatabaseSET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE UpsertTestDatabaseSET READ_COMMITTED_SNAPSHOT ONGO 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 );    COMMITGO

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

解決方案六（提升隔離等級+表變數）

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 );    COMMITGO 

 

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

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

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

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 );    COMMITGO

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

總結

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

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

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     COMMITGO

解決方案二（最高隔離等級 + 表變數）

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 );    COMMITGO

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

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 );    COMMITGO

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

2017-06-03更新

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

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

解決方案一（HOLDLOCK）

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     COMMITGO

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

解決方案二（UPDLOCK + HOLDLOCK）

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 );    COMMITGO

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

解決方案三（SERIALIZABLE + Merge）

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 );    COMMITGO

資料庫存在即更新的並發處理 - 轉