講到資料庫的唯一值，第一個我們會想到就是主索引鍵（Primary Key），那同一個資料表內除了主索引鍵之外會不會有其他的唯一值？我之前遇過的一個需求就有，它是一個部落格的功能，讀者可以收藏文章，同一篇文章不能重複收藏，而讀者可以對已收藏的文章進行任意的排序，所以「讀者 ID + 文章 ID」就成了資料表的主索引鍵，而「讀者 ID + 序號」就是資料表中的另一組唯一值。
偏偏在部落格的文章列表頁面上，有設計一顆快速收藏的按鈕，讀者可以不必看過文章的內容就能收藏，當讀者的點擊快那麼一點，網路的延遲慢那麼一點的時候，收藏文章就會出現併發的狀況，多篇文章有可能同時收藏，這時候我們就要避免文章的順序有重複的情況。
在實驗環境中，我簡單地設計一個 Favorite 資料表，用來儲存收藏文章的資料，資料表的結構如下圖：
然後，我為了模擬併發的情況，增加了一個 Network 資料表，在執行收藏文章的程序之前，先獨佔鎖定 Network 資料表，假裝網路卡個 10 秒。
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- 用鎖定 Network 資料表來模擬網路卡卡
SELECT
*
FROM Network n WITH (TABLOCK, XLOCK)
WAITFOR DELAY '00:00:10'
COMMIT TRANSACTION
而在收藏文章的資料寫進資料庫之前，先查詢 Network 資料表的其中一筆特定的資料，等待 Network 資料表的資源釋放，表示網路通了。
-- 如果能成功撈取 Network 其中一筆特定的資料就表示網路通了
SELECT
*
FROM Network n
WHERE n.Id = 1
交易＋鎖定
產生序號的方法，就用目前序號最大值加 1 的方式，初始值為 1，為了在併發執行時避免產生重複的序號以及死結（Deadlock），我們會將整個程序包在一個交易裡面，並且加上嚴格的鎖定類型及隔離層級策略，大略的 INSERT 語句如下：
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @MemberNo INT = 1
DECLARE @PostId INT = 1
DECLARE @SeqNo INT
SELECT
@SeqNo = ISNULL(MAX(f.SeqNo), 0) + 1
FROM Favorite f WITH (TABLOCK, XLOCK)
WHERE f.MemberId = @MemberNo
INSERT INTO Favorite(MemberId
,PostId
,SeqNo)
VALUES (@MemberNo
,@PostId
,@SeqNo);
COMMIT TRANSACTION
下面我同時獨立執行 4 個上面的寫入程序，交易＋鎖定的策略運作正常。
唯一條件約束
但是嚴格的鎖定類型及隔離層級是一種悲觀的策略，相較於此，我個人更傾向使用樂觀的策略，不另外包成交易、不額外指定鎖定類型及隔離層級，一樣可以做到預期的增刪改結果。
就文章收藏序號的這個案例，我選擇在資料表上加上個唯一條件約束（UNIQUE Constraints），來約束「讀者 ID + 序號」是唯一值，唯一條件約束產生的語法如下：
ALTER TABLE Favorite
ADD CONSTRAINT AK_Favorite_MemberId_SeqNo UNIQUE (MemberId, SeqNo)
執行完這段語法之後，我們會在資料表的索引鍵選單下面，看到新增了一個索引鍵，這個索引鍵叫做「唯一索引鍵」。
加上這個唯一索引鍵之後，寫入收藏文章資料的邏輯就改成當收到索引鍵重複的例外錯誤時，重新再執行一次同一份 SQL 語句即可，大概像下面這樣：
DECLARE @MemberId INT = 1
DECLARE @PostId INT = 1
DECLARE @SeqNo INT
WHILE 1 = 1 BEGIN
BEGIN TRY
SELECT
@SeqNo = ISNULL(MAX(f.SeqNo), 0) + 1
FROM Favorite f WITH (NOLOCK)
WHERE f.MemberId = @MemberId
INSERT INTO Favorite(MemberId
,PostId
,SeqNo)
VALUES (@MemberId
,@PostId
,@SeqNo);
BREAK;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627 AND CHARINDEX('AK_Favorite_MemberId_SeqNo', ERROR_MESSAGE()) > 0
PRINT ERROR_MESSAGE();
ELSE
THROW;
END CATCH;
END
即使是使用 C# 下 SQL 語句，也是類似的邏輯。
var sql = @"
DECLARE @SeqNo INT
SELECT
@SeqNo = ISNULL(MAX(f.SeqNo), 0) + 1
FROM Favorite f WITH (NOLOCK)
WHERE f.MemberId = @MemberId
INSERT INTO Favorite(MemberId
,PostId
,SeqNo)
VALUES (@MemberId
,@PostId
,@SeqNo);";
using (var db = new SqlConnection(@"{ConnectionString}"))
{
while (true)
{
try
{
// 此處需引用 Dapper
db.Execute(sql, new { MemberId = 1, PostId = 1 });
break;
}
catch (Exception ex)
{
if (!(ex is SqlException sqlEx && sqlEx.Number == 2627 && sqlEx.Message.Contains("AK_Favorite_MemberId_SeqNo"))) throw;
}
}
}
我一樣獨立執行 4 個寫入資料的程序，可以看到結果是符合預期的，過程中也如預期會發生索引鍵重複的例外錯誤。
隨著軟體開發的經驗增多，所要處理的資料量也或多或少隨之增長，處理的資料量愈大，就愈覺得對於資料庫相關的知識，也應該要隨之擴展、累積，掌握的知識愈多，自己才不會永遠只用一根錘子在敲釘子，以上，SQL Server 一個小小的功能分享給大家，希望對大家有一點幫助。
