SQL Server并發(fā)處理存在就更新解決方案探討
前言
本節(jié)我們來(lái)講講并發(fā)中最常見(jiàn)的情況存在即更新，在并發(fā)中若未存在行記錄則插入，此時(shí)未處理好極容易出現(xiàn)插入重復(fù)鍵情況，本文我們來(lái)介紹對(duì)并發(fā)中存在就更新行記錄的七種方案并且我們來(lái)綜合分析最合適的解決方案。
探討存在就更新七種方案
首先我們來(lái)創(chuàng)建測(cè)試表
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
解決方案一（開(kāi)啟事務(wù)）
我們統(tǒng)一創(chuàng)建存儲(chǔ)過(guò)程通過(guò)來(lái)SQLQueryStress來(lái)測(cè)試并發(fā)情況，我們來(lái)看第一種情況。
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
同時(shí)開(kāi)啟100個(gè)線程和200個(gè)線程出現(xiàn)插入重復(fù)鍵的幾率比較少還是存在。
解決方案二（降低隔離級(jí)別為最低隔離級(jí)別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
此時(shí)問(wèn)題依舊和解決方案一無(wú)異（如果降低級(jí)別為最低隔離級(jí)別，如果行記錄為空，前一事務(wù)如果未進(jìn)行提交，當(dāng)前事務(wù)也能讀取到該行記錄為空，如果當(dāng)前事務(wù)插入進(jìn)去并進(jìn)行提交，此時(shí)前一事務(wù)再進(jìn)行提交此時(shí)就會(huì)出現(xiàn)插入重復(fù)鍵問(wèn)題）
解決方案三（提升隔離級(jí)別為最高級(jí)別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
在這種情況下更加糟糕，直接到會(huì)導(dǎo)致死鎖
此時(shí)將隔離級(jí)別提升為最高隔離級(jí)別會(huì)解決插入重復(fù)鍵問(wèn)題，但是對(duì)于更新來(lái)獲取排它鎖而未提交，而此時(shí)另外一個(gè)進(jìn)程進(jìn)行查詢獲取共享鎖此時(shí)將造成進(jìn)程間相互阻塞從而造成死鎖，所以從此知最高隔離級(jí)別有時(shí)候能夠解決并發(fā)問(wèn)題但是也會(huì)帶來(lái)死鎖問(wèn)題。
解決方案四（提升隔離級(jí)別+良好的鎖）
此時(shí)我們?cè)賮?lái)在添加最高隔離級(jí)別的基礎(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)什么異常，通過(guò)查詢數(shù)據(jù)時(shí)使用更新鎖而非共享鎖，這樣的話一來(lái)可以讀取數(shù)據(jù)但不阻塞其他事務(wù)，二來(lái)還確保自上次讀取數(shù)據(jù)后數(shù)據(jù)未被更改，這樣就解決了死鎖問(wèn)題。貌似這樣的方案是可行得，如果是高并發(fā)不知是否可行。
解決方案五（提升隔離級(jí)別為行版本控制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
上述解決方案也會(huì)出現(xiàn)插入重復(fù)鍵問(wèn)題不可取。
解決方案六（提升隔離級(jí)別+表變量）
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)過(guò)多次認(rèn)證也是零錯(cuò)誤，貌似通過(guò)表變量形式實(shí)現(xiàn)可行。
解決方案七（提升隔離級(jí)別+Merge）
通過(guò)Merge關(guān)鍵來(lái)實(shí)現(xiàn)存在即更新否則則插入，同時(shí)我們應(yīng)該注意設(shè)置隔離級(jí)別為SERIALIZABLE否則會(huì)出現(xiàn)插入重復(fù)鍵問(wèn)題，代碼如下：
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)證無(wú)論是并發(fā)100個(gè)線程還是并發(fā)200個(gè)線程依然沒(méi)有異常信息。
總結(jié)
本節(jié)我們?cè)敿?xì)討論了在并發(fā)中如何處理存在即更新，否則即插入問(wèn)題的解決方案，目前來(lái)講以上三種方案可行。
解決方案一（最高隔離級(jí)別 + 更新鎖）
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
暫時(shí)只能想到這三種解決方案，個(gè)人比較推薦方案一和方案三， 請(qǐng)問(wèn)您有何高見(jiàn)，請(qǐng)留下您的評(píng)論若可行，我將進(jìn)行后續(xù)補(bǔ)充。
解決方案二（最高隔離級(jí)別 + 表變量）
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í)別 + 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
暫時(shí)只能想到這三種解決方案，個(gè)人比較推薦方案一和方案三， 請(qǐng)問(wèn)您有何高見(jiàn)，請(qǐng)留下您的評(píng)論若可行，我將進(jìn)行后續(xù)補(bǔ)充。
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
本文詳細(xì)講解了SQL?Server中的約束(constraints)，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-05-05
在安裝好SQL?Server后,默認(rèn)sa用戶是禁用且也沒(méi)有啟用SQL?Server登陸驗(yàn)證,這需要手動(dòng)開(kāi)啟,下面這篇文章主要給大家介紹了關(guān)于SQL?Server中啟用SA賬戶的最新詳細(xì)教程,需要的朋友可以參考下2024-04-04
本文主要介紹了SQL 嵌套查詢的具體使用，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2023-01-01
自動(dòng)清理 MSSQL Server Table Collation問(wèn)題的解決方法
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation2013-02-02
本篇文章是對(duì)使用SQL Server判斷文件是否存在后再刪除進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
mssql 30萬(wàn)條數(shù)據(jù) 搜索文本字段的各種方式對(duì)比
30萬(wàn)條，有ID列但無(wú)主鍵，在要搜索的“分類”字段上建有非聚集索引2010-04-04
這篇文章主要介紹了SQLServer日期函數(shù)總結(jié)案例詳解,本篇文章通過(guò)簡(jiǎn)要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-09-09
SQL?Server2000在win10上安裝的方法圖文教程
Win10本身是一個(gè)兼容性較好的操作系統(tǒng),有很多人在咨詢?nèi)绾卧赪indows 10上安裝SQL Server 2000數(shù)據(jù)庫(kù),都沒(méi)有成功過(guò),這篇文章主要給大家介紹了關(guān)于SQL?Server2000在win10上安裝的方法,需要的朋友可以參考下2024-05-05
SQL?Server使用T-SQL進(jìn)階之公用表表達(dá)式(CTE)
這篇文章介紹了SQL?Server中T-SQL的公用表表達(dá)式(CTE)，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-05-05
sql?server導(dǎo)入、導(dǎo)出數(shù)據(jù)庫(kù)詳細(xì)步驟記錄
這篇文章主要給大家介紹了關(guān)于sql?server導(dǎo)入、導(dǎo)出數(shù)據(jù)庫(kù)的相關(guān)資料,SQL?Server?導(dǎo)入和導(dǎo)出向?qū)且环N使你可以將數(shù)據(jù)從源復(fù)制到目標(biāo)的工具,文中通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-03-03