SqlServer 可更新訂閱中在訂閱庫並發擷取最大單據號測試及解決法案！

標籤：

說明：

很多交易的系統都需要單據號，而單據號按順序並發擷取又是一件頭疼的事！~

 

一般單據號格式為：首碼+日期+編號（如：KK20150501000001）

 

現在類比測試，資料庫中的單據號作為一張表處理。

資料庫單據號表（Billnumber）儲存每個帳號及其最大的單據號總共一條記錄

單據號是組合而成的字串，後6位為序號。

擷取單據號的預存程序：

EXEC [dbo].[GetBillnumber_Test]@Account=‘Account‘,@Billnumber NVARCHAR(20)OUTPUT

 

預存程序功能：傳遞使用者帳號，返回使用者的最大單據號。

預存程序內部邏輯：（查詢和更新都使用了叢集索引尋找）

a. 表中找出該帳號的單據號

b. 如果單據號存在，截取後位字元轉數字加1產生新的單據號返回，並更新這條記錄為最新單據號

c. 如果單據號不存在，則為這帳號第一次插入新的單據號，並返回該單據號

但是並行作業時，就可能發生堵塞以及死結。當前資料庫的事務層級為讀提交（read committed）。

通常的操作是，預存程序內部邏輯查詢表中的單據號時（如上a.），加上鎖並保持鎖到事務結束。我這裡為WITH(UPDLOCK,HOLDLOCK)，網上還有一種這樣加法WITH (XLOCK,PAGLOCK) ，這種加鎖方法會把整頁資料都不能訪問。

為了方便在SQLqueryStress測試，我把預存程序改為：

EXEC [dbo].[GetBillnumber_Test]@Account=‘Account‘

 

即不返回單據號，而在預存程序末尾把單據號拋出，讓SQLqueryStress能夠擷取查看單據號

DECLARE @RE NVARCHAR(100)

DECLARE @billNumber NVARCHAR(100)--已賦值的單據號（賦值過程不描述）

SET @RE=N‘[billNumber=‘+@billNumber+N‘]  [SPID=‘+CONVERT(VARCHAR(10),@@SPID)+N‘]‘

RAISERROR (@RE,16,1,@billNumber) --拋出

開啟2個SQLQuerySrees視窗，設定好串連參數：

第一個視窗：開啟1個串連，每個串連執行500次；帳號為A

第二個視窗：開啟1個串連，每個串連執行500次；帳號為A

資料庫中開啟死結跟蹤：

--  開啟死結跟蹤

dbcc traceon(1222,-1)

dbcc tracestatus

【執行測試】

快速執行第一個,第二個SQLQuerySrees視窗（點擊“GO”）

執行完成後，點擊SQLQuerySrees日誌查看：

測試結果：

發現單據號都是遞增的，只是中間有跳躍，因為另一個進程也在擷取單據號。查看SqlServer日誌，並沒有看到死結！

開啟幾個SQLqueryStress測試也是一樣。結果是正常和符合的！

 

 

但是！問題來了！~當資料庫中建立了可更新訂閱，並且在訂閱資料庫操作，就會產生死結！~

對該表建立可更新訂閱：

與上面一樣，的步驟，對訂閱資料庫的單據表進行單據號的擷取操作。

 

在執行SQLQuerySrees時，同時也快速返回到資料庫，查看鎖情況。參考指令碼：SqlServer 查看當前鎖請求情況的一個指令碼

可以看到，一個spid在對錶BillNumberRecord更新時，也觸發將資料輸入插入到同步隊列表中。對單據表使用的是範圍所RangeS_U和RangeX_X。這會兒查看SqlServer日誌，

3個參與死結的預存程序機器堵塞的語句：

sp_MSdel_dboBillNumberRecord：（死結犧牲品）

delete [dbo].[BillNumberRecord]

where [Guid]=@pkc1and[Account]=@pkc2andmsrepl_tran_version=@msrepl_tran_version

trg_MSsync_upd_BillNumberRecord：

update [dbo].[BillNumberRecord]setmsrepl_tran_version=@c7 

where  [Guid] = @c1 and [Account] = @c2

EXEC[dbo].[GetBillnumberBySID_Test]：

操作為：更新單據表的單據號列為最大單據號（此處語句就不顯示了）

資源清單如下：

--  deadlockvictim=processe988e0

resource-list

keylock objectname= BillNumberRecordindexname=叢集索引id=locke1193c0mode=RangeX-X

owner-list

owner id=processcdac70mode=RangeX-X

waiter-list

waiter id= processe988e0mode=UrequestType=wait

keylock objectname= BillNumberRecordindexname=主鍵(非叢集索引)id=lock58d986c0mode=U

owner-list

owner id= processe988e0mode=U

waiter-list

waiter id=processcdac70mode=UrequestType=wait

接下來在資料庫中執行查看執行計畫，到底處理了哪些語句：

EXEC [dbo].[GetBillnumber_Test]@Account=‘Account‘

 

結果如下，按預存程序內部邏輯：

a. 首先尋找這個帳號當前的單據號（叢集索引尋找）(UPDLOCK,HOLDLOCK)

b.單據號存在，產生一個新的最大單據號，更新回表中（叢集索引尋找和更新）

c.掃描偽表inserted（叢集索引），在觸發器trg_MSsync_upd_BillNumberRecord中

d. 掃描偽表deleted（叢集索引），在觸發器trg_MSsync_upd_BillNumberRecord中

e. 觸發器trg_MSsync_upd_BillNumberRecord執行更新msrepl_tran_version，（索引尋找，更新叢集索引）

f.最後將同步的事務和命令插入到隊列表中

上面能出現索引尋找的，只有trg_MSsync_upd_BillNumberRecord了。使用主鍵非叢集索引（guid，account）尋找，更新叢集索引（account）。

 

這算是設計上的問題，作為同步中的主鍵，最好也是叢集索引，表主鍵，在同步觸發中是不會更改的，而同步是經常觸發更改的，主鍵又不是叢集索引，將頻繁地更改。但是現在資料已經同步中，不能更改了，只能使用其他方法。而沒有同步的時候，並發是正常的，沒出現死結。就沒要更改原來擷取單據號的預存程序了，也沒有什麼可以改了。

 

既然是下面這個語句引起的，那就想辦法更改索引。

update [dbo].[BillNumberRecord]setmsrepl_tran_version=@c7 

where  [Guid] = @c1 and [Account] = @c2

最後解決死結方法是：

更改同步觸發器[dbo].[trg_MSsync_upd_BillNumberRecord]

 

將觸發器內的更改語句注釋。

update[dbo].[BillNumberRecord] set msrepl_tran_version = @c7 

where  [Guid] = @c1 and [Account]= @c2

 

設定新的更新方法，並強制加上叢集索引。這樣就能使用叢集索引尋找，並且是叢集索引更新！

update bsetmsrepl_tran_version=@c7 

from [dbo].[BillNumberRecord]asbwith(index=IX_BillNumberRecord)

where  [Guid] = @c1 and  [Account]=@c2   

 

測試的時候開啟了十幾個SQLQuerySrees，除了幾個是其他帳號，其餘都是相同帳號。

這裡要說明一下，為什麼開啟十幾個。

因為我們要類比十幾個帳號同時操作。SQLQuerySrees雖然有兩個選擇設定——迭代次數和線程數。

迭代次數：是重複執行的次數

線程數：是建立多少個新的資料庫session串連

 

迭代次數和線程數只是類比並發，為了類比在某一時刻同時操作才設定更多。雖然開啟不同線程，但是似乎還是順序建立。

這是某一刻查看的鎖資源情況，如

此刻只有spid=199 的會話正在擷取單據號，使用的是範圍鎖 RangX_X，並且對錶加上意向獨佔鎖定（只能查詢不能更改），而其他線程都是在查詢單據號並想擷取鎖RangS_U，因此都在等待。結果再日誌中是不會再出現死結了！！~同步也是正常的！~而如果沒有更改觸發器，出現了死結，並且要處理死結，執行時間反而更長。

 

至此，同步中並發擷取最大單據號的測試就到這吧。

SqlServer 可更新訂閱中在訂閱庫並發擷取最大單據號測試及解決法案！