解決MySQL死鎖的方法

下面一起來了解下解決MySQL死鎖的方法，相信大家看完肯定會受益匪淺，文字在精不在多，希望解決MySQL死鎖的方法這篇短內容是你想要的。 

一、查看MySQL死鎖

MySQL數據庫會自己解決死鎖，kill掉一個session的事務，讓另外一個session的事務可以執行

 SQL>SHOW ENGINE INNODB STATUS\G

    LATEST DETECTED DEADLOCK

2018-02-12 15:42:06 0x7f6bd43df700
(1) TRANSACTION:
TRANSACTION 3368, ACTIVE 16717 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 35, OS thread handle 140101230081792, query id 297 localhost root updating
delete from  employees  where emp_no=10001
(1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 4 n bits 408 index PRIMARY of table employees.employees trx id 3368 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 80002711; asc   ' ;;
1: len 6; hex 000000000b20; asc       ;;
2: len 7; hex be0000013a0110; asc     :  ;;
3: len 3; hex 8f4322; asc  C";;
4: len 6; hex 47656f726769; asc Georgi;;
5: len 7; hex 466163656c6c6f; asc Facello;;
6: len 1; hex 01; asc  ;;
7: len 3; hex 8f84da; asc    ;;

(2) TRANSACTION:
TRANSACTION 3374, ACTIVE 79 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 36, OS thread handle 140101099058944, query id 298 localhost root updating
delete from  employees  where emp_no=10001
(2) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 4 n bits 408 index PRIMARY of table employees.employees trx id 3374 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 80002711; asc   ' ;;
1: len 6; hex 000000000b20; asc       ;;
2: len 7; hex be0000013a0110; asc     :  ;;
3: len 3; hex 8f4322; asc  C";;
4: len 6; hex 47656f726769; asc Georgi;;
5: len 7; hex 466163656c6c6f; asc Facello;;
6: len 1; hex 01; asc  ;;
7: len 3; hex 8f84da; asc    ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 4 n bits 408 index PRIMARY of table employees.employees trx id 3374 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
0: len 4; hex 80002711; asc   ' ;;
1: len 6; hex 000000000b20; asc       ;;
2: len 7; hex be0000013a0110; asc     :  ;;
3: len 3; hex 8f4322; asc  C";;
4: len 6; hex 47656f726769; asc Georgi;;
5: len 7; hex 466163656c6c6f; asc Facello;;
6: len 1; hex 01; asc  ;;
7: len 3; hex 8f84da; asc    ;;

*** WE ROLL BACK TRANSACTION (2)

注意：死鎖不記錄在錯誤日志中，只能通過 SHOW ENGINE INNODB STATUS\G查看，而且 SHOW ENGINE INNODB STATUS\G只會記錄上一次的死鎖，如果要看上上次的則看不到

死鎖模擬

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

mysql> select * from employees  where emp_no=10001 lock in share mode;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)

session2：

mysql> delete from  employees  where emp_no=10001;  -- 卡住

session1：

mysql> delete from  employees  where emp_no=10001;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

session2：

上面卡住的語句已經被執行

Query OK, 1 row affected (5.76 sec)

死鎖分析：
session1持有s鎖，session2執行刪除操作請求x，但s、x互斥，session2進入請求隊列等待（等待session1釋放s鎖），session1又在請求x鎖，這個時候隊列中session2在排隊，還輪不上session1，session1就等待，這種循環等待出現，死鎖就出現了。

看完解決MySQL死鎖的方法這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業信息，可以關注我們的行業資訊欄目。