雷火电竞-中国电竞赛事及体育赛事平台

歡迎來(lái)到入門(mén)教程網(wǎng)！

MsSql

當(dāng)前位置：主頁(yè) > 數(shù)據(jù)庫(kù) > MsSql >

SQL Server并發(fā)處理存在就更新解決方案探討

來(lái)源：本站原創(chuàng)|時(shí)間：2020-01-10|欄目：MsSql|點(diǎn)擊： 次

前言

本節(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í)有所幫助，也希望大家多多支持我們。

上一篇：SQL Server 常用函數(shù)使用方法小結(jié)

欄    目：MsSql

下一篇：sql server 2008 數(shù)據(jù)庫(kù)管理系統(tǒng)使用SQL語(yǔ)句創(chuàng)建登錄用戶步驟詳解

本文標(biāo)題：SQL Server并發(fā)處理存在就更新解決方案探討

本文地址：http://www.jygsgssxh.com/a1/MsSql/10480.html

網(wǎng)頁(yè)制作CMS教程網(wǎng)絡(luò)編程軟件編程腳本語(yǔ)言數(shù)據(jù)庫(kù)服務(wù)器

如果侵犯了您的權(quán)利，請(qǐng)與我們聯(lián)系，我們將在24小時(shí)內(nèi)進(jìn)行處理、任何非本站因素導(dǎo)致的法律后果，本站均不負(fù)任何責(zé)任。

聯(lián)系QQ：835971066 | 郵箱：835971066#qq.com(#換成@)

Copyright © 2002-2020 腳本教程網(wǎng) 版權(quán)所有