T-SQL - 強制執行原子操作 x 鎖

我有一個類似於銀行帳戶的數據庫模型（一個用於操作的表，一個用於更新余額的觸發器）。我目前正在使用 SQL Server 2008 R2。

表OPERATIONS：

VL_CREDIT decimal(10,2)
VL_DEBIT decimal(10,2)

表BALANCE：

DT_OPERATION datetime
VL_CURRENT decimal(10,2)

程序INSERT_OPERATION

GET LAST BALANCE BY DATE
CHECK IF VALUE OF OPERATION > BALANCE
  IF > RETURN ERROR
  ELSE INSERT INTO OPERATION(...,....)

我遇到的問題如下：

插入操作的過程必須在插入操作之前檢查餘額以查看是否有可用資金，因此餘額永遠不會變為負數。如果沒有餘額，我會返回一些程式碼告訴使用者餘額不夠。

我擔心的是：如果這個過程被連續呼叫多次，我怎麼能保證它是原子的？

我有一些想法，但我不確定哪個可以保證：

• BEGIN TRANSACTION在OPERATION程序中
• 選擇表的某種鎖定BALANCE，但它必須保持到過程執行結束

你能建議一些方法來保證嗎？提前致謝。

您可以在會話中使用兩種不同的隔離級別，這將確保在事務開始時讀取的數據不會改變，直到具有下面提到的隔離級別之一的事務被送出

BOL關於隔離級別的文章

可序列化

語句無法讀取已被其他事務修改但尚未送出的數據

在目前事務完成之前，任何其他事務都不能修改目前事務已讀取的數據。

在目前事務完成之前，其他事務不能插入鍵值落在目前事務中的任何語句讀取的鍵範圍內的新行。

可重複閱讀

指定語句不能讀取已被其他事務修改但尚未送出的數據，並且在目前事務完成之前沒有其他事務可以修改目前事務已讀取的數據。

兩者的主要區別在於可序列化的隔離級別防止在行範圍內的任何位置插入新數據。

USE MYDATABASE
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
sp_insert_operation
COMMIT TRANSACTION

還可以選擇在數據庫級別設置快照隔離，但我認為這不會為您提供所需的隔離級別。

這裡是相關文件ALTER DATABASE看下快照隔離

可序列化的更新

http://blogs.msdn.com/ 可 序列化隔離級別依賴於悲觀並發控制。它通過假設兩個事務可能嘗試更新相同的數據並使用鎖來確保它們不會但以降低並發性為代價來保證一致性——一個事務必須等待另一個事務完成

為了防止這種情況：您可以透支，因為兩個借記交易同時執行，您必須執行以下操作：（使用此表設計）

如果您的業務邏輯要求您首先進行選擇（根據您的範例）

BEGIN TRAN

SELECT VL_CURRENT
FROM   BALANCE
WHERE  conditions
WITH (xlock,rowlock)

--DO business validation

INSERT INTO OPERATION(columns)
VALUES(values)

UPDATE BALANCE TABLE

COMMIT TRAN

關鍵是，從讀取餘額行到更新它，您對余額行持有排他鎖。這樣，只要任何讀取隔離級別為已送出或更高但不是快照的餘額表的語句都將被阻塞，因此您不會出現透支情況。

如果您的業務邏輯可以只更新 BALANCE 表，您可以嘗試：

BEGIN TRAN
UPDATE BALANCE
SET VL_CURRENT=NewBalance
WHERE accountID=@ID

--Do business logic here
--If business logic fails Rollback
--If success continue with operation insert

INSERT INTO OPERATION(columns)
    VALUES(values)

COMMIT TRAN

但是，索引器與影響鎖定的 where 子句相結合存在一些棘手的問題，這可能會影響結果並肯定會影響並發性，因此最好提供包括索引在內的表定義。所以人們可以幫助你。

引用自：https://dba.stackexchange.com/questions/56991