简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
首頁 > 資料庫 > mysql教程 > 主體

SQL Server並發處理存在就更新解決方案探討_MsSql

微波
發布： 2017-06-28 15:42:23
原創
1296 人瀏覽過

這篇文章主要和大家一起探討了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
登入後複製
登入後複製

暂时只能想到这三种解决方案，个人比较推荐方案一和方案三， 请问您有何高见，请留下您的评论若可行，我将进行后续补充。

以上是SQL Server並發處理存在就更新解決方案探討_MsSql的詳細內容。更多資訊請關注PHP中文網其他相關文章！

相關標籤：
server 存在 更新
來源：php.cn
上一篇：SQL Server 磁碟請求逾時的833錯誤原因及解決方法_MsSql 下一篇：Shell下實作免密碼快速登陸MySQL資料庫的方法_Mysql
本網站聲明
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
作者最新文章
最新問題
XAMPP/SQLSRV：無法在 PHPINFO() 中找到 Sqlsrv; - 來自連線的錯誤 我正在嘗試連接到我在LinuxVM上託管的SQLServer資料庫。我在我的開發Windows機器上運行xampp，連接來自我正在建立的php網站。我想我需要使用sqlsrv來連線...
來自於 2023-11-09 22:34:33
0
1
172
如何在產生的靜態檔案中新增快取控制（Nuxt 3） 我正在使用Nuxt3建立SSR專案。我正在考慮將Cache-Control標頭新增至.output/_nuxt目錄中產生的靜態檔案。我嘗試了下面的程式碼server/middlew...
來自於 2023-11-05 08:55:14
0
2
287
Nuxt 3：useAsyncData 與 $fetch 只能工作一次 我在一個專案中使用nuxt3，我想向/server/api/目錄中的打字稿檔案發出請求。但是當我在檔案app.vue中這樣做時：<scriptsetuplang="...
來自於 2023-11-04 00:03:52
0
1
270
如何在 Nuxt 3 中使用 useQuery() 作為 API 路由參數？ 我正在遵循一個指南，其中api路由的建構方式如下：1建立server/api/route.js檔案：exportdefaultdefineEventHandler((event)=...
來自於 2023-11-01 19:50:53
0
2
339
在 ubuntu20.0 上安裝/啟用 php8.1 的 sqlsrv 和 pdo_sqlsrv 驅動程式 我正在嘗試在Ubuntu20.0上啟用php8.1的sqlsrv驅動程式。它沒有在phpinfo()中顯示擴展名。當嘗試使用命令php-m檢查sqlsrv模組時，輸出如下：我遵循了...
來自於 2023-11-01 11:06:13
0
2
283
相關專題
更多>
熱門推薦
熱門教學
更多>
相關教學
熱門推薦
最新課程
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板
關於我們 免責聲明 Sitemap
PHP中文網：公益線上PHP培訓，幫助PHP學習者快速成長！