MySQL中一个经典的并发场景。当发生 Duplicate entry 错误时，事务仍然持有 S锁（共享锁），这在某些情况下确实可能导致死锁。这现象在Oracle中并不存在，在MySQL无论事务隔离级别是REPEATABLE READ还是READ COMMITTED都存在这个问题，2个会话相同的SQL可能就会导致死锁的现象，如有些业务习惯在一个事务先insert 再update 同一记录。
演示
mysql> use anbob;
Database changed
mysql> create table t10(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.01 sec)当数据不重复时
| 时间点 | 事务1 | 事务2 | 现象 |
| 1 | begin; insert into t10 values(1,’anbob); | 插入成功 | |
| 2 | begin; insert into t10 values(1,’anbob’); | 因为事务1持有这条数据的排它锁，所以事务2进入锁等待状态 | |
| 3 | update t10 set name =’ weejar’ where id = 1; | 事务1update也可以正常执行 | |
| 4 | 事务2触发超时或者持续等待 |
mysql> select * from t10;
+----+--------+
| id | name |
+----+--------+
| 1 | weejar |
+----+--------+| time | 事务A | 事务B | 锁查询 |
| 1 | mysql> select * from t10; +—-+——–+ | id | name | +—-+——–+ | 1 | weejar | +—-+——–+ mysql> insert into t10 values(1,’anbob’); ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘t10.PRIMARY’ (获取id=1的S锁) | mysql> select trx_id,trx_state,trx_started,trx_isolation_level from information_schema.innodb_trx; +——–+———–+———————+———————+ | trx_id | trx_state | trx_started | trx_isolation_level | +——–+———–+———————+———————+ | 9766 | RUNNING | 2025-12-27 18:42:54 | REPEATABLE READ | +——–+———–+———————+———————+ 1 row in set (0.00 sec) mysql> select thread_id,ENGINE_TRANSACTION_ID,OBJECT_NAME,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks; +———–+———————–+————-+———–+—————+————-+———–+ | thread_id | ENGINE_TRANSACTION_ID | OBJECT_NAME | lock_type | lock_mode | lock_status | lock_data | +———–+———————–+————-+———–+—————+————-+———–+ | 51 | 9766 | t10 | TABLE | IX | GRANTED | NULL | | 51 | 9766 | t10 | RECORD | S,REC_NOT_GAP | GRANTED | 1 | +———–+———————–+————-+———–+—————+————-+———–+ 2 rows in set (0.00 sec) | |
| 2 | mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t10 values(1,’anbob’); ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘t10.PRIMARY’ (获取id=1的S锁) | mysql> select trx_id,trx_state,trx_started,trx_isolation_level from information_schema.innodb_trx; +——–+———–+———————+———————+ | trx_id | trx_state | trx_started | trx_isolation_level | +——–+———–+———————+———————+ | 9767 | RUNNING | 2025-12-27 18:44:25 | REPEATABLE READ | | 9766 | RUNNING | 2025-12-27 18:42:54 | REPEATABLE READ | +——–+———–+———————+———————+ 2 rows in set (0.00 sec) mysql> select thread_id,ENGINE_TRANSACTION_ID,OBJECT_NAME,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks; +———–+———————–+————-+———–+—————+————-+———–+ | thread_id | ENGINE_TRANSACTION_ID | OBJECT_NAME | lock_type | lock_mode | lock_status | lock_data | +———–+———————–+————-+———–+—————+————-+———–+ | 52 | 9767 | t10 | TABLE | IX | GRANTED | NULL | | 52 | 9767 | t10 | RECORD | S,REC_NOT_GAP | GRANTED | 1 | | 51 | 9766 | t10 | TABLE | IX | GRANTED | NULL | | 51 | 9766 | t10 | RECORD | S,REC_NOT_GAP | GRANTED | 1 | +———–+———————–+————-+———–+—————+————-+———–+ 4 rows in set (0.00 sec) | |
| 3 | mysql> update t10 set name=’weejar’ where id=1; –waiting 尝试获取id=1的X锁 (等待事务B的S锁释放) | mysql> select thread_id,ENGINE_TRANSACTION_ID,OBJECT_NAME,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks; +———–+———————–+————-+———–+—————+————-+———–+ | thread_id | ENGINE_TRANSACTION_ID | OBJECT_NAME | lock_type | lock_mode | lock_status | lock_data | +———–+———————–+————-+———–+—————+————-+———–+ | 52 | 9767 | t10 | TABLE | IX | GRANTED | NULL | | 52 | 9767 | t10 | RECORD | S,REC_NOT_GAP | GRANTED | 1 | | 51 | 9766 | t10 | TABLE | IX | GRANTED | NULL | | 51 | 9766 | t10 | RECORD | S,REC_NOT_GAP | GRANTED | 1 | | 51 | 9766 | t10 | RECORD | X,REC_NOT_GAP | WAITING | 1 | +———–+———————–+————-+———–+—————+————-+———–+ 5 rows in set (0.00 sec) | |
| 4 | 事务2deadlock 中断，事务1更新成功 | mysql> update t10 set name=’weejar’ where id=1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction => DEADLOCK! | mysql> select thread_id,ENGINE_TRANSACTION_ID,OBJECT_NAME,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks; +———–+———————–+————-+———–+—————+————-+———–+ | thread_id | ENGINE_TRANSACTION_ID | OBJECT_NAME | lock_type | lock_mode | lock_status | lock_data | +———–+———————–+————-+———–+—————+————-+———–+ | 51 | 9766 | t10 | TABLE | IX | GRANTED | NULL | | 51 | 9766 | t10 | RECORD | S,REC_NOT_GAP | GRANTED | 1 | | 51 | 9766 | t10 | RECORD | X,REC_NOT_GAP | GRANTED | 1 | +———–+———————–+————-+———–+—————+————-+———–+ 3 rows in set (0.00 sec) |
在读已提交隔离级别测试同样存在
mysql> set transaction isolation level read committed;
... 相同的操作
mysql> select trx_id,trx_state,trx_started,trx_isolation_level from information_schema.innodb_trx;
+--------+-----------+---------------------+---------------------+
| trx_id | trx_state | trx_started | trx_isolation_level |
+--------+-----------+---------------------+---------------------+
| 9765 | RUNNING | 2025-12-27 18:28:45 | READ COMMITTED |
| 9762 | RUNNING | 2025-12-27 18:27:29 | READ COMMITTED |
+--------+-----------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> select thread_id,ENGINE_TRANSACTION_ID,OBJECT_NAME,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+
| thread_id | ENGINE_TRANSACTION_ID | OBJECT_NAME | lock_type | lock_mode | lock_status | lock_data |
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+
| 52 | 9765 | t10 | TABLE | IX | GRANTED | NULL |
| 52 | 9765 | t10 | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
| 51 | 9762 | t10 | TABLE | IX | GRANTED | NULL |
| 51 | 9762 | t10 | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
| 51 | 9762 | t10 | RECORD | X,REC_NOT_GAP | WAITING | 1 |
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)
mysql> select trx_id,trx_state,trx_started,trx_isolation_level from information_schema.innodb_trx;
+--------+-----------+---------------------+---------------------+
| trx_id | trx_state | trx_started | trx_isolation_level |
+--------+-----------+---------------------+---------------------+
| 9765 | RUNNING | 2025-12-27 18:28:45 | READ COMMITTED |
| 9762 | RUNNING | 2025-12-27 18:27:29 | READ COMMITTED |
+--------+-----------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> select thread_id,ENGINE_TRANSACTION_ID,OBJECT_NAME,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+
| thread_id | ENGINE_TRANSACTION_ID | OBJECT_NAME | lock_type | lock_mode | lock_status | lock_data |
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+
| 52 | 9765 | t10 | TABLE | IX | GRANTED | NULL |
| 52 | 9765 | t10 | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
| 51 | 9762 | t10 | TABLE | IX | GRANTED | NULL |
| 51 | 9762 | t10 | RECORD | S,REC_NOT_GAP | GRANTED | 1 |
| 51 | 9762 | t10 | RECORD | X,REC_NOT_GAP | WAITING | 1 |
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)问题核心
在 INSERT 操作中，MySQL的执行流程是这样的：
- 检查阶段：首先会对唯一索引进行检查，这个检查需要获取 S锁（共享锁）
- 插入阶段：如果检查通过，才会尝试获取 X锁（排他锁） 并插入数据
- 错误处理：如果发现重复键，事务会回滚到保存点，但 S锁并不会立即释放
为什么S锁不立即释放？防止其他事务修改或删除这条记录，导致幻读；及频繁的锁获取/释放开销较大
解决方案
1. 使用 INSERT ... ON DUPLICATE KEY UPDATE
# 事务A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t10 values(1,'anbob')
-> on duplicate key update name='weejar';
Query OK, 0 rows affected (0.00 sec)
mysql> select thread_id,ENGINE_TRANSACTION_ID,OBJECT_NAME,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+
| thread_id | ENGINE_TRANSACTION_ID | OBJECT_NAME | lock_type | lock_mode | lock_status | lock_data |
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+
| 51 | 9768 | t10 | TABLE | IX | GRANTED | NULL |
| 51 | 9768 | t10 | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)
# 事务B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t10 values(1,'anbob')
-> on duplicate key update name='weejar';
-- waitting
mysql> select thread_id,ENGINE_TRANSACTION_ID,OBJECT_NAME,lock_type,lock_mode,lock_status,lock_data from performance_schema.data_locks;
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+
| thread_id | ENGINE_TRANSACTION_ID | OBJECT_NAME | lock_type | lock_mode | lock_status | lock_data |
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+
| 52 | 9769 | t10 | TABLE | IX | GRANTED | NULL |
| 52 | 9769 | t10 | RECORD | X,REC_NOT_GAP | WAITING | 1 |
| 51 | 9768 | t10 | TABLE | IX | GRANTED | NULL |
| 51 | 9768 | t10 | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+-----------+-----------------------+-------------+-----------+---------------+-------------+-----------+2. 使用 REPLACE INTO 或 merge into