mysql并发插入死锁_lock_mode x insert intention waiting

mysql并发插入死锁

场景

多个线程同时执行条件插入语句
SQL：

INSERT INTO system_lock (resource, owner, token, version, lock_time, release_time) SELECT ?, ?, ?, 0, ?, ? FROM DUAL WHERE NOT EXISTS(SELECT resource FROM system_lock WHERE resource = ?)
1

状态跟踪

执行命令查看 innodb 状态

show engine innodb status
1

部分结果：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-11-18 10:57:27 0x7f8cad4f4700
*** (1) TRANSACTION:
TRANSACTION 1484317, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL t
1
2
3
4
5
6
7
8