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

 

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

在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中：
参考https://stackoverflow.com/questions/17068686/how-do-i-lock-on-an-innodb-row-that-doesnt-exist-yet，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锁，锁定的范围完全一致的时候，如果这个范围内没有数据，则不会互相阻塞）。
 因此无法通过先判断是否存在，再决定是插入或者更新的方式来实现。

这里https://bugs.mysql.com/bug.php?id=48911 有一个死锁的示例，虽然是2009年提交的，但是在MySQL 5.7.24 REPEATABLE-READ下依旧存在

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层面是无法解决的

纯sql层面，各种奇淫技巧根本无法解决死锁的问题，因此最终方案：
1，除非显式锁定表，执行插入或者更新语句，最后解锁
#cursor.execute('lock tables t01 WRITE,t01 AS t read;')
cursor.execute(sql_statment)
#cursor.execute('unlock tables;')

2，增加异常重试次数

 

参考这里：https://juejin.im/entry/5adca48df265da0b9c1037c8
在MySQL5.7.24版本下，以下case依然可以重现，只要提交第一个事务，第二三个事务就会出现死锁(这里也有演示https://mingwho.com/posts/insert-on-duplicate/)，
貌似再MySQL 5.7.26已经fix这个bug，https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-26.html
MySQL 8.0下改case没有重现，可能已经解决（https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html）？？？
https://bugs.mysql.com/bug.php?id=98324

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-26.html

 

 

 

 MySQL 8.0下类似场景也有人反馈死锁问题
https://forums.percona.com/t/puzzling-deadlocks-with-on-duplicate-key-update/7650/2

 

 

 

MySQL的Insert on duplicate 在并发下，真的能完美实现不存在插入存在更新操作？ 

https://stackoverflow.com/questions/17068686/how-do-i-lock-on-an-innodb-row-that-doesnt-exist-yet
这里有提到

 

 

 

 

https://juejin.cn/post/6844904106742513672

mysqlde GAP锁是兼容的，因此，if not exits then insert 这种做法会引起死锁 https://medium.com/@tanishiking/avoid-deadlock-caused-by-a-conflict-of-transactions-that-accidentally-acquire-gap-lock-in-innodb-a114e975fd72，问题是insert into on duplicate update 能完全避免死锁？
从这链接看，https://stackoverflow.com/questions/45652775/thread-safety-of-insert-on-duplicate-key-update，insert into on duplicate update并不是安全的，这个语句并非一个原子操作
The insert ... on duplicate key update... statement may fail only if the delete statement acquires the exclusive lock on the duplicate record after the duplicate key error and holds the lock so long that the insert's transaction times out.
这里也有case，https://mingwho.com/posts/insert-on-duplicate/，理论上将， insert ... on duplicate key update... 并没有从本质上改善select where id = ? for update这种用法。

 

https://juejin.im/entry/5adca48df265da0b9c1037c8
https://blog.csdn.net/pml18710973036/article/details/78452688
http://xiajunhust.github.io/2018/11/30/MySQL%20Insert%20on%20duplicate%E9%94%81/
https://fastmail.blog/2017/12/09/mysql-lock-nonexistent-row/