前言
本節我們來講講并發中最常見的情況存在即更新，在并發中若未存在行記錄則插入，此時未處理好極容易出現插入重復鍵情況，本文我們來介紹對并發中存在就更新行記錄的七種方案并且我們來綜合分析最合適的解決方案。
探討存在就更新七種方案
首先我們來創建測試表
|
1
2
3
4
5
6
7
8
9
10
11
|
IF OBJECT_ID('Test') IS NOT NULL DROP TABLE TestCREATE TABLE Test( Id int, Name nchar(100), [Counter] int,primary key (Id), unique (Name));GO |
解決方案一（開啟事務）
我們統一創建存儲過程通過來SQLQueryStress來測試并發情況，我們來看第一種情況。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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 ); COMMITGO |
同時開啟100個線程和200個線程出現插入重復鍵的幾率比較少還是存在。
解決方案二（降低隔離級別為最低隔離級別UNCOMMITED）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 ); COMMITGO |
此時問題依舊和解決方案一無異（如果降低級別為最低隔離級別，如果行記錄為空，前一事務如果未進行提交，當前事務也能讀取到該行記錄為空，如果當前事務插入進去并進行提交，此時前一事務再進行提交此時就會出現插入重復鍵問題）
解決方案三（提升隔離級別為最高級別SERIALIZABLE）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 ); COMMITGO |
在這種情況下更加糟糕，直接到會導致死鎖
此時將隔離級別提升為最高隔離級別會解決插入重復鍵問題，但是對于更新來獲取排它鎖而未提交，而此時另外一個進程進行查詢獲取共享鎖此時將造成進程間相互阻塞從而造成死鎖，所以從此知最高隔離級別有時候能夠解決并發問題但是也會帶來死鎖問題。
解決方案四（提升隔離級別+良好的鎖）
此時我們再來在添加最高隔離級別的基礎上增添更新鎖，如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 ); COMMITGO |
運行多次均未發現出現什么異常，通過查詢數據時使用更新鎖而非共享鎖，這樣的話一來可以讀取數據但不阻塞其他事務，二來還確保自上次讀取數據后數據未被更改，這樣就解決了死鎖問題。貌似這樣的方案是可行得，如果是高并發不知是否可行。
解決方案五（提升隔離級別為行版本控制SNAPSHOT）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
ALTER DATABASE UpsertTestDatabaseSET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE UpsertTestDatabaseSET READ_COMMITTED_SNAPSHOT ONGO 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 ); COMMITGO |
上述解決方案也會出現插入重復鍵問題不可取。
解決方案六（提升隔離級別+表變量）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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 ); COMMITGO |
經過多次認證也是零錯誤，貌似通過表變量形式實現可行。
解決方案七（提升隔離級別+Merge）
通過Merge關鍵來實現存在即更新否則則插入，同時我們應該注意設置隔離級別為SERIALIZABLE否則會出現插入重復鍵問題，代碼如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 ); COMMITGO |
多次認證無論是并發100個線程還是并發200個線程依然沒有異常信息。
總結
本節我們詳細討論了在并發中如何處理存在即更新，否則即插入問題的解決方案，目前來講以上三種方案可行。
解決方案一（最高隔離級別 + 更新鎖）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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 COMMITGO |
暫時只能想到這三種解決方案，個人比較推薦方案一和方案三， 請問您有何高見，請留下您的評論若可行，我將進行后續補充。
解決方案二（最高隔離級別 + 表變量）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
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 ); COMMITGO |
解決方案三（最高隔離級別 + Merge）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 ); COMMITGO |
暫時只能想到這三種解決方案，個人比較推薦方案一和方案三， 請問您有何高見，請留下您的評論若可行，我將進行后續補充。
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持服務器之家。
原文鏈接：http://www.cnblogs.com/CreateMyself/p/6906594.html
