mysql死鎖(dead lock)與鎖等待(lock wait)的出現(xiàn)解決

更新時間：2024年09月09日 10:23:55 作者：foxytale

死鎖和鎖等待是數(shù)據(jù)庫運(yùn)維中常見的問題,區(qū)別在于死鎖會自動解除,而鎖等待需要手動處理,本文就來介紹一下mysql死鎖(dead lock)與鎖等待(lock wait),感興趣的可以了解一下

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）

上文說了，死鎖會自動解除，這里主要展示一下怎么追查。這里就不演示具體執(zhí)行順序了

前臺報(bào)錯

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參數(shù)，將記錄打印到error.log中，該參數(shù)默認(rèn)為off，可以動態(tài)修改

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-30T14:53:00.101516Z 22 [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個參數(shù)

innodb_lock_wait_timeout

行鎖等待的時間，如果超過這個時間，session2（后發(fā)起那個）會自動跳出

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

怎么處理

省略構(gòu)造鎖

通過information_schema.innodb_lock_waits視圖獲得鎖等待的關(guān)系

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)

再結(jié)合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的狀態(tài)進(jìn)行確認(rèn)，然后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)

級聯(lián)鎖或大量鎖

這里構(gòu)造一個多個爭用的情況。

session1，線程id為27，這個先發(fā)起

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，第二個發(fā)起

mysql> update ttt set y=now() where x=1;

session3，線程id為28，最后一個發(fā)起

mysql> update ttt set y=now();

鎖關(guān)系情況

mysql> select * from information_schema.innodb_lock_waits;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_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

確認(rèn)他的進(jìn)程狀態(tài)后來考慮是否殺

如果他是活動的thread，在干活，就可以刪掉釋放資源。

如果他是非活動，就要看他這個thread的blocking_thread，找到活動持有資源的，來殺掉。

當(dāng)然也有執(zhí)行了完了不提交的情況，innodb_trx的trx_autocommit_non_locking列的值就是為了標(biāo)記是否是提交了。0表示沒有提交。

到此這篇關(guān)于mysql死鎖(dead lock)與鎖等待(lock wait)的出現(xiàn)解決的文章就介紹到這了,更多相關(guān)mysql死鎖與鎖等待內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: