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>
MySQL thread> delete fromemployeeswhere emp_no=10001
(1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space>employees.employees trx> 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; ascC";;
4: len 6; hex 47656f726769; asc Georgi;;
5: len 7; hex 466163656c6c6f; asc> 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>
MySQL thread> delete fromemployeeswhere emp_no=10001
(2) HOLDS THE LOCK(S):
RECORD LOCKS space>employees.employees trx> 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; ascC";;
4: len 6; hex 47656f726769; asc Georgi;;
5: len 7; hex 466163656c6c6f; asc> 6: len 1; hex 01; asc;;
7: len 3; hex 8f84da; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space>employees.employees trx> 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; ascC";;
4: len 6; hex 47656f726769; asc Georgi;;
5: len 7; hex 466163656c6c6f; asc> 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 employeeswhere emp_no=10001 lock in share mode;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date|
+--------+------------+------------+-----------+--------+------------+
|10001 | 1953-09-02 | Georgi |> +--------+------------+------------+-----------+--------+------------+
1 row in set (0.00 sec)
session2：
mysql> delete fromemployeeswhere emp_no=10001;-- 卡住
session1：
mysql> delete fromemployeeswhere 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就等待，这种循环等待出现，死锁就出现了。
页:
[1]