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就等待，这种循环等待出现，死锁就出现了。