SQL Server-存在则更新有关问题

SQL Server--存在则更新问题

在博客园看到一篇讨论特别多的文章“探讨SQL Server并发处理存在就更新七种解决方案”，这种业务需求很常见：如果记录存在就更新，不存在就插入。

最常见的做法：

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

一个很明显的问题，在高并发下可能存在操作同一条记录的多个线程都进入到INSERT环节，导致插入失败。

上面问题原因在于进入INSERT或UPDATE环节没有“排他”锁，如果每个线程在进行插入或更新前就获得记录的“排他锁”，也就解决了其他线程并发处理相同记录的可能性。换个说法，一个萝卜一个坑，先不管这个坑有没有萝卜，先把坑占上，再考虑其他的。

如何占坑呢？而且是“排他地”占坑呢？

在SQL SERVER中，排他锁即X锁，对目标加X锁有两种方式：

1、使用SELECT+WITH(XLOCK)查询提示

2、使用UPDATE/INSERT/DELETE操作

虽然SELECT+WITH(XLOCK)查询提示能做到加X锁，但是这种X锁有点“不靠谱”，MSDN给出解释：

Using XLOCK in SELECT statements will not prevent reads from happening. This is because SQL Server has a special optimization under read committed isolation level that checks if the row is dirty or not and ignores the xlock if the row has not changed. Since this is acceptable under the read committed isolation level semantics it is by design.

哪就只能UPDATE/INSERT/DELETE方式，DELETE肯定排除，直接INSERT如果碰到记录已存在又会报错，最终只能选择UPDATE，于是将业务需求实现为：

BEGIN TRANSACTION
--先尝试更新记录占坑
UPDATE  Test
SET     [Counter] = [Counter] + 1
WHERE   Id = @Id;

--如果更新操作没有影响行，证明记录不存在，则插入
IF @@ROWCOUNT<1
BEGIN
    INSERT  Test
            ( Id, Name, [Counter] )
    VALUES  ( @Id, @Name, 1 );
END
COMMIT

哪这样真的把坑占上没？

当ID=1记录不存在时，执行下面SQL:

BEGIN TRANSACTION
--先尝试更新记录占坑
UPDATE  Test
SET     [Counter] = [Counter] + 1
WHERE   Id = 1;

EXEC sp_lock @@SPID

SQL Server-存在则更新有关问题

虽然UPDATE的确会产生X排他锁，但是没有把锁“持续”地占下来，因此也无法保证高并发下对该记录的INSERT/UPDATE操作以“串行”方式执行。

要“持续”锁，也有两个办法：

1、使用WITH(HOLDLOCK)锁提示

2、使用SERIALIZABLE事务隔离级别

看下使用WITH(HOLDLOCK)锁提示获得的锁，同样当ID=1记录不存在时：

BEGIN TRANSACTION
--先尝试更新记录占坑
UPDATE  Test WITH(HOLDLOCK)
SET     [Counter] = [Counter] + 1
WHERE   Id = 1;

EXEC sp_lock @@SPID

SQL Server-存在则更新有关问题

可以看到除对表和唯一索引上加IX和IS锁以及页上IX锁外，还有一个KEY级别的范围锁RangX-X，由于范围X锁的存在，任何其他回话尝试对此范围的UPDATE和INSERT操作都将被阻塞，因此可以继续判断是否需要插入。

当然，使用SERIALIZABLE隔离级别也是相同的效果

BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--先尝试更新记录占坑
UPDATE  Test
SET     [Counter] = [Counter] + 1
WHERE   Id = 1;

EXEC sp_lock @@SPID

======================================

更新后如何判断是否需要插入呢？

方式1：使用@@ROWCOUNT来判断更新数据是否影响行，如果影响，则证明数据存在，无需更新

方式2：使用IF NOT EXISTS也是可以的，反正坑已经被占上，别的回话也不能改，再查一次就是画蛇添足而已，不影响结果。

总的还是推荐使用方式1，效率最高，避免一次SELECT操作，最后的脚本推荐为：

BEGIN TRANSACTION
--先尝试更新记录占坑
UPDATE  Test WITH(HOLDLOCK)
SET     [Counter] = [Counter] + 1
WHERE   Id = @Id;

--如果更新操作没有影响行，证明记录不存在，则插入
IF @@ROWCOUNT<1
BEGIN
    INSERT  Test
            ( Id, Name, [Counter] )
    VALUES  ( @Id, @Name, 1 );
END
COMMIT

================================================

PS1: 通常情况下，个人不太推荐修改事务隔离级别，事务隔离级别影响的是整个事务，而锁提示只影响特定语句。

================================================

PS2: 上面业务查询和更新都基于主键，在很多真实的业务场景下，主键通常为非业务键即自增键，而需要根据业务键来操作，存在以下死锁可能：

回话1：先获取聚集索引上X锁，尝试获取非聚集索引上的X锁

回话2：先获取到非聚集索引上的X锁，尝试获取聚集索引上X锁

=================================================

语文不好，本来很简单的一个东西，被自己描述成这样，各位包含。

处理问题，先看原理，再考虑如何解决，才最简单有效。

=================================================

SQL Server-存在则更新有关问题

9楼不能飚车: 虽然技术上这篇文写得很好，但我认为逻辑上思考更重要，所以我觉得 PS2 写得很对，是这篇文章意义所在

8楼MSSQL123: 胡写八道的得了20个赞，真正深入的才一个赞，还是我点的

7楼Jacklondon Chen: 感觉这个还是存在并发问题：，IF @@ROWCOUNTlt;1，BEGIN， INSERT Test

6楼自由的鱼: 这篇文章内容和评论都精彩，但是鲜有点赞的。我猜都是像我一样的菜鸟。学习了。好文章。

5楼MSSQL123: 虽然SELECT+WITH(XLOCK)查询提示能做到加X锁，但是这种X锁有点“不靠谱”，，又回头学习了一下这个http://www.cnblogs.com/CareySon/p/4226577.html，，如果对该表的操作都是通过当前这一种方式来执行的（select + xlock），应该可以锁的住，否则就锁不住。

4楼.net老吴: Parallel.ForEach(users, new ParallelOptions { MaxDegreeOfParallelism = 10

3楼Jacklondon Chen: @MSSQL123，那你说怎么测呢？，测并发当然要加延迟，不然如何测并发?

2楼Jeffcky: 楼主文章我在好好品味，我理解透了会更新博客内容，感谢楼主前辈的解读，日后对于更新数据库内容还望楼主多加指点，在此表示感谢。【另外谁说除了垃圾文章就该那么多赞，楼主的文章必须顶一个，虽然我还是小菜】！

1楼MSSQL123: WAITFOR DELAY@Jacklondon Chen，引用，@笑东风，@MSSQL123，引用引用，@Jacklondon Chen，我测试了下，数据库设置为READ_COMMITTED_SNAPSHOT后，使用HOLDLOCK还是可以正常获得X锁，所以没问题，，对你们以上的代码，我测试出来问题了。，，测试环境: Windows 8.1 64位 + SQL Server 2014 Express.，测试步骤: ，step_1, 创建数据库 test_db1。，，step_2, 运行 SQL 更改数据库属性:，ALTER DATABASE test_db1 SET SINGLE_USER WITH ROLLBACK IMMEDI...，，，，你吓我一跳，WAITFOR DELAY之后的@@RowCount都是0，不能这么测

SQL Server-存在则更新有关问题

相关推荐