當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

数据库存在即更新的并发处理 - 转

發布時間：2024/4/14 数据库 35 豆豆

生活随笔收集整理的這篇文章主要介紹了数据库存在即更新的并发处理 - 转小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

前言

本節我們來講講并發中最常見的情況存在即更新，在并發中若未存在行記錄則插入，此時未處理好極容易出現插入重復鍵情況，本文我們來介紹對并發中存在就更新行記錄的七種方案并且我們來綜合分析最合適的解決方案。

探討存在就更新七種方案

首先我們來創建測試表

IF OBJECT_ID('Test') IS NOT NULLDROP TABLE TestCREATE TABLE Test (Id int,Name nchar(100),[Counter] int,primary key (Id),unique (Name) ); GO

解決方案一（開啟事務）?

我們統一創建存儲過程通過來SQLQueryStress來測試并發情況，我們來看第一種情況。

IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))BEGIN TRANSACTIONIF EXISTS ( SELECT 1FROM TestWHERE Id = @Id )UPDATE TestSET [Counter] = [Counter] + 1WHERE Id = @Id;ELSEINSERT Test( Id, Name, [Counter] )VALUES ( @Id, @Name, 1 );COMMIT GO

同時開啟100個線程和200個線程出現插入重復鍵的幾率比較少還是存在。

解決方案二（降低隔離級別為最低隔離級別UNCOMMITED）

IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDBEGIN TRANSACTIONIF EXISTS ( SELECT 1FROM TestWHERE Id = @Id )UPDATE TestSET [Counter] = [Counter] + 1WHERE Id = @Id;ELSEINSERT Test( Id, Name, [Counter] )VALUES ( @Id, @name, 1 );COMMIT GO

此時問題依舊和解決方案一無異（如果降低級別為最低隔離級別，如果行記錄為空，前一事務如果未進行提交，當前事務也能讀取到該行記錄為空，如果當前事務插入進去并進行提交，此時前一事務再進行提交此時就會出現插入重復鍵問題）

解決方案三（提升隔離級別為最高級別SERIALIZABLE）

IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSACTIONIF EXISTS ( SELECT 1FROM dbo.TestWHERE Id = @Id )UPDATE dbo.TestSET [Counter] = [Counter] + 1WHERE Id = @Id;ELSEINSERT dbo.Test( Id, Name, [Counter] )VALUES ( @Id, @Name, 1 );COMMIT GO

在這種情況下更加糟糕，直接到會導致死鎖?

此時將隔離級別提升為最高隔離級別會解決插入重復鍵問題，但是對于更新來獲取排它鎖而未提交，而此時另外一個進程進行查詢獲取共享鎖此時將造成進程間相互阻塞從而造成死鎖，所以從此知最高隔離級別有時候能夠解決并發問題但是也會帶來死鎖問題。

解決方案四（提升隔離級別+良好的鎖）

此時我們再來在添加最高隔離級別的基礎上增添更新鎖，如下：

IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSACTIONIF EXISTS ( SELECT 1FROM dbo.Test WITH(UPDLOCK)WHERE Id = @Id )UPDATE dbo.TestSET [Counter] = [Counter] + 1WHERE Id = @Id;ELSEINSERT dbo.Test( Id, Name, [Counter] )VALUES ( @Id, @Name, 1 );COMMIT GO

運行多次均未發現出現什么異常，通過查詢數據時使用更新鎖而非共享鎖，這樣的話一來可以讀取數據但不阻塞其他事務，二來還確保自上次讀取數據后數據未被更改，這樣就解決了死鎖問題。貌似這樣的方案是可行得，如果是高并發不知是否可行。

解決方案五（提升隔離級別為行版本控制SNAPSHOT）?

ALTER DATABASE UpsertTestDatabase SET ALLOW_SNAPSHOT_ISOLATION ONALTER DATABASE UpsertTestDatabase SET READ_COMMITTED_SNAPSHOT ON GO IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))BEGIN TRANSACTIONIF EXISTS ( SELECT 1FROM dbo.TestWHERE Id = @Id )UPDATE dbo.TestSET [Counter] = [Counter] + 1WHERE Id = @Id;ELSEINSERT dbo.Test( Id, Name, [Counter] )VALUES ( @Id, @Name, 1 );COMMIT GO

上述解決方案也會出現插入重復鍵問題不可取。

解決方案六（提升隔離級別+表變量）

IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))DECLARE @updated TABLE ( i INT );SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTIONUPDATE TestSET [Counter] = [Counter] + 1OUTPUT DELETED.IdINTO @updatedWHERE Id = @Id;IF NOT EXISTS ( SELECT iFROM @updated )INSERT INTO Test( Id, Name, counter )VALUES ( @Id, @Name, 1 );COMMIT GO

經過多次認證也是零錯誤，貌似通過表變量形式實現可行。

解決方案七（提升隔離級別+Merge）

通過Merge關鍵來實現存在即更新否則則插入，同時我們應該注意設置隔離級別為?SERIALIZABLE??否則會出現插入重復鍵問題，代碼如下：

IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))SET TRAN ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTIONMERGE Test AS [target]USING( SELECT @Id AS Id) AS sourceON source.Id = [target].IdWHEN MATCHED THENUPDATE SET[Counter] = [target].[Counter] + 1WHEN NOT MATCHED THENINSERT ( Id, Name, [Counter] )VALUES ( @Id, @Name, 1 );COMMIT GO

多次認證無論是并發100個線程還是并發200個線程依然沒有異常信息。

總結

本節我們詳細討論了在并發中如何處理存在即更新，否則即插入問題的解決方案，目前來講以上三種方案可行。

解決方案一（最高隔離級別 + 更新鎖）

IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))BEGIN TRANSACTION;UPDATE dbo.Test WITH ( UPDLOCK, HOLDLOCK )SET [Counter] = [Counter] + 1WHERE Id = @Id;IF ( @@ROWCOUNT = 0 )BEGININSERT dbo.Test( Id, Name, [Counter] )VALUES ( @Id, @Name, 1 );ENDCOMMIT GO

解決方案二（最高隔離級別 + 表變量）

IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))DECLARE @updated TABLE ( i INT );SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;BEGIN TRANSACTIONUPDATE TestSET [Counter] = [Counter] + 1OUTPUT DELETED.idINTO @updatedWHERE id = @id;IF NOT EXISTS ( SELECT iFROM @updated )INSERT INTO Test( Id, Name, counter )VALUES ( @Id, @Name, 1 );COMMIT GO

解決方案三（最高隔離級別 + Merge）

暫時只能想到這三種解決方案，個人比較推薦方案一和方案三，請問您有何高見，請留下您的評論若可行，我將進行后續補充。

2017-06-03更新

本博文的評論非常精彩，同時對于小菜的我又重新學習了下存在即更新反之則插入的解決方案。本文重新更新已經過了兩天，期間我是一直在看這方面的東西更加深入的理解有些基礎方面的東西還是說的太籠統并且是我自身不是很理解而導致，菜不可怕，可怕的是還不深入學習自認為自己的是對的，你說呢。

首先我們得理解UPDLOCK和HOLDLOCK鎖的作用是什么，HOLDLOCK類似于SERIALIZABLE隔離級別，對于共享鎖我們是可以讀，但是不能進行更新和刪除和插入直到當前并發事務完成，而UPDLOCK園中博文的解釋：是允許您讀取數據（不阻塞其它事務）并在以后更新數據，同時確保自從上次讀取數據后數據沒有被更改。當我們用它來讀取記錄時可以對取到的記錄加上更新鎖，從而加上鎖的記錄在其它的線程中是不能更改的只能等本線程的事務結束后才能更改。通俗易懂點說，它不會阻塞并發的查詢和插入操作，但是會阻塞更新或者刪除對于當前事務查詢出的數據，當查詢到該數據存在時則有更新鎖切換到排它鎖。所以對于上述結尾總結的三種解決方案，我們再來闡述下。

解決方案一（HOLDLOCK）

IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))BEGIN TRANSACTION;UPDATE dbo.Test WITH ( HOLDLOCK )SET [Counter] = [Counter] + 1WHERE Id = @Id;IF ( @@ROWCOUNT = 0 )BEGININSERT dbo.Test( Id, Name, [Counter] )VALUES ( @Id, @Name, 1 );ENDCOMMIT GO

如果我們未加上HOLDLOCK鎖提示，雖然UPDATE會獲取排它鎖，但是排它鎖不會持續到事務結束一直保持著所以會導致插入重復鍵的問題，當我們加上HOLDLOCK鎖提示上述也說到類似悲觀并發中的最高隔離級別，該鎖提示一直會持續到事務結束，當有并發請求過來時，若此時查詢到數據存在則會進行更新操作但是事務還未進行提交，此時其他請求將會也查到該行記錄存在，但是會被當前的事務更新操作鎖阻塞，若此時查詢到數據不存在時同理如此。

解決方案二（UPDLOCK + HOLDLOCK）

IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))BEGIN TRANSACTIONIF EXISTS ( SELECT 1FROM dbo.Test WITH(UPDLOCK, HOLDLOCK)WHERE Id = @Id )UPDATE dbo.TestSET [Counter] = [Counter] + 1WHERE Id = @Id;ELSEINSERT dbo.Test( Id, Name, [Counter] )VALUES ( @Id, @Name, 1 );COMMIT GO

對于上述查詢對比第一種解決方案我們加上了UPDLOCK更新鎖代替SELECT的共享鎖，目的是當所傳遞的變量Id所查詢的行記錄不存在時不會導致阻塞，讓其進行插入，也就是說不阻塞其他事務的插入并確保自上次以來行記錄未被修改過，對于HOLDLOCK為了確保一直到事務釋放鎖，從而達到我們的期望。總結起來一句話，如果查詢期間行記錄存在則鎖定的資源則查詢存在的行記錄上，如果查詢期間行記錄不存在，那么通過HOLDLOCK來獲取主鍵上的范圍鎖來防止在釋放鎖之前插入重復鍵，所以UPDLOCK為了解決并發更新不阻塞其他事務查詢，HOLDLOCK防止并發插入重復鍵。

解決方案三（SERIALIZABLE + Merge）

IF OBJECT_ID('TestPro') IS NOT NULLDROP PROCEDURE TestPro; GOCREATE PROCEDURE TestPro ( @Id INT ) ASDECLARE @Name NCHAR(100) = CAST(@Id AS NCHAR(100))BEGIN TRANSACTIONMERGE Test WITH(SERIALIZABLE ) AS [target]USING( SELECT @Id AS Id) AS sourceON source.Id = [target].IdWHEN MATCHED THENUPDATE SET[Counter] = [target].[Counter] + 1WHEN NOT MATCHED THENINSERT ( Id, Name, [Counter] )VALUES ( @Id, @Name, 1 );COMMIT GO

測試

UPDLOCK.UPDLOCK 的優點是允許您讀取數據（不阻塞其它事務）并在以后更新數據，同時確保自從上次讀取數據后數據沒有被更改。當我們用UPDLOCK來讀取記錄時可以對取到的記錄加上更新鎖，從而加上鎖的記錄在其它的線程中是不能更改的只能等本線程的事務結束后才能更改.

示例：

測試:

在另一個查詢里:

BEGIN TRANSACTION

SELECT * FROM myTable?WITH (UPDLOCK)?WHERE Id in (1,2,3)

waitfor delay '00:00:10'?

update myTable?set [Name]='ZZ' where?Id in (1,2,3)
commit?TRANSACTION

在另一個查詢里:
SELECT * FROM myTable?WHERE Id in (1,2,3)

可以馬上查詢到數據。

但如果要更新數據，必須等其他更新鎖釋放后才能執行。

update?myTable?set [Name]='ZZ' where?Id in (1,2,3)

這就說明,有時候需要控制某條記錄在我讀取后就不許再進行更新,那么我就可以將所有要處理當前記錄的查詢都加上更新鎖,以防止查詢后被其它事務修改.將事務的影響降低到最小

轉載于:https://www.cnblogs.com/EasyLive2006/p/7630165.html

總結

以上是生活随笔為你收集整理的数据库存在即更新的并发处理 - 转的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。