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

前言

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

探讨存在就更新七种方案

首先我们来创建测试表

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

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

2017-06-03更新

本博文的评论非常精彩，同时对于小菜的我又重新学习了下存在即更新反之则插入的解决方案。本文重新更新已经过了两天，期间我是一直在看这方面的东西更加深入的理解有些基础方面的东西还是说的太笼统并且是我自身不是很理解而导致，菜不可怕，可怕的是还不深入学习自认为自己的是对的，你说呢。

首先我们得理解UPDLOCK和HOLDLOCK锁的作用是什么，HOLDLOCK类似于SERIALIZABLE隔离级别，对于共享锁我们是可以读，但是不能进行更新和删除和插入直到当前并发事务完成，而UPDLOCK园中博文的解释：是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。当我们用它来读取记录时可以对取到的记录加上更新锁，从而加上锁的记录在其它的线程中是不能更改的只能等本线程的事务结束后才能更改。通俗易懂点说，它不会阻塞并发的查询和插入操作，但是会阻塞更新或者删除对于当前事务查询出的数据，当查询到该数据存在时则有更新锁切换到排它锁。所以对于上述结尾总结的三种解决方案，我们再来阐述下。

解决方案一（HOLDLOCK）

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 ( 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

如果我们未加上HOLDLOCK锁提示，虽然UPDATE会获取排它锁，但是排它锁不会持续到事务结束一直保持着所以会导致插入重复键的问题，当我们加上HOLDLOCK锁提示上述也说到类似悲观并发中的最高隔离级别，该锁提示一直会持续到事务结束，当有并发请求过来时，若此时查询到数据存在则会进行更新操作但是事务还未进行提交，此时其他请求将会也查到该行记录存在，但是会被当前的事务更新操作锁阻塞，若此时查询到数据不存在时同理如此。

解决方案二（UPDLOCK + HOLDLOCK）

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 WITH(UPDLOCK, HOLDLOCK)
                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

对于上述查询对比第一种解决方案我们加上了UPDLOCK更新锁代替SELECT的共享锁，目的是当所传递的变量Id所查询的行记录不存在时不会导致阻塞，让其进行插入，也就是说不阻塞其他事务的插入并确保自上次以来行记录未被修改过，对于HOLDLOCK为了确保一直到事务释放锁，从而达到我们的期望。总结起来一句话，如果查询期间行记录存在则锁定的资源则查询存在的行记录上，如果查询期间行记录不存在，那么通过HOLDLOCK来获取主键上的范围锁来防止在释放锁之前插入重复键，所以UPDLOCK为了解决并发更新不阻塞其他事务查询，HOLDLOCK防止并发插入重复键。

解决方案三（SERIALIZABLE + 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))
    BEGIN TRANSACTION
    MERGE Test WITH(SERIALIZABLE ) 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

结论

写这篇博客后看到的评论才明白过来对并发存在即更新否则插入是一知半解，此前的我认为更新语句获取排它锁，但是HOLDLOCK等同于SERIALIZABLE获取共享锁，但是排它锁和共享锁是互斥的，怎么可以在更新语句中添加HOLDLOCK提示呢？纠结了很久，只能说HOLDLOCK锁提示的作用类似于SERIALIZABLE，一个属于锁，而SERIALIZABLE 属于隔离级别，基于这点二者是不一样的，同时我一直认为在存储过程中加上SERIALIZABLE隔离级别和语句中加上HOLDLOCK作用是一样的，其实不然，在存储过程中加上隔离级别和语句中加上HOLDLOCK作用域不一样，存储过程中加上SERIALIZABLE隔离级别对整个会话都起作用，而在语句中使用锁提示只是对当前执行语句其作用，这里感谢园友【笑东风】的指教，同时也感谢园友【MSSQL123】让我明白了我混淆了锁和隔离级别。

 

其实对于上述三种最终解决方案而言对于少量并发而言没有什么问题，上述对于存在即更新否则插入的并发方案只是降低了并发可能发生重复键的可能性，就像园友【Jacklondon Chen】所说，同时如上述第二种解决方案而言，如果行记录不存在那么UPDLOCK就不起作用，对于高并发而言利用HOLDLOCK虽然会阻塞插入，但是理论上来说估计依然会发生插入重复键的问题，此时推荐利用园友【Jacklondon Chen】的解决方案，园友专门写了一篇博客来讲述本篇博文的讨论以作为参考：http://www.cnblogs.com/jacklondon/p/programming_experience_concurrent.html

 

时间： 2024-10-23 01:35:20

探讨SQL Server并发处理存在就更新七种解决方案的相关文章

探讨SQL Server并发处理队列数据不阻塞解决方案

前言 之前对于并发这一块确实接触的比较少,自从遇到现在的老大,每写完一块老大都会过目一下然后给出意见,期间确实收获不少,接下来有几篇会来讲解SQL Server中关于并发这一块的内容,有的是总结,有的是学习,若有错误见解请批评性指出. SQL Server并发处理队列数据问题 在我们的项目中对于购买产品的用户会对应分配卡密,同时会更新其卡密的状态为已使用,所以当出现并发时此时我们不加以控制会导致同一个卡号和密码被不同的用户所使用,这样的情况是不能允许的,此时我们迫切需要解决对卡密使用后的更新和产

SQL Server ->> 深入探讨SQL Server 2016新特性之 --- Temporal Table（历史表）

原文:SQL Server ->> 深入探讨SQL Server 2016新特性之 --- Temporal Table(历史表) 作为SQL Server 2016(CTP3.x)的另一个新特性,Temporal Table(历史表)记录了表历史上任何时间点所有的数据改动.Temporal Table其实早在ANSI SQL 2011就提出了,而SAP HANA, DB2和Oracle早已在它们的产品中加入/实现了这一特性.所以说微软其实是落后了几个竞争对手.既然在CTP3.0中加入了,相信

asp.net excle表批量导入到sql server 2000的同时实现更新功能

问题描述 asp.net excle表批量导入到sql server 2000的同时实现更新功能 excle批量导入功能已经实现了,用的SqlBulkCopy. 但怎样能实现更新功能呢? 导入的数据表内容很多,都是上万条数据的,所以要保证效率! 有知道的大神,请给我详细代码,谢谢! 解决方案 我用的下面代码实现的,是在网上找的方法,主要思路是,两个表比较,删除相同数据,然后用SqlBulkCopy导入数据. 现在的问题是,如果excle表和sql server表的数据很多,执行起来会很慢,经常超

探讨SQL Server 2005的评价函数

一. 简介 在2005年11月份,微软发行了三种新产品系列:Visual Studio 2005,SQL Server 2005和.NET框架2.0(它包括ASP.NET 2.0).SQL Server 2005是微软自从其上一个主要发行版本SQL Server 2000以来最新版本的数据库平台.在过去五年的发展中,SQL Server中加入了大量的新特征,所有这些新内容都被总结到微软网站的一篇文章<What's New in SQL Server 2005?>中.使用SQL Server 2

SQL Server 2008空间数据应用系列七：基于Bing Maps(Silverlight) 的空间数据展现

原文:SQL Server 2008空间数据应用系列七:基于Bing Maps(Silverlight) 的空间数据展现 友情提示,您阅读本篇博文的先决条件如下: 1.本文示例基于Microsoft SQL Server 2008 R2调测. 2.具备 Transact-SQL 编程经验和使用 SQL Server Management Studio 的经验. 3.具有使用 Microsoft Visual Studio 进行 Microsoft .NET Framework开发的经验. 4.具

使用SQL Server 2005 Express Edition的三种场合

SQL Server 2005 Express Edition 版本是SQL Sever的免费版,它包含在微软的几个产品中.本文介绍使用SQL Server 2005 Express Edition的3种场合. SQL Server 2005 Express Edition 版本是SQL Sever的免费版,它包含在微软的几个产品中.但是与Visual Studio 2005 捆绑的这一版本产品缺少关键组建:Management Studio(相当于企业管理器). 微软提供了解决方案:允许用户下

深入探讨SQL Server 2000对XML的支持

server|xml  ［作者简介:］Wayne,新一代程序员,十二岁时开始学习编程,十三岁时拥有自己的电脑,先后学过Basic.Pascal.C.FOXPRO.VB.DELPHI.C++.SQL,学习Java语言后,遂成为Java的疯狂崇拜者,现就读于中国科技大学. 引言 内容导航  * 引言 * 配置SQL Server 2000的IIS虚拟目录 * 使用 HTTP 执行 SQL 语句 * 使用XML模板进行查询 * XPath查询 架构和模板   我们可以很简单的使用ADO访问数据库中的数

SQL Server 2014的可更新列存储聚集索引新特性探秘

简介 列存储索引其实在在SQL Server 2012中就已经存在,但SQL Server 2012中只允许建立非聚集列索引,这意味着列索引是在原有的行存储索引之上的引用了底层的数据,因此会消耗更多的存储空间,但2012中的限制最大的还是一旦将非聚集列存储索引建立在某个表上时,该表将变为只读,这使得即使在数据仓库中使用列索引,每次更新数据都变成非常痛苦的事.SQL Server 2014中的可更新聚集列索引则解决了该问题. 可更新聚集列存储索引? 聚集列存储索引的概念可以类比于传统的行存储,聚集

深入探讨SQL Server 2000对XML的支持(一)

我们可以很简单的使用ADO访问数据库中的数据,但是,如果我们想把从数据库中检索得到的数据以XML的格式显示出来的话,就要费点神了,当然,我们可以去找一些现成的应用程序或者是把现有的存储过程修改一下,来完成这个想法. 虽说ADO2.5宣称支持XML,但是它仍需要一个额外的层来处理把数据转化成XML的过程.幸好有了SQL Server 2000,它宣称可以直接从数据库中取出数据,而不通过ADO2.5直接把数据以XML方式显示出来.这个功能极大的提高了构造分布式.数据集中的应用程序的性能,因为这个特性