前言

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

探讨存在就更新七种方案

首先我们来创建测试表

  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    

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

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

同时开启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    

此时问题依旧和解决方案一无异(如果降低级别为最低隔离级别,如果行记录为空,前一事务如果未进行提交,当前事务也能读取到该行记录为空,如果当前事务插入进去并进行提交,此时前一事务再进行提交此时就会出现插入重复键问题)

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

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

在这种情况下更加糟糕,直接到会导致死锁

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

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

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

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

  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    

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

运行多次均未发现出现什么异常,通过查询数据时使用更新锁而非共享锁,这样的话一来可以读取数据但不阻塞其他事务,二来还确保自上次读取数据后数据未被更改,这样就解决了死锁问题。貌似这样的方案是可行得,如果是高并发不知是否可行。

解决方案五(提升隔离级别为行版本控制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    

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

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

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

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

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

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持沃谷博客。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注