下面一起來了解下解決MySQL死鎖的方法，相信大家看完肯定會(huì)受益匪淺，文字在精不在多，希望解決MySQL死鎖的方法這篇短內(nèi)容是你想要的。
創(chuàng)新互聯(lián)公司是專業(yè)的新華網(wǎng)站建設(shè)公司，新華接單;提供成都做網(wǎng)站、成都網(wǎng)站制作、成都外貿(mào)網(wǎng)站建設(shè),網(wǎng)頁設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行新華網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來合作!
一、查看MySQL死鎖
MySQL數(shù)據(jù)庫(kù)會(huì)自己解決死鎖，kill掉一個(gè)session的事務(wù)，讓另外一個(gè)session的事務(wù)可以執(zhí)行 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)
注意：死鎖不記錄在錯(cuò)誤日志中，只能通過 SHOW ENGINE INNODB STATUS\G查看，而且 SHOW ENGINE INNODB STATUS\G只會(huì)記錄上一次的死鎖，如果要看上上次的則看不到
死鎖模擬
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：
上面卡住的語句已經(jīng)被執(zhí)行
Query OK, 1 row affected (5.76 sec)
死鎖分析：
session1持有s鎖，session2執(zhí)行刪除操作請(qǐng)求x，但s、x互斥，session2進(jìn)入請(qǐng)求隊(duì)列等待（等待session1釋放s鎖），session1又在請(qǐng)求x鎖，這個(gè)時(shí)候隊(duì)列中session2在排隊(duì)，還輪不上session1，session1就等待，這種循環(huán)等待出現(xiàn)，死鎖就出現(xiàn)了。
看完解決MySQL死鎖的方法這篇文章后，很多讀者朋友肯定會(huì)想要了解更多的相關(guān)內(nèi)容，如需獲取更多的行業(yè)信息，可以關(guān)注我們的行業(yè)資訊欄目。
重慶分公司
重慶分公司
