SQL Server与MySQL在“存在则更新,不存在则插入”并发处理上的一些差异。

服务器

浏览数:242

2019-7-8

AD:资源代下载服务

“存在则更新,不存在则插入的逻辑”并发情况下的处理

在sqlserver中:

在sqlserver中,是通过可序列化隔离级别+排它锁的方式来锁定一个范围来实现的
当前锁定一个不存在的记录的时候,sqlserver是通过范围锁来实现的,具体锁定的范围,表中已存在的数据和当前具体判断的Id有关
参考之前写的一篇文章:http://www.cnblogs.com/wy123/p/7501261.html

简单举个例子,如下表中的表中没有任何数据行,Id 字段是primary key

当前Session锁定一个不存在的记录
在另外一个Session中试图锁定相同的记录的时候被阻塞(go提交之后没有任何返回结果,实际上是Session被阻塞)

第一个Session开水器事务

 实际上当前Session锁定的范围是从表中的最小值(没有最小值就是无穷小)到无穷大的一个范围
 也就是说说不但锁定了当前Session锁定的Id = 66的数据,甚至只66到正无穷大的数据也被锁定.

以上也即就是sqlserver中范围锁的效果以及适应的场景,可能有其他中写的变种,比如with(serializable),或者with(holdlock),或者先更新再判断受影响行数啥的
本质上都是:序列化隔离级别+事务+排它锁,不但可以锁定已存在的记录行,也可以锁定不存在的记录行。
因此不必纠结各种写法的差异,本质都是一样的。

set transaction isolation level serializable;
begin tran
    if exists (select * from TestLockNotExistId with(xlock) where Id = 66)
    begin
        --更新
        update TestLockNotExistId set CreateDate = getdate()
    end
    else
    begin
        --插入
        insert TestLockNotExistId values (66,'xxx',getdate())
    end
commit

在MySQL中:

在MySQL中,是通过insert into values on duplicate key update语法实现的,
虽然MySQL中有类似于SQLServer中显式加锁的语法,也即select from where for update,原本以为可以使用 for update语法来照搬SQL Server的方式实现类似资源隔离
但是经过测试时候,mysql的for update方式显然是锁不住不存在的记录的
但是select from where for update只能锁定已存在的记录,而锁不住不存在的记录

以下测试,无法锁住不存在的记录

可以锁定已存在的记录

 因此MySQL中的GAP锁,虽然表面含义也是区间锁(范围锁),与SQLServer中的范围锁,在细节上还是有一定的差异的。
 MySQL在默认的Reapted Read隔离级别下,虽然通过GAP锁解决了幻读的问题,
 但是这种锁仅仅是在读写之间阻塞(互斥)的,在读与读之间,即便是select显式加排它锁的方式,不同Session的同一个不存在Id的查询,也是不阻塞。
 因此无法通过先判断是否存在,再决定是插入或者更新的方式来实现。

 

作者:MSSQL123