mysql死锁(dead lock)与锁等待(lock wait)的出现解决
目录
- 死锁（dead lock）
- 前台报错
- 事后追查
- innodb status
- error.log
- 锁等待（lock wait）
- 1个参数
- innodb_lock_wait_timeout
- 怎么处理
- 级联锁或大量锁
很多人都分不清死锁和锁等待的区别，也有不同IT口的人叫法的差异。在运维侧：
死锁最明显的特征是会自动解开，是需要我们去事后解决逻辑缺陷。
锁等待则是业务卡住了（一般是某个大事务还在执行，或有事务没提交），需要杀掉持有锁的进城让业务正常进行
做几个实验详细演示一下。
表结构及数据情况
mysql> desc ttt; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | x | int(11) | NO | PRI | NULL | | | y | datetime | YES | | NULL | | | z | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> select * from ttt; +---+---------------------+------+ | x | y | z | +---+---------------------+------+ | 1 | 2023-05-30 23:50:13 | 123 | | 2 | 2023-04-26 17:58:18 | av3 | | 3 | 2023-05-30 22:52:35 | at | | 4 | 2023-04-26 17:58:29 | attt | | 5 | 2023-05-30 22:52:55 | zxz | +---+---------------------+------+ 5 rows in set (0.00 sec)
死锁（dead lock）
上文说了，死锁会自动解除，这里主要展示一下怎么追查。这里就不演示具体执行顺序了
前台报错
session1，这个先持有
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update ttt set y=now() where x=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update ttt set y=now() where x=3; Query OK, 1 row affected (7.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>
session2，这个来跳出错误
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update ttt set y=now() where x=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update ttt set y=now() where x=1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction mysql>
事后追查
innodb status的last dead lock只会记录上一次，建议是使用innodb_print_all_deadlocks参数，将记录打印到error.log中，该参数默认为off，可以动态修改
mysql> show variables like '%innodb_print_all_deadlocks%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_print_all_deadlocks | OFF | +----------------------------+-------+ 1 row in set (0.00 sec) mysql> set global innodb_print_all_deadlocks=on; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%innodb_print_all_deadlocks%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | innodb_print_all_deadlocks | ON | +----------------------------+-------+ 1 row in set (0.00 sec)
innodb status
show engine innodb status; ********* ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2023-05-30 22:42:09 0x7f9fd41ba700 *** (1) TRANSACTION: TRANSACTION 5454, ACTIVE 26 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 18, OS thread handle 140324434298624, query id 440 localhost root updating update ttt set y=now() where x=3 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `ddd`.`ttt` trx id 5454 lock_mode X locks rec but not gap waiting Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000000154f; asc O;; 2: len 7; hex 390000014e0110; asc 9 N ;; 3: len 5; hex 99b03d6a76; asc =jv;; 4: len 2; hex 6174; asc at;; *** (2) TRANSACTION: TRANSACTION 5455, ACTIVE 15 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 20, OS thread handle 140324435109632, query id 441 localhost root updating update ttt set y=now() where x=1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `ddd`.`ttt` trx id 5455 lock_mode X locks rec but not gap Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000000154f; asc O;; 2: len 7; hex 390000014e0110; asc 9 N ;; 3: len 5; hex 99b03d6a76; asc =jv;; 4: len 2; hex 6174; asc at;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `ddd`.`ttt` trx id 5455 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 00000000154e; asc 编程客栈N;; 2: len 7; hex 380000014d0110; asc 8 M ;; 3: len 5; hex 99b03d6a6b; asc =jk;; 4: len 3; hex 313233; asc 123;; *** WE ROLL BACK TRANSACTION (2) ********
error.log
[root@mysql57-1 data]# tail -100f mysql57-1.err 2023-05-25T07:42:37.677851Z 14 [Note] Access denied for user 'root'@'localhost' (using password: YES) 2023-05-25T07:44:15.360390Z 15 [Note] Access denied for user 'roo'@'localhost' (using password: YES) 2023-05-25T07:44:28.477560Z 16 [Note] Access denied for user 'roo'@'localhost' (using password: YES) 2023-05-30T14:53:00.101403Z 22 [Note] InnoDB: Transactions deadlock detected, dumping detailed information. 2023-05-30T14:53:00.101475Z 22 [Note] InnoDB: *** (1) TRANSACTION: TRANSACTION 5458, ACTIVE 25 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 21, OS thread handle 140324434298624, query id 473 localhost root updating update ttt set y=now() where x=5 2023-05-30T14www.devze.com:53:00.101516Z android22 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `ddd`.`ttt` trx id 5458 lock_mode X locks rec but not gap waiting Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000001553; asc S;; 2: len 7; hex 3b00000130036d; asc ; 0 m;; 3: len 5; hex 99b03d6d32; asc =m2;; 4: len 3; hex 7a787a; asc zxz;; 2023-05-30T14:53:00.101718Z 22 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 5459, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 22, OS thread handle 140324435109632, query id 474 localhost root updating update ttt set y=now() where x=3 2023-05-30T14:53:00.101748Z 22 [Note] InnoDB: *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `ddd`.`ttt` trx id 5459 lock_mode X locks rec but not gap Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000001553; asc S;; 2: len 7; hex 3b00000130036d; asc ; 0 m;; 3: len 5; hex 99b03d6d32; asc =m2;; 4: len 3; hex 7a787a; asc zxz;; 2023-05-30T14:53:00.101912Z 22 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 23 page no 3 n bits 80 index PRIMARY of table `ddd`.`ttt` trx id 5459 lock_mode X locks rec but not gap waiting Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 000000001552; asc R;; 2: len 7; hex 3a0000012e03d1; asc : . ;; 3: len 5; hex 99b03d6d23; asc =m#;; 4: len 2; hex 6174; asc at;; 2023-05-30T14:53:00.102084Z 22 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)
锁等待（lock wait）
1个参数
innodb_lock_wait_timeout
行锁等待的时间，如果超过这个时间，session2（后发起那个）会自动跳出
session1
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update ttt set y=now() where x=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
session2
mysql> update ttt set y=now() where x=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
怎么处理
省略构造锁
通过information_schema.innodb_lock_waits视图获得锁等待的关系
mysql> select * from information_schema.innodb_lock_waits; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 5467 | 5467:23:3:7 | 5466 | 5466:23:3:7 | +-------------------+-------------------+-----------------+------------------+ 1 row in set, 1 warning (0.00 sec)
再结合information_schema.innodb_trx视图得到语句和线程ID
这里提供一个sql
SELECT r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query,
b.trx_autocommit_non_locking
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON
b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON
r.trx_id = w.requesting_trx_id;
mysql> SELECT r.trx_id waiting_trx_id,
-> r.trx_mysql_thread_id waiting_thread,
-> r.trx_query waiting_query,
-> b.trx_id blocking_trx_id,
-> b.trx_mysql_thread_id blocking_thread,
-> b.trx_query blocking_query,
-> b.trx_autocommit_non_locking
-> FROM information_schema.innodb_lock_waits w
-> INNER JOIN information_schema.innodb_trx b ON
-> b.trx_id = w.blocking_trx_id
-> INNER JOIN information_schema.innodb_trx r ON
-> r.trx_id = w.requesting_trx_id;
+----------------+----------------+----------------------------------+-----------------+-----------------+----------------+----------------------------+
| waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query | trx_autocommit_non_locking |
+----------------+----------------+----------------------------------+-----------------+-----------------+----------------+----------------------------+
| 5470 | 22 | update ttt set y=now() where x=1 | 5466 | 26 | NULL | 0 |
+----------------+----------------+----------------------------------+-----------------+-----------------+----------------+----------------------------+
1 row in set, 1 warning (0.00 sec)
再对blocking thread的状态进行确认，然后kill
mysql> select * from information_schema.PROCESSLIST where id=26; +----+------+-----------+------+---------+------+-------+------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----+------+-----------+------+---------+------+-------+------+ | 26 | root | localhost | ddd | Sleep | 1221 | | NULL | +----+------+-----------+------+---------+------+-------+------+ 1 row in set (0.00 sec)
杀线程
mysql> kill 26; Query OK, 0 rows affected (0.00 sec)
级联锁或大量锁
这里构造一个多个争用的情况。
session1，线程id为27，这个先发起
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update ttt set y=now() where x=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
session，线程id为22，第二个发起
mysql> update ttt set y=now() where x=1;
session3，线程id为28，最后一个发起
mysql> update ttt set y=now();
锁关系情况
mysql> select * from information_schema.innodb_lock_waits; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blockinhttp://www.devze.comg_lock_id | +-------------------+-------------------+-----------------+------------------+ | 5479 | 5479:23:3:7 | 5478 | 5478:23:3:7 | | 5479 | 5479:23:3:7 | 5473 | 5473:23:3:7 | | 5478 | 5478:23:3:7 | 5473 | 5473:23:3:7 | +-------------------+-------------------+-----------------+------------------+ 3 rows in set, 1 warning (0.01 sec)
查询sql的展示情况
mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query, b.trx_autocommit_non_locking FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id; +----------------+----------------+----------------------------------+-----------------+-----------------+----------------------------------+----------------------------+ | waiting_trx_id | waiting_thread | waiting_query | blocking_trx_id | blocking_thread | blocking_query | trx_autocommit_non_locking | +----------------+----------------+----------------------------------+-----------------+-----------------+----------------------------------+----------------------------+ | 5479 | 28 | update ttt set y=now() | 5478 | 22 | update ttt set y=now() where x=1 | 0 | | 5479 | 28 | update ttt set y=now() | 5473 | 27 | NULL | 0 | | 5478 | 22 | update ttt set y=now() where x=1 | 5473 | 27 | NULL | 0 | +----------------+----------------+----------------------------------+-----------------+-----------------+----------------------------------+----------------------------+
这个时候一般的做法是先找到最多的那个blocking_thread
确认他的进程状态后来考虑是否杀
如果他是活动的thread，在干活，就可以删掉释放资源。
如果他是非活动，就要看他这个thread的blocking_thread，找到活动持有资源的，来杀掉。
当然也有执行了完了不提交的情况，innodb_trx的trx_autocommit_non_locking列的值就是为了标记是否是提交了。0表示没有提交。
到此这篇关于mysql死锁(dead lock)与锁等待(lock wait)的出现解决的文章就介绍到这了,更多相关mysql死锁与锁等待内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章php希望大家以后多多支持编程客栈(www.devze.com)！
加载中,请稍侯......
精彩评论