浅谈Mysql insert on duplicate key 死锁问题定位与解决

浅谈Mysql insert on duplicate key 死锁问题定位与解决

问题描述

在MySQL中，执行insert操作时，可以使用on duplicate key来进行唯一键冲突时的处理。但是，当多个线程并发地执行insert操作的时候，可能会出现死锁问题。

定位死锁问题

当出现死锁问题时，可以使用show processlist命令查看正在执行的线程状态，看看是否有线程处于Waiting for table metadata lock状态。

mysql> show processlist;
+-----+------+-----------+------+---------+------+--------------------------+---------------------------------------+----------+
| Id  | User | Host      | db   | Command | Time | State                    | Info                                  | Progress |
+-----+------+-----------+------+---------+------+--------------------------+---------------------------------------+----------+
| 195 | root | localhost | test | Query   |    0 | starting                 | show processlist                      |    0.000 |
| 196 | root | localhost | test | Sleep   |   75 |                          | NULL                                  |    0.000 |
| 197 | root | localhost | test | Query   |   75 | Waiting for table metadata lock | INSERT INTO table (id, name) VALUES (1, 'test') ON DUPLICATE KEY UPDATE name='test' |
+-----+------+-----------+------+---------+------+--------------------------+---------------------------------------+----------+

如上所示，发现有一个线程处于Waiting for table metadata lock状态，并且是在执行insert语句，因此可以初步定位是insert on duplicate key导致的死锁问题。

接下来，可以使用show engine innodb status命令查看InnoDB的状态，并找到死锁信息。

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2022-02-14 19:10:49 0x700009955000 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds

SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 438814, signal count 1458752
Mutex spin waits 1553835, rounds 3379891, OS waits 63580
RW-shared spins 139670, rounds 1042817, OS waits 22313
RW-excl spins 114627, rounds 5660164, OS waits 142902
Spin rounds per wait: 2.17 mutex, 7.47 RW-shared, 49.36 RW-excl

[..省略..]，不是死锁信息

TRANSACTIONS
------------
Trx id counter 7038591

[..省略..]，不是死锁信息

INNODB MONITOR OUTPUT
-------
[...省略...]
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-02-14 13:03:03 0x7fa1964f1700
*** (1) TRANSACTION:
TRANSACTION 23305241, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1128, 7 row lock(s), undo log entries 7
MySQL thread id 11970, OS thread handle 0x7fa1b0fc3700, query id 79369552 192.168.170.129 root update
INSERT INTO `test`.`test` (`col1`, `col2`, `col3`) VALUES (33544124, 46158925, 7910988) ON DUPLICATE KEY UPDATE `col2`=VALUES(`col2`),`col3`=VALUES(`col3`)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 389 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 23305241 lock_mode X locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 4; hex 8004aa1c; asc     ;;
 1: len 6; hex 000022c80a12; asc   "    ;;
 2: len 7; hex 00000010003d6d; asc      =m;;
 3: len 5; hex 99914597bd; asc   E  ;;
 4: len 5; hex 99914599f7; asc   E  ;;
 5: len 5; hex 9991459cb9; asc   E  ;;
 6: len 1; hex 81; asc  ;;
 7: len 4; hex 8000000d; asc     ;;

[...省略...]

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2022-02-14 19:34:18 0x7f88914e9700 Error in foreign key constraint of table `mydb`.`t2`:

可以找到上面的LATEST DETECTED DEADLOCK信息，来进行死锁问题的定位。

解决死锁问题

1. 减小事务并发度

当多个并发事务同时访问MySQL数据时，就可能出现死锁问题。因此，可以尝试减小事务并发度，降低死锁风险。如下所示：

# 修改innodb参数，增加等待超时时间
set global innodb_lock_wait_timeout = 120;

# 减少事务并发度
SET GLOBAL innodb_thread_concurrency = 8;

# 在事务中对数据的访问越少，就越不容易出现死锁问题
START TRANSACTION;
SELECT …;
UPDATE …;
COMMIT;

使用上述方法，可以在一定程度上减少死锁的发生。

1. 修改程序逻辑

当多个线程并发地执行insert操作时，会出现死锁问题。因此，可以通过修改程序逻辑，将insert操作转换为update操作等方式来规避死锁问题。如下所示：

# 将insert操作转换为update操作
INSERT INTO table (id, name) VALUES (1, 'test') ON DUPLICATE KEY UPDATE name='test', id=LAST_INSERT_ID(id);

# 使用select for update避免死锁
START TRANSACTION;
SELECT name FROM table WHERE id=1 FOR UPDATE;
UPDATE table SET name='test' WHERE id=1;
COMMIT;

上述两种方式，可以有效的避免insert on duplicate key死锁问题的发生。

示例说明

示例1：死锁问题的原因

假设有一张test表，其中有一条数据：

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `test` (`id`, `name`) VALUES (1, 'tom');

并发执行以下insert语句可能会出现死锁问题：

INSERT INTO test (id, name) VALUES (1, 'jerry') ON DUPLICATE KEY UPDATE name='jerry';

因为当多个线程同时执行insert语句时，会占用相同的行锁，从而导致死锁问题的产生。

示例2：解决死锁问题

为了解决上述的死锁问题，可以将insert语句转换为update语句，如下所示：

INSERT INTO `test` (`id`, `name`) VALUES (1, 'jerry') ON DUPLICATE KEY UPDATE `name`='jerry', `id`=LAST_INSERT_ID(`id`);

在执行上述语句时，并不会出现死锁问题。因为在update操作时，会将id作为LAST_INSERT_ID()返回，从而避免了相同行的操作。