SQL Server中流水號生成的注意事項

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

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

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

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

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

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

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

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

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

　　CREATETABLESeek--種子表
　　(
　　SeekValueINT
　　)
　　GO
　　INSERTINTOSeekVALUES(0)--種子初始值為0
　　GO
　　CREATETABLEOrders
　　(
　　OrderIDINTPRIMARYKEY,--訂單號，主鍵
　　RemarkVARCHAR(5)NOTNULL
　　)

　　2.建立一個儲存過程，該儲存過程傳入Remark引數，根據生成的流水號插入到訂單表中：

　　CREATEPROCAddOrder--Author：深藍
　　@remarkVARCHAR(5)--傳入的引數
　　AS
　　DECLARE@seekint
　　BEGINTRAN　--開啟一個事務
　　SELECT@seek=SeekValue--讀取種子表中的最大值作為流水號
　　FROMSeek
　　--生成訂單號這一步省略，因為這裡假定的訂單的編號就是流水號
　　UPDATESeekSETSeekValue=@seek+1--更新種子表，使最大值+1
　　INSERTINTOt1VALUES(@seek,@remark)--插入一條訂單資料
　　COMMIT--提交事務

　　3.新建一個查詢視窗，使用以下語句呼叫建立的儲存過程，不斷的插入新訂單：

　　WHILE1=1

　　EXECAddOrder'test1'--不斷的插入訂單

　　4.再新建一個查詢視窗，使用通過的方式，不斷的插入新訂單，這樣用於模擬高併發時候的情況：

　　WHILE1=1
　　EXECAddOrder'test2'

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

　　訊息 2627，級別 14，狀態 1，過程 AddOrder，第 11 行

　　違反了 PRIMARY KEY 約束 'PK__Orders__C3905BAF08EA5793'。不能在物件 'dbo.Orders' 中插入重複鍵。

　　語句已終止。

　　為什麼會這樣呢?這得從事務隔離級別和鎖來解釋：

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

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

　　ALTERPROCAddOrder--Author：深藍
　　@remarkVARCHAR(5)
　　AS
　　DECLARE@seekint
　　BEGINTRAN
　　UPDATESeekSETSeekValue=SeekValue+1　--先修改資料
　　SELECT@seek=SeekValue-1--已經加了1，所以這裡-1下來
　　FROMSeek
　　INSERTINTOOrdersVALUES(@seek,@remark)
　　COMMIT

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

　　這裡附上鎖相容性表：

　　現有授予模式

　　請求模式ISSUIXSIXX

　　意向共享 (IS)是是是是是否

　　共享 (S)是是是否否否

　　更新 (U)是是否否否否

　　意向排他 (IX)是否否是否否

　　意向排他共享 (SIX)是否否否否否

　　排他 (X)否否否否否否

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-625209/，如需轉載，請註明出處，否則將追究法律責任。