Mysql死锁问题排查和解决

Mysql锁分析文档:https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

1.查看Mysql隔离级别https://www.cndba.cn/hbhe0316/article/22636https://www.cndba.cn/hbhe0316/article/22636

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.01 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

2.取消autocommithttps://www.cndba.cn/hbhe0316/article/22636

https://www.cndba.cn/hbhe0316/article/22636

mysql>  set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

3.创建测试表https://www.cndba.cn/hbhe0316/article/22636https://www.cndba.cn/hbhe0316/article/22636

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

4.插入测试数据https://www.cndba.cn/hbhe0316/article/22636

mysql> insert into account(name,amount) values ('ZhangSan','100');
Query OK, 1 row affected (0.00 sec)

mysql> insert into account(name,amount) values ('LiSi','100');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+----------+--------+
| id | name     | amount |
+----+----------+--------+
|  3 | ZhangSan |    100 |
|  4 | LiSi     |    100 |
+----+----------+--------+
2 rows in set (0.00 sec)

5.在A终端更新数据

https://www.cndba.cn/hbhe0316/article/22636

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set amount=999 where name='ZhangSan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

6.在B终端更新数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set amount=999 where name='LiSi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account set amount=999 where name='ZhangSan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+---------------+
| lock_id         | lock_trx_id | lock_mode | lock_type | lock_table        | lock_index | lock_space | lock_page | lock_rec | lock_data     |
+-----------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+---------------+
| 19020570:88:4:2 | 19020570    | X,GAP     | RECORD    | `mysql`.`account` | idx_name   |         88 |         4 |        2 | 'ZhangSan', 3 |
| 19020571:88:4:2 | 19020571    | X         | RECORD    | `mysql`.`account` | idx_name   |         88 |         4 |        2 | 'ZhangSan', 3 |
+-----------------+-------------+-----------+-----------+-------------------+------------+------------+-----------+----------+---------------+
2 rows in set, 1 warning (0.00 sec)

7.在A终端执行命令插入数据，这个时候一直hung住https://www.cndba.cn/hbhe0316/article/22636

mysql> update account set amount=999 where name='LiSi';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

查看https://www.cndba.cn/hbhe0316/article/22636

mysql> show engine innodb status/G
Status: 
=====================================
2021-11-28 11:59:39 0x7f46aa03e700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 39 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 7 srv_active, 0 srv_shutdown, 916 srv_idle
srv_master_thread log flush and writes: 923
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 48
OS WAIT ARRAY INFO: signal count 47
RW-shared spins 0, rounds 42, OS waits 21
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 42.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-28 11:48:44 0x7f46aa03e700
*** (1) TRANSACTION:
TRANSACTION 19021580, ACTIVE 44 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 2, OS thread handle 139941477091072, query id 90 localhost root updating
update account set amount=999 where name='LiSi'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 88 page no 4 n bits 80 index idx_name of table `mysql`.`account` trx id 19021580 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 4c695369; asc LiSi;;
 1: len 4; hex 80000004; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 19021581, ACTIVE 26 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 139941476820736, query id 91 localhost root updating
update account set amount=999 where name='ZhangSan'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 88 page no 4 n bits 80 index idx_name of table `mysql`.`account` trx id 19021581 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 4c695369; asc LiSi;;
 1: len 4; hex 80000004; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 88 page no 4 n bits 80 index idx_name of table `mysql`.`account` trx id 19021581 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 5a68616e6753616e; asc ZhangSan;;
 1: len 4; hex 80000003; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 19021584
Purge done for trx's n:o < 19021584 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421424435160912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------

版权声明：本文为博主原创文章，未经博主允许不得转载。

MYSQL