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

虽然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

可以看到除对表和唯一索引上加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--存在则更新问题

最新文章

热门文章