幾種方式避免取號功能取到重複的值

前陣子在開發系統時,意外發現系統會取到相同的號碼,導致DB的資料亂掉,閱讀一些資料後,才了解到任何有關取號或流水號的功能,都要做一些處理來避免發生重複取號的狀況,以下介紹幾種處理方式。

 

Lock陳述式

lock 陳述式會取得指定物件的互斥鎖定、執行陳述式區塊,然後釋放鎖定。意思就是,如有程式運行到Lock區段內,後有其他的程式想要存取此區段程式時,將被要求等待Lock區段內的程式運行完成後才可進入。

 

用法:

先宣告提供Lock鎖定的物件

 

將欲執行的取號程式用Lock包進去,裡面的程式碼就會標記為關鍵區段 (Critical Section),其他程式必須等待取號動作(關鍵區段)執行完畢後才能再次取號,如此就能解決重複取號的問題。

缺點:

若同時有多個站台時,記憶體的lock對取號的檢查就無法準確,此時就要使用其他方法。

 

交易控管(Transaction) + TABLOCKX語法

SELECT中加入TABLOCKX語法會讓這張TABLE進入Exclusive Lock的狀態,且在這個Transaction結束前都會維持此狀態。此時其他交易的動作皆無法針對此張資料表產生Lock,也因為使用Transaction的關係,讓交易在結束前,其他人皆無法取得此張資料表內容,如此一來取號(SELECT)時都會是取最新的值。

缺點:

因為TABLOCKX語法會讓其他查詢等待,如果使用過多或是不當,會造成

系統過多的等待而讓效能低落,因此仍需依照需求作使用。

 

 

SQL SERVER Sequence(序列) 或 IDENTITY Column(識別欄位)

此兩種方法都是在資料庫進行設定,讓資料庫自己控管流水號,每當有資料新增時,流水號欄位會自動加一,以此避免重複單號。

  1. IDENTITY 只要在創建Table時流水號欄位加上 INT IDENTITY(1,1), –從1開始,每次增加1該欄位在每次有資料新增時都會自動加一。
  2. SEQUENCE與 Identity不同的是,Sequence不會綁定Table,可以在不同的Table中共用,以下用Customers與 Users兩張表示範; 新建一個Sequence Object後使用NEXT VALUE for新增,就可以看到兩張表的序列號碼是一起計算的。

 

缺點:

這兩種方法在配合Transaction時可能會發生漏號或跳號的問題,假設A取完號後交易出錯,B再進行取號時會直接跳過A取的號碼自行加一,須看實務上會不會影響來作使用。

 

交易控管(Transaction) + UPDATE

此方法雖然簡單卻非常實用,在執行SELECT取號之前先使用UPDATE語法將流水號欄位加一,如此一來不論有多少交易要同時進行,進入交易時都是取目前流水號的值再加一來進行取號,就不會發生覆蓋的情況。

 

 

交易控管(Transaction) + WHERE條件驗證

此方式可用來做最後的驗證來確保流水號一致,在最後要UPDATE流水號加一時,加入WHERE條件:

UPDATE 流水號 = K+1 WHERE 流水號 = K

再利用回傳的成功與否(有值或無值)來判斷最後的Commit要執行還是RETRY。

 

 

Reference:

https://docs.microsoft.com/zh-tw/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15

https://dotnettutorials.net/lesson/difference-between-sequence-and-identity-in-sql-server/

https://yohey66.wordpress.com/2017/07/11/sql-server%E9%80%8F%E9%81%8Etablockx%E8%AA%9E%E6%B3%95%E4%BB%A5%E9%81%BF%E5%85%8D%E5%8F%96%E8%99%9F%E5%8A%9F%E8%83%BD%E5%8F%96%E5%88%B0%E9%87%8D%E8%A4%87%E7%9A%84%E5%80%BC/comment-page-1/


發佈留言

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料