2017年12月5日 星期二

[SQL Server] 案例分析 : 用IF EXISTS來進行交易的判斷真的沒有問題嗎?

案例情境
近幾天檢查資料庫客戶資料時意外發現有客戶的帳戶餘額為負值，這對公司來說是一個非常致命的錯誤，事關到公司營收。
便開始檢查是否被鑽了漏洞或者程式有邏輯錯誤。







在逐筆確認了交易的Log後，發現客戶的提款動作在短時間內重覆了兩次





此時心中開始咒罵 : 一定是哪個傢伙沒確認餘額狀況，就讓客戶可以領錢
於是乎去檢查了提款的Stored Procedure，發現以下這段邏輯

IF EXISTS(
    SELECT 1
    FROM dbo.MemberAccount
    WHERE MemberID = @MemberID
          AND ActualBalance >= @TransferAmount
)
BEGIN
    UPDATE dbo.MemberAccount
    SET ActualBalance = ActualBalance - @TransferAmount
    WHERE MemberID = @MemberID
END


明明就有做判斷，為何會被領到負值？
上面這段邏輯在思考上看起來是沒有問題的
Step 1. 先確認客戶的帳戶是否有足夠的錢可以提領
Step 2. 實際扣款

會發生扣到負值的原因是因為對於資料庫交易的行為不夠熟悉所造成的

先聊聊Select在交易中的行為
在SQL Server 交易中有四種基本的隔離層級 (先不談Snapshot)
  1. Read uncommitted
  2. Read committed
  3. Repeatable read
  4. Serializable

這四種隔離層級對於SELECT這行為鎖定的處理並不一樣
  1. Read uncommitted
    如果說到　NOLOCK　大家應該很熟悉，NOLOCK其實就是在語法上指定SELECT 採用的層級是READ UNCOMMITTED，但其實並不是真的如字面上所說的" NO LOCK",  其背後行為一樣也是有在Object或Row 上置放鎖定的，只是並不是大家常見Shared Lock，而是Sch-S  Lock，其目的就是為了避免在做髒讀取的時候有人去對Table的結構作異動。

  1. Read committed
    當你沒特別指定隔離層級時，資料庫預設的隔離層級就是Read committed，當在Read committed的隔離層級下進行 SELECT時， 其背後行為就是放上大家常聽到的Shared Lock，但此隔離層級有一個非常非常重要的設定：一段交易中的SELECT，在 SELECT 完的當下就會釋放掉Shared Lock，並不會保留到交易結束，這也是我等等會提到為什麼上面這段語法會有問題

    我們做個簡單的測試
    這段語法首先會把隔離層級設定為READ COMMITTED，接下來在Transaction 中做Select，在立刻去DMV查詢鎖定，最後在ROLLBACK。

    執行後我們會發現查詢鎖定的當下並沒有找到任何記錄，故可以了解到在    READ COMMITTED隔離層級下，查詢的鎖定 :    Shared Lock，在查詢完的當下會立刻釋放掉
    2. DECLARE @SessionID SMALLINT;

    SELECT @SessionID = @@SPID

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRAN

    SELECT *
    FROM [dbo].[MemberAccount]
    WHERE MemberID = 1003

    --確認是否有鎖定
    SELECT  request_session_id
          , resource_type
          , request_mode
          , DB_NAME([resource_database_id]) AS [Database Name]
          , CASE  WHEN DTL.resource_type  IN  ( 'DATABASE', 'FILE', 'METADATA' )
                  THEN DTL.resource_type
                  WHEN DTL.resource_type =  'OBJECT'
                  THEN  OBJECT_NAME(DTL.resource_associated_entity_id,
                                   DTL.[resource_database_id])
                  WHEN DTL.resource_type IN  ( 'KEY', 'PAGE', 'RID' )
                  THEN(
                          SELECT  OBJECT_NAME([object_id])
                          FROM sys.partitions
                          WHERE sys.partitions.hobt_id = DTL.resource_associated_entity_id
                      )
                 ELSE  'Unidentified'
            END  AS requested_object_name
          , [request_mode]
          , [resource_description]
    FROM sys.dm_tran_locks DTL
    WHERE DTL.[resource_type] <>  'DATABASE' AND request_session_id = @SessionID;

    ROLLBACK
    GO

  1. Repeatable Read

    此隔離層級是相較於Read committed更加一層嚴謹的隔離層級，它與READ COMMITED最大的差異在於：一樣會放Shared Lock但會保留到交易最後。

    我們將上面的語法隔離層級更改為
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

    接下來執行後我們就可以看到SELECT時所放的鎖定。

    為什麼會出現這樣的隔離層級，其目的就是為了避免　    在交易中兩次一樣語法的讀取會是不一樣的結果，也就是NON   REPEATABLE READ，日後有機會在分享。

  1. Serializiable

    最嚴謹的層級則是SERIALIZABLE，除了查詢的鎖會保留到最後，其鎖定並非只在單一個ROW，而是對查詢的條件相關範圍都會做鎖定。
    做個簡單測試    :

    DECLARE     @SessionID SMALLINT;

    SELECT @SessionID = @@SPID

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRAN

    SELECT *
    FROM [dbo].[MemberAccount]
    WHERE MemberID BETWEEN 10 AND 20

    --確認是否有鎖定
    SELECT  request_session_id
          , resource_type
          , request_mode
          , DB_NAME([resource_database_id]) AS [Database Name]
          , CASE  WHEN DTL.resource_type  IN  ( 'DATABASE', 'FILE', 'METADATA' )
                  THEN DTL.resource_type
                  WHEN DTL.resource_type =  'OBJECT'
                  THEN  OBJECT_NAME(DTL.resource_associated_entity_id,
                                   DTL.[resource_database_id])
                  WHEN DTL.resource_type IN  ( 'KEY', 'PAGE', 'RID' )
                  THEN(
                          SELECT  OBJECT_NAME([object_id])
                          FROM sys.partitions
                          WHERE sys.partitions.hobt_id = DTL.resource_associated_entity_id
                      )
                 ELSE  'Unidentified'
            END  AS requested_object_name
          , [request_mode]
          , [resource_description]
          , *
    FROM sys.dm_tran_locks DTL
    WHERE DTL.[resource_type] <>  'DATABASE' AND request_session_id = @SessionID;

    ROLLBACK
    GO

    查詢結果如下:

    我們可以得知10 到 20 的MemberID是沒有任何一筆資料的，但最下面的層級(Key Level)它一樣會放在RangeS-S　鎖定，用意是為了避免交易完成之前有人對MemberID 介於10 到 20 的範圍中插入任何一筆資料，進而避免鬼魅讀取(phantom read)



分析問題

想必看完上面敘述有人大概已經猜到Root Cause了
沒錯，答案就是   IF EXISTS (SELECT…) 在近乎同時間操作此交易時，由於Shared Lock並不互斥，且READ COMMITTED的預設層級下，會在讀取結束的瞬間馬上釋放掉，這段語法根本就不會把門擋住，而會同時判斷IF條件成功，進到BEGIN裡面。

我們利用以下Sample Code進行測試：
Step 1.  建立測試Table，並塞入一筆有一百萬餘額的帳戶
 
CREATE TABLE dbo.MemberAccount
(
    MemberID INT PRIMARY KEY,
    MemberName NVARCHAR(100) NOT NULL,
    ActualBalance MONEY NOT NULL
)
GO

INSERT INTO dbo.MemberAccount
(
    MemberID,
    MemberName,
    ActualBalance
)
VALUES (1, 'Max W', 1000000)
 

Step 2.
開啟兩個Session同時執行以下語法來提款，為了避免手腳不夠快，故意在BEGIN一開始等個10秒
 
DECLARE @TransferMoney MONEY = 1000000

BEGIN TRAN

IF EXISTS(
    SELECT *
    FROM dbo.MemberAccount
    WHERE MemberID = 1 AND ActualBalance >= @TransferMoney
)
BEGIN
    --故意做點延遲
    WAITFOR DELAY '00:00:10'

    UPDATE dbo.MemberAccount
    SET ActualBalance = ActualBalance - @TransferMoney
    WHERE MemberID = 1
END

COMMIT;


Step 3. 確認帳戶餘額
 
SELECT *
FROM dbo.MemberAccount




如同正式環境上所發生的，被重複提款了！

解決方案
我們了解到，並不是加了BEGIN TRAN以及加上IF EXISTS就可以解決問題，那解決方法是什麼? 上面所提到的交易層級往上拉高嗎? 錯! 由於Shared Lock是不互斥的，即便保留到交易最後還是有可能讓他找到時間差同時進到UPDATE那一段。

解決方法有兩種
1.   直接進行UPDATE，但將EXISTS中的WHERE條件帶到UPDATE中，如下：

UPDATE dbo.MemberAccount
SET ActualBalance = ActualBalance - @TransferMoney
WHERE MemberID = 1 AND ActualBalance >= @TransferMoney

由於UPDATE的行為會置放UPD  LOCK進而轉換成X    lock，這兩種鎖定都是互斥的，所以可以避免此問題發生。

如果在大部分情況都會判斷成功並進而更新的情況下，效能也較佳，因為不用重複存取MemberAccount兩次(Exists 一次，Update一次)。

 2.    IF EXISTS的查詢中自行加上exclusive lock的Hint

此種情況適合情境適合在IF EXISTS確認完後，並不是馬上更新Member Account，會在處理其它的商業邏輯。
DECLARE @TransferMoney MONEY = 1000000

BEGIN TRAN
IF EXISTS(
    SELECT *
    FROM dbo.MemberAccount WITH(XLOCK, ROWLOCK)
    WHERE MemberID = 1 AND ActualBalance >= @TransferMoney
)
BEGIN
    /*
    處理其它重要商業邏輯
    */

    UPDATE dbo.MemberAccount
    SET ActualBalance = ActualBalance - @TransferMoney
    WHERE MemberID = 1
END

COMMIT;
由於XLOCK是互斥且會保留到交易結束，透過此方法就能在入口先把這個Account給上鎖，避免其它Session進來干涉
而加上ROWLOCK則是為了避免要更新一個Account但卻鎖定了整個表，同時非常重要的是：要確認查詢條件是否有恰當的INDEX，不然鎖了整片，所有的交易效能都會被拖垮。

結語
在開發資料庫的時候，了解到鎖定的方法跟隔離層級是一定要做好的基本功，如果錯誤的使用，有可能造成公司系統漏洞導致嚴重的虧損。
張貼者： 於
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)