SqlServer 高並發的情況下，如何利用鎖保證數據的穩定性

sql的鎖機制，是時刻貫徹在每一次的sql事務中的，為了理解更透徹，介紹鎖之前，我們得先了解，鎖是為了幹什麼！！

1、資料庫異常情況　　

　　1.1 先來聊聊數據可能發生個異常狀況

　　　　1）髒讀：讀未提交，顧名思義，讀到了不該讀的東西，如：

　　　　事務B讀到了事務A回滾的數據，就是髒讀　　

　　　　2）不可重複讀：讀已提交，同個事務內，多次讀取同個數據，卻返回不同結果，偏向數據更新

　　　　事務B發生了不可重複讀

　　　　3）幻讀：同個事務內，因其他事務插入或刪除數據，導致讀取到不同的數據量(本質和不可重複讀相似)

　　　　事務B發生了幻讀

　　1.2 資料庫用什麼機制來處理這些異常情況的發生，四種隔離級別

　　　　1）讀未提交（Read Uncommitted）：發生髒讀，基本沒有資料庫使用這個級別了

　　　　2）讀已提交（Read Committed）：大多數資料庫系統的默認隔離級別，解決了髒讀問題

　　　　3）可重複讀（Repeatable Read）：同一事務的多個實例在並發讀取數據時，會看到同樣的數據，解決了不可重複讀問題

　　　　4）可串行化（serializable）：這是最高的隔離級別，它通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。簡言之，它是在每個讀的數據行上加上共享鎖。但可能導致大量的超時現象和鎖競爭

　　　　總結：四種級別越往後越影響性能，但數據越穩定，實現機制就是"鎖";

2、都有什麼鎖

　　2.1鎖的種類：

　　　　1）共享鎖：其他事務可select，無法被update、delete、insert

　　　　2）排他鎖：其他事務不可任何操作

　　2.2粒度提示：

　　　　1）rowlock：行鎖，指定到行，select * from dual where id=1會默認行鎖

　　　　2）paglock：頁鎖，select * from dual會默認頁鎖，select的時候先鎖定第一頁，讀取後釋放，再鎖定第二頁，直到讀完

　　　　3）tablock：表鎖，語句結束解鎖

　　　　4）tabockx：表鎖，排他鎖。

　　　　5）nolock：取消默認鎖，涉及大量刪除數據的時候可能會堵塞進程，如果需要select，可以加上nolock來過濾掉需要刪除的數據

　　　　6）holdlock：保持共享鎖，資料庫會根據sql操作加默認鎖

　　　　7）serializable：同holdlock

　　　　8）readcommited：遵循讀已提交隔離級別

　　　　9）updlock：更新鎖，排他鎖

3、舉例子

　　3.1 模擬場景，多個客戶在搶一個優惠券，利用updlock保證數據準確性，具有排他性，悲觀並發控制

　　　　1）沒有鎖的情況，客戶kxy&客戶keys搶券Id=2

　　　　owner='kxy' 和 owner='keys' 先後同時執行以下代碼

begin tran
    declare @_owner varchar(100);
    set @_owner = (select owner from Coupons where id=2);
    if(@_owner is null or @_owner='')
        begin
            update Coupons  set owner='kxy' where id=2
            waitfor delay '00:00:10'
            print '恭喜您，搶到了!!'
        end;
    else
        print '該券已經被搶了!!'
commit tran

　　　　結果是：兩邊都提示搶到券了，資料庫會記錄後update的客戶，這無疑是錯誤的

　　　　該怎麼解決呢？利用更新鎖，因為具有排他性，在事務提交之前不允許其他事務查詢

　　　　2）帶更新鎖

　　　　owner='kxy' 和 owner='keys' 先後同時執行以下代碼

begin tran
    declare @_owner varchar(100);
    set @_owner = (select owner from Coupons with(updlock) where id=2);
    if(@_owner is null or @_owner='')
        begin
            update Coupons  set owner='kxy' where id=2
            waitfor delay '00:00:10'
            print '恭喜您，搶到了!!'
        end;
    else
        print '該券已經被搶了!!'
commit tran

　　　　結果：

　　　　　　kxy

　　　　　　keys

　　3.2 定義鑒權欄位，保證數據穩定性，共享性，樂觀並發控制

update Coupons set owner='kxy' 
    where id=2 
    and (owner='' or owner is null);

　　　　這將owner定義為鑑權欄位，update數據的時候具有共享鎖，高並發情況下，當第一條update語句執行成功，owner便有了值，後續更新語句返回(0 行受影響)

　　　　即：鑒權欄位=舊值，可提高數據穩定性

　　3.3 額外添加鑒權欄位

　　　　沒有方便可用的鑒權欄位，可用自己加一個RowVersion類型欄位

CREATE TABLE [dbo].[Coupons] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Name]        NVARCHAR (MAX) NOT NULL,
    [Description] NVARCHAR (MAX) NULL,
    [Owner]       NVARCHAR (MAX) NULL,
    [RowVersion]  ROWVERSION     NOT NULL, #當數據更新時，版本會自動遞增
    CONSTRAINT [PK_Coupons] PRIMARY KEY CLUSTERED ([Id] ASC)
);

　　　　owner='kxy' 和 owner='keys' 先後同時執行以下代碼

begin tran
    declare @_owner varchar(100);
    declare @_rowversion rowversion;
    --set @_owner,@_rowversion = (select owner,RowVersion from Coupons where id=2);
    select @_owner=owner,@_rowversion=RowVersion from Coupons where id=2
    if(@_owner is null or @_owner='')
        begin
            waitfor delay '00:00:10'
            update Coupons  set owner='kxy' where id=2 and RowVersion=@_rowversion
            if @@ROWCOUNT = 0
                begin
                    print '該券已經被搶了!!'
                end;
            else
                print '恭喜您，搶到了!!'
        end;
    else
        print '該券已經被搶了!!'
commit tran

　　　　結果：

　　　　　　kxy

　　　　　　keys