SQL Server并发处理存在就更新解决方案探讨

前言

本节我们来讲讲并发中最常见的情况存在即更新，在并发中若未存在行记录则插入，此时未处理好极容易出现插入重复键情况，本文我们来介绍对并发中存在就更新行记录的七种方案并且我们来综合分析最合适的解决方案。

探讨存在就更新七种方案

首先我们来创建测试表

IF OBJECT_ID('Test') IS NOT NULL DROP TABLE TestCREATE 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 ); COMMITGO

解决方案二（降低隔离级别为最低隔离级别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 ); COMMITGO

解决方案三（提升隔离级别为最高级别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 ); COMMITGO

此时将隔离级别提升为最高隔离级别会解决插入重复键问题，但是对于更新来获取排它锁而未提交，而此时另外一个进程进行查询获取共享锁此时将造成进程间相互阻塞从而造成死锁，所以从此知最高隔离级别有时候能够解决并发问题但是也会带来死锁问题。

解决方案四（提升隔离级别+良好的锁）

此时我们再来在添加最高隔离级别的基础上增添更新锁，如下：

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 ); COMMITGO

解决方案五（提升隔离级别为行版本控制SNAPSHOT）

ALTER DATABASE UpsertTestDatabaseSET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE UpsertTestDatabaseSET READ_COMMITTED_SNAPSHOT ONGO 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 ); COMMITGO

解决方案六（提升隔离级别+表变量）

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 ); COMMITGO

经过多次认证也是零错误，貌似通过表变量形式实现可行。

解决方案七（提升隔离级别+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 ); COMMITGO

总结

本节我们详细讨论了在并发中如何处理存在即更新，否则即插入问题的解决方案，目前来讲以上三种方案可行。

解决方案一（最高隔离级别 + 更新锁）

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  COMMITGO

解决方案二（最高隔离级别 + 表变量）

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 ); COMMITGO

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 ); COMMITGO

更多相关文章

随机推荐