這篇文章主要和大家一起探討了sql server并發處理存在就更新的7種解決方案，具有一定的參考價值，感興趣的小伙伴們可以參考一下
前言
本節我們來講講并發中最常見的情況存在即更新，在并發中若未存在行記錄則插入，此時未處理好極容易出現插入重復鍵情況，本文我們來介紹對并發中存在就更新行記錄的七種方案并且我們來綜合分析最合適的解決方案。
探討存在就更新七種方案
首先我們來創建測試表
IF?OBJECT_ID('Test')?IS?NOT?NULL ?DROP?TABLE?Test CREATE?TABLE?Test ( ?Id?int, ?Name?nchar(100), ?[Counter]?int,primary?key?(Id), ?unique?(Name) ); GO
解決方案一（開啟事務）
我們統一創建并發處理通過來SQLQueryStress來測試并發情況，我們來看第一種情況。
IF?OBJECT_ID('TestPro')?IS?NOT?NULL ?DROP?PROCEDURE?TestPro; GO ? CREATE?PROCEDURE?TestPro?(?@Id?INT?) AS ?DECLARE?@Name?NCHAR(100)?=?CAST(@Id?AS?NCHAR(100)) ? ?BEGIN?TRANSACTION ?IF?EXISTS?(?SELECT?1 ????FROM?Test ????WHERE?Id?=?@Id?) ??UPDATE?Test ??SET??[Counter]?=?[Counter]?+?1 ??WHERE?Id?=?@Id; ?ELSE ??INSERT?Test ????(?Id,?Name,?[Counter]?) ??VALUES?(?@Id,?@Name,?1?); ?COMMIT GO
同時開啟100個線程和200個線程出現插入重復鍵的幾率比較少還是存在。
解決方案二（降低隔離級別為最低隔離級別UNCOMMITED）
IF?OBJECT_ID('TestPro')?IS?NOT?NULL ?DROP?PROCEDURE?TestPro; GO ? CREATE?PROCEDURE?TestPro?(?@Id?INT?) AS ?DECLARE?@Name?NCHAR(100)?=?CAST(@Id?AS?NCHAR(100)) ? ?SET?TRANSACTION?ISOLATION?LEVEL?READ?UNCOMMITTED ?BEGIN?TRANSACTION ?IF?EXISTS?(?SELECT?1 ????FROM?Test ????WHERE?Id?=?@Id?) ??UPDATE?Test ??SET??[Counter]?=?[Counter]?+?1 ??WHERE?Id?=?@Id; ?ELSE ??INSERT?Test ????(?Id,?Name,?[Counter]?) ??VALUES?(?@Id,?@name,?1?); ?COMMIT GO
此時問題依舊和解決方案一無異（如果降低級別為最低隔離級別，如果行記錄為空，前一事務如果未進行提交，當前事務也能讀取到該行記錄為空，如果當前事務插入進去并進行提交，此時前一事務再進行提交此時就會出現插入重復鍵問題）
解決方案三（提升隔離級別為最高級別SERIALIZABLE）
IF?OBJECT_ID('TestPro')?IS?NOT?NULL ?DROP?PROCEDURE?TestPro; GO ? CREATE?PROCEDURE?TestPro?(?@Id?INT?) AS ?DECLARE?@Name?NCHAR(100)?=?CAST(@Id?AS?NCHAR(100)) ? ?SET?TRANSACTION?ISOLATION?LEVEL?SERIALIZABLE ?BEGIN?TRANSACTION ?IF?EXISTS?(?SELECT?1 ????FROM?dbo.Test ????WHERE?Id?=?@Id?) ??UPDATE?dbo.Test ??SET??[Counter]?=?[Counter]?+?1 ??WHERE?Id?=?@Id; ?ELSE ??INSERT?dbo.Test ????(?Id,?Name,?[Counter]?) ??VALUES?(?@Id,?@Name,?1?); ?COMMIT GO
在這種情況下更加糟糕，直接到會導致死鎖
此時將隔離級別提升為最高隔離級別會解決插入重復鍵問題，但是對于更新來獲取排它鎖而未提交，而此時另外一個進程進行并發處理獲取共享鎖此時將造成進程間相互阻塞從而造成死鎖，所以從此知最高隔離級別有時候能夠解決并發問題但是也會帶來死鎖問題。
解決方案四（提升隔離級別+良好的鎖）
此時我們再來在添加最高隔離級別的基礎上增添更新鎖，如下：
IF?OBJECT_ID('TestPro')?IS?NOT?NULL ?DROP?PROCEDURE?TestPro; GO ? CREATE?PROCEDURE?TestPro?(?@Id?INT?) AS ?DECLARE?@Name?NCHAR(100)?=?CAST(@Id?AS?NCHAR(100)) ? ?SET?TRANSACTION?ISOLATION?LEVEL?SERIALIZABLE ?BEGIN?TRANSACTION ?IF?EXISTS?(?SELECT?1 ????FROM?dbo.Test?WITH(UPDLOCK) ????WHERE?Id?=?@Id?) ??UPDATE?dbo.Test ??SET??[Counter]?=?[Counter]?+?1 ??WHERE?Id?=?@Id; ?ELSE ??INSERT?dbo.Test ????(?Id,?Name,?[Counter]?) ??VALUES?(?@Id,?@Name,?1?); ?COMMIT GO
運行多次均未發現出現什么異常，通過查詢數據時使用更新鎖而非共享鎖，這樣的話一來可以讀取數據但不阻塞其他事務，二來還確保自上次讀取數據后數據未被更改，這樣就解決了死鎖問題。貌似這樣的方案是可行得，如果是高并發不知是否可行。
解決方案五（提升隔離級別為行版本控制SNAPSHOT）
ALTER?database?UpsertTestDatabase SET?ALLOW_SNAPSHOT_ISOLATION?ON ? ALTER?DATABASE?UpsertTestDatabase SET?READ_COMMITTED_SNAPSHOT?ON GO? IF?OBJECT_ID('TestPro')?IS?NOT?NULL ?DROP?PROCEDURE?TestPro; GO ? CREATE?PROCEDURE?TestPro?(?@Id?INT?) AS ?DECLARE?@Name?NCHAR(100)?=?CAST(@Id?AS?NCHAR(100)) ? ?BEGIN?TRANSACTION ?IF?EXISTS?(?SELECT?1 ????FROM?dbo.Test ????WHERE?Id?=?@Id?) ??UPDATE?dbo.Test ??SET??[Counter]?=?[Counter]?+?1 ??WHERE?Id?=?@Id; ?ELSE ??INSERT?dbo.Test ????(?Id,?Name,?[Counter]?) ??VALUES?(?@Id,?@Name,?1?); ?COMMIT GO
上述解決方案也會出現插入重復鍵問題不可取。
解決方案六（提升隔離級別+表變量）
IF?OBJECT_ID('TestPro')?IS?NOT?NULL ?DROP?PROCEDURE?TestPro; GO ? CREATE?PROCEDURE?TestPro?(?@Id?INT?) AS ?DECLARE?@Name?NCHAR(100)?=?CAST(@Id?AS?NCHAR(100)) ?DECLARE?@updated?TABLE?(?i?INT?); ? ?SET?TRANSACTION?ISOLATION?LEVEL?SERIALIZABLE; ?BEGIN?TRANSACTION ?UPDATE?Test ?SET??[Counter]?=?[Counter]?+?1 ?OUTPUT?DELETED.Id ???INTO?@updated ?WHERE?Id?=?@Id; ? ?IF?NOT?EXISTS?(?SELECT?i ?????FROM?@updated?) ??INSERT?INTO?Test ????(?Id,?Name,?counter?) ??VALUES?(?@Id,?@Name,?1?); ?COMMIT GO
經過多次認證也是零錯誤，貌似通過表變量形式實現可行。
解決方案七（提升隔離級別+Merge）
通過Merge關鍵來實現存在即更新否則則插入，同時我們應該注意設置隔離級別為SERIALIZABLE否則會出現插入重復鍵問題，代碼如下：
IF?OBJECT_ID('TestPro')?IS?NOT?NULL ?DROP?PROCEDURE?TestPro; GO ? CREATE?PROCEDURE?TestPro?(?@Id?INT?) AS ?DECLARE?@Name?NCHAR(100)?=?CAST(@Id?AS?NCHAR(100)) ?SET?TRAN?ISOLATION?LEVEL?SERIALIZABLE? ?BEGIN?TRANSACTION ?MERGE?Test?AS?[target] ?USING ??(?SELECT?@Id?AS?Id ??)?AS?source ?ON?source.Id?=?[target].Id ?WHEN?MATCHED?THEN ??UPDATE?SET ????[Counter]?=?[target].[Counter]?+?1 ?WHEN?NOT?MATCHED?THEN ??INSERT?(?Id,?Name,?[Counter]?) ??VALUES?(?@Id,?@Name,?1?); ?COMMIT GO
多次認證無論是并發100個線程還是并發200個線程依然沒有異常信息。
總結
本節我們詳細討論了在并發中如何處理存在即更新，否則即插入問題的解決方案，目前來講以上三種方案可行。
解決方案一（最高隔離級別 + 更新鎖）
IF?OBJECT_ID('TestPro')?IS?NOT?NULL ?DROP?PROCEDURE?TestPro; GO ? CREATE?PROCEDURE?TestPro?(?@Id?INT?) AS ?DECLARE?@Name?NCHAR(100)?=?CAST(@Id?AS?NCHAR(100)) ? ?BEGIN?TRANSACTION; ? ?UPDATE?dbo.Test?WITH?(?UPDLOCK,?HOLDLOCK?) ?SET??[Counter]?=?[Counter]?+?1 ?WHERE?Id?=?@Id; ? ?IF?(?@@ROWCOUNT?=?0?) ??BEGIN ???INSERT?dbo.Test ?????(?Id,?Name,?[Counter]?) ???VALUES?(?@Id,?@Name,?1?); ??END ? ?COMMIT GO
暫時只能想到這三種解決方案，個人比較推薦方案一和方案三， 請問您有何高見，請留下您的評論若可行，我將進行后續補充。
解決方案二（最高隔離級別 + 表變量）
IF?OBJECT_ID('TestPro')?IS?NOT?NULL ?DROP?PROCEDURE?TestPro; GO ? CREATE?PROCEDURE?TestPro?(?@Id?INT?) AS ?DECLARE?@Name?NCHAR(100)?=?CAST(@Id?AS?NCHAR(100)) ?DECLARE?@updated?TABLE?(?i?INT?); ? ?SET?TRANSACTION?ISOLATION?LEVEL?SERIALIZABLE; ?BEGIN?TRANSACTION ?UPDATE?Test ?SET??[Counter]?=?[Counter]?+?1 ?OUTPUT?DELETED.id ???INTO?@updated ?WHERE?id?=?@id; ? ?IF?NOT?EXISTS?(?SELECT?i ?????FROM?@updated?) ??INSERT?INTO?Test ????(?Id,?Name,?counter?) ??VALUES?(?@Id,?@Name,?1?); ?COMMIT GO
解決方案三（最高隔離級別 + Merge）
IF?OBJECT_ID('TestPro')?IS?NOT?NULL ?DROP?PROCEDURE?TestPro; GO ? CREATE?PROCEDURE?TestPro?(?@Id?INT?) AS ?DECLARE?@Name?NCHAR(100)?=?CAST(@Id?AS?NCHAR(100)) ?SET?TRAN?ISOLATION?LEVEL?SERIALIZABLE? ?BEGIN?TRANSACTION ?MERGE?Test?AS?[target] ?USING ??(?SELECT?@Id?AS?Id ??)?AS?source ?ON?source.Id?=?[target].Id ?WHEN?MATCHED?THEN ??UPDATE?SET ????[Counter]?=?[target].[Counter]?+?1 ?WHEN?NOT?MATCHED?THEN ??INSERT?(?Id,?Name,?[Counter]?) ??VALUES?(?@Id,?@Name,?1?); ?COMMIT GO
暫時只能想到這三種解決方案，個人比較推薦方案一和方案三， 請問您有何高見，請留下您的評論若可行，我將進行后續補充。
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
