SQL Server并發(fā)處理存在就更新解決方案探討
前言
本節(jié)我們來講講并發(fā)中最常見的情況存在即更新，在并發(fā)中若未存在行記錄則插入，此時未處理好極容易出現(xiàn)插入重復(fù)鍵情況，本文我們來介紹對并發(fā)中存在就更新行記錄的七種方案并且我們來綜合分析最合適的解決方案。
探討存在就更新七種方案
首先我們來創(chuàng)建測試表
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
解決方案一（開啟事務(wù)）
我們統(tǒng)一創(chuàng)建存儲過程通過來SQLQueryStress來測試并發(fā)情況，我們來看第一種情況。
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個線程出現(xiàn)插入重復(fù)鍵的幾率比較少還是存在。
解決方案二（降低隔離級別為最低隔離級別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
此時問題依舊和解決方案一無異（如果降低級別為最低隔離級別，如果行記錄為空，前一事務(wù)如果未進(jìn)行提交，當(dāng)前事務(wù)也能讀取到該行記錄為空，如果當(dāng)前事務(wù)插入進(jìn)去并進(jìn)行提交，此時前一事務(wù)再進(jìn)行提交此時就會出現(xiàn)插入重復(fù)鍵問題）
解決方案三（提升隔離級別為最高級別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
在這種情況下更加糟糕，直接到會導(dǎo)致死鎖
此時將隔離級別提升為最高隔離級別會解決插入重復(fù)鍵問題，但是對于更新來獲取排它鎖而未提交，而此時另外一個進(jìn)程進(jìn)行查詢獲取共享鎖此時將造成進(jìn)程間相互阻塞從而造成死鎖，所以從此知最高隔離級別有時候能夠解決并發(fā)問題但是也會帶來死鎖問題。
解決方案四（提升隔離級別+良好的鎖）
此時我們再來在添加最高隔離級別的基礎(chǔ)上增添更新鎖，如下：
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
運(yùn)行多次均未發(fā)現(xiàn)出現(xiàn)什么異常，通過查詢數(shù)據(jù)時使用更新鎖而非共享鎖，這樣的話一來可以讀取數(shù)據(jù)但不阻塞其他事務(wù)，二來還確保自上次讀取數(shù)據(jù)后數(shù)據(jù)未被更改，這樣就解決了死鎖問題。貌似這樣的方案是可行得，如果是高并發(fā)不知是否可行。
解決方案五（提升隔離級別為行版本控制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
上述解決方案也會出現(xiàn)插入重復(fù)鍵問題不可取。
解決方案六（提升隔離級別+表變量）
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
經(jīng)過多次認(rèn)證也是零錯誤，貌似通過表變量形式實(shí)現(xiàn)可行。
解決方案七（提升隔離級別+Merge）
通過Merge關(guān)鍵來實(shí)現(xiàn)存在即更新否則則插入，同時我們應(yīng)該注意設(shè)置隔離級別為SERIALIZABLE否則會出現(xiàn)插入重復(fù)鍵問題，代碼如下：
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
多次認(rèn)證無論是并發(fā)100個線程還是并發(fā)200個線程依然沒有異常信息。
總結(jié)
本節(jié)我們詳細(xì)討論了在并發(fā)中如何處理存在即更新，否則即插入問題的解決方案，目前來講以上三種方案可行。
解決方案一（最高隔離級別 + 更新鎖）
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
暫時只能想到這三種解決方案，個人比較推薦方案一和方案三， 請問您有何高見，請留下您的評論若可行，我將進(jìn)行后續(xù)補(bǔ)充。
解決方案二（最高隔離級別 + 表變量）
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
暫時只能想到這三種解決方案，個人比較推薦方案一和方案三， 請問您有何高見，請留下您的評論若可行，我將進(jìn)行后續(xù)補(bǔ)充。
以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持我們。
上一篇：SQL Server 常用函數(shù)使用方法小結(jié)
欄 目：MsSql
下一篇：sql server 2008 數(shù)據(jù)庫管理系統(tǒng)使用SQL語句創(chuàng)建登錄用戶步驟詳解
本文標(biāo)題：SQL Server并發(fā)處理存在就更新解決方案探討
本文地址：http://mengdiqiu.com.cn/a1/MsSql/10480.html
您可能感興趣的文章
- 01-10SQLServer存儲過程實(shí)現(xiàn)單條件分頁
- 01-10SQL Server 2012降級至2008R2的方法
- 01-10SQLServer中防止并發(fā)插入重復(fù)數(shù)據(jù)的方法詳解
- 01-10SQL Server數(shù)據(jù)庫定時自動備份
- 01-10SQL Server性能調(diào)優(yōu)之緩存
- 01-10實(shí)現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)據(jù)的實(shí)例代碼
- 01-10Sql Server 死鎖的監(jiān)控分析解決思路
- 01-10SqlServer 在事務(wù)中獲得自增ID的實(shí)例代碼
- 01-10SqlServer快速檢索某個字段在哪些存儲過程中(sql 語句)
- 01-10SQLServer性能優(yōu)化--間接實(shí)現(xiàn)函數(shù)索引或者Hash索引
閱讀排行
本欄相關(guān)
- 01-10SQLServer存儲過程實(shí)現(xiàn)單條件分頁
- 01-10SQLServer中防止并發(fā)插入重復(fù)數(shù)據(jù)的方
- 01-10SQL Server 2012降級至2008R2的方法
- 01-10SQL Server性能調(diào)優(yōu)之緩存
- 01-10SQL Server數(shù)據(jù)庫定時自動備份
- 01-10Sql Server 死鎖的監(jiān)控分析解決思路
- 01-10實(shí)現(xiàn)SQL Server 原生數(shù)據(jù)從XML生成JSON數(shù)
- 01-10SqlServer快速檢索某個字段在哪些存儲
- 01-10SqlServer 在事務(wù)中獲得自增ID的實(shí)例代
- 01-10SQLServer性能優(yōu)化--間接實(shí)現(xiàn)函數(shù)索引或
隨機(jī)閱讀
- 01-10SublimeText編譯C開發(fā)環(huán)境設(shè)置
- 08-05DEDE織夢data目錄下的sessions文件夾有什
- 01-10C#中split用法實(shí)例總結(jié)
- 01-10使用C語言求解撲克牌的順子及n個骰子
- 01-10delphi制作wav文件的方法
- 08-05dedecms(織夢)副欄目數(shù)量限制代碼修改
- 04-02jquery與jsp,用jquery
- 01-11ajax實(shí)現(xiàn)頁面的局部加載
- 08-05織夢dedecms什么時候用欄目交叉功能？
- 01-11Mac OSX 打開原生自帶讀寫NTFS功能(圖文