在SQL Server中使用種子表產生流水號注意順序

前幾天一個人問到了關於流水號重複的問題，我想了下，雖然說這個問題比較簡單，但是具有廣泛性，所以寫了這篇部落格來介紹下，希望對大家有所協助。

在進行資料庫應用開發時經常會遇到產生流水號的情況，比如說做了一個訂單模組，要求訂單號是唯一的，規則是：下訂單時的年月日+6位的流水號這樣的規則。

對於這種要產生流水號的系統，我們一般是在資料庫中建立了一個種子表，每次產生新的訂單時：

1.讀取當天種子最大值。

2.根據種子最大值和當時的年月日產生唯一的訂單號。

3.更新種子最大值，使最大值+1。

4.根據產生的訂單號將訂單資料插入到訂單表中。

以上幾步操作是在一個事務中完成，保證了流水號的連續。這個思路是正確的，使用起來好像也沒有什麼問題，但是在業務量比較大的情況下卻經常報錯：“訂單號違反主鍵約束，不能將重複的訂單號插入到訂單表中。”這是怎麼回事？讓我們做一個簡單的Demo來重現一下：

1.建立種子表和訂單表，這裡只是一個簡單的Demo，所以就省去了很多欄位，而且訂單號假設就是一個流水號，不用再使用年月日+6位流水號了。

CREATE TABLE Seek --種子表
(
    SeekValue INT
)
GO
INSERT INTO Seek VALUES(0)--種子初始值為0
GO
CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY, --訂單號，主鍵
    Remark VARCHAR(5) NOT NULL
)

 2.建立一個預存程序，該預存程序傳入Remark參數，根據產生的流水號插入到訂單表中：

CREATE PROC AddOrder --Author：深藍
@remark VARCHAR(5) --傳入的參數
AS
DECLARE @seek int 
BEGIN TRAN  --開啟一個事務
SELECT @seek=SeekValue --讀取種子表中的最大值作為流水號
FROM Seek

--產生訂單號這一步省略，因為這裡假定的訂單的編號就是流水號

UPDATE Seek SET SeekValue=@seek+1 --更新種子表，使最大值+1

INSERT INTO t1 VALUES(@seek,@remark) --插入一條訂單資料

COMMIT --提交事務

3.建立一個查詢時段，使用以下語句調用建立的預存程序，不斷的插入新訂單：

WHILE 1=1
EXEC AddOrder 'test1' --不斷的插入訂單

 

4.再建立一個查詢時段，使用通過的方式，不斷的插入新訂單，這樣用於類比高並發時候的情況：

WHILE 1=1
EXEC AddOrder 'test2'

 

5.運行了一段時間後，我們停止這兩個死迴圈，我們可以看到訊息視窗中存在大量的異常：

訊息 2627，層級 14，狀態 1，過程 AddOrder，第 11 行
違反了 PRIMARY KEY 約束 'PK__Orders__C3905BAF08EA5793'。不能在對象 'dbo.Orders' 中插入重複鍵。
語句已終止。

為什麼會這樣呢？這得從交易隔離等級和鎖來解釋：

一般我們寫程式時都是使用的是預設的交易隔離等級——已提交讀，在第一步查詢Seek表時，系統會為該表放置共用鎖定，而鎖的相容性中共用鎖定和共用鎖定是可以相容的，所以一個事務在讀取Seek表最大值時，其他事務也可以讀取出相同的最大值，兩個事務中讀取到了相同的最大值，所以產生了相同的流水號，所以產生了相同的訂單號，所以才會出現違反主鍵約束的錯誤。

既然知道了這其中的原理了，那麼解決辦法也就有了，只需要先對種子表中的數+1，然後再進行讀取即可，修改預存程序如下：

ALTER PROC AddOrder--Author：深藍
@remark VARCHAR(5)
AS
DECLARE @seek int
BEGIN TRAN

UPDATE Seek SET SeekValue=SeekValue+1  --先修改資料

SELECT @seek=SeekValue-1 --已經加了1，所以這裡-1下來
FROM Seek

INSERT INTO Orders VALUES(@seek,@remark)

COMMIT

 

為什麼這樣寫就可以呢？第一步執行更新操作，系統會請求更新鎖定然後再升級為獨佔鎖定，因為更新鎖定和更新鎖定以及獨佔鎖定都是不相容的，所以一個事務對Seek表進行了更新後，其他的事務就不能對錶進行更新操作，只有等到事務提交以後才能繼續。

這裡附上鎖相容性表：

	現有授予模式
請求模式	IS	S	U	IX	SIX	X
意圖共用 (IS)	是	是	是	是	是	否
共用 (S)	是	是	是	否	否	否
更新 (U)	是	是	否	否	否	否
意向排他 (IX)	是	否	否	是	否	否
意向排他共用 (SIX)	是	否	否	否	否	否
排他 (X)	否	否	否	否	否	否