正文

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

IP云
IP云V管理员/0 阅读
09/13
小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

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

在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的查询，也是不阻塞（MySQL的gap锁不阻塞，也就是说两个gap锁，锁定的范围完全一致的时候，如果这个范围内没有数据，则不会互相阻塞）。
 因此无法通过先判断是否存在，再决定是插入或者更新的方式来实现。

20191213补充：

mysql中，对于不存再插入，存在则更新，从sql语句上是无法解决的，经测试，以下Version1和Version2两种写法，并发时均潜在死锁的问题
Version1
insert into t01 (parameter_key )
SELECT parameter_key
WHERE NOT EXISTS
(SELECT 1 FROM t01 AS t WHERE t.key = parameter_key);
Version2
insert into t01 (parameter_key )
SELECT parameter_key
ON DUPLICATE KEY UPDATE
lastupdate_date = now()

如下Version3的写法，会给出警告
Version3
insert IGNORE into t01 (parameter_key )
SELECT parameter_key

insert ignore与ON DUPLICATE KEY UPDATE是不兼容的，也就是说，MySQL想要达到“存在更新不存在插入的效果”，sql层面是无法解决的

除非显式锁定表，执行插入或者更新语句，最后解锁
#cursor.execute('lock tables t01 WRITE,t01 AS t read;')
cursor.execute(sql_statment)
#cursor.execute('unlock tables;')

https://juejin.im/entry/5adca48df265da0b9c1037c8https://blog.csdn.net/pml18710973036/article/details/78452688http://xiajunhust.github.io/2018/11/30/MySQL%20Insert%20on%20duplicate%E9%94%81/