mysql死锁几种情况的测试_MySQL

sessionA:
test>begin
-> ;
Query OK, 0 rows affected (0.00 sec)

test>select * from tt where id_test=1234 lock in share mode;
+—-+———+
| id | id_test |
+—-+———+
| 4 | 1234 |
+—-+———+
1 row in set (0.01 sec)
sessionB:
test>delete from tt where id_test=1234;
卡住
sessionA:
test>delete from tt where id_test=1234;
Query OK, 1 row affected (0.00 sec)
sessionb被杀掉，a执行
查看锁信息
(none)>show engine innodb status\G
***************** 1. row *****************
Type: InnoDB
Name:

Status:

2016-05-09 16:26:27 7f8ee2123700 INNODB MONITOR OUTPUT

Per second averages calculated from the last 18 seconds

BACKGROUND THREAD

srv_master_thread loops: 9 srv_active, 0 srv_shutdown, 191469 srv_idle

srv_master_thread log flush and writes: 191478

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 23
OS WAIT ARRAY INFO: signal count 23
Mutex spin waits 16, rounds 210, OS waits 7
RW-shared spins 16, rounds 480, OS waits 16
RW-excl spins 0, rounds 0, OS waits 0

Spin rounds per wait: 13.12 mutex, 30.00 RW-shared, 0.00 RW-excl

LATEST DETECTED DEADLOCK

2016-05-09 15:56:30 7f8ee2154700
* (1) TRANSACTION:
TRANSACTION 4396, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 4, OS thread handle 0x7f8ee2123700, query id 52 192.168.90.109 myadmin updating
delete from tt where id_test=1234
* (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 36 page no 3 n bits 88 index PRIMARY of table test.tt trx id 4396 lock_mode X waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000000001117; asc ;;
2: len 7; hex 13000001460476; asc F v;;
3: len 4; hex 800003e8; asc ;;

* (2) TRANSACTION:
TRANSACTION 4395, ACTIVE 30 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 7 row lock(s)
MySQL thread id 3, OS thread handle 0x7f8ee2154700, query id 53 192.168.90.109 myadmin updating
delete from tt where id_test=1234
* (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 36 page no 3 n bits 88 index PRIMARY of table test.tt trx id 4395 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000000001117; asc ;;
2: len 7; hex 13000001460476; asc F v;;
3: len 4; hex 800003e8; asc ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000000002; asc ;;
1: len 6; hex 000000001117; asc ;;
2: len 7; hex 1300000146049b; asc F ;;
3: len 4; hex 800003e8; asc ;;

Record lock, heap no 12 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000000003; asc ;;
1: len 6; hex 000000001117; asc ;;
2: len 7; hex 130000014604c0; asc F ;;
3: len 4; hex 800003e8; asc ;;

Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000000004; asc ;;
1: len 6; hex 000000001129; asc );;
2: len 7; hex 1d000001d202df; asc ;;
3: len 4; hex 800004d2; asc ;;

Record lock, heap no 15 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000000006; asc ;;
1: len 6; hex 000000001117; asc ;;
2: len 7; hex 1300000146052f; asc F /;;
3: len 4; hex 800003e8; asc ;;

* (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 36 page no 3 n bits 88 index PRIMARY of table test.tt trx id 4395 lock_mode X waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000000001117; asc ;;
2: len 7; hex 13000001460476; asc F v;;
3: len 4; hex 800003e8; asc ;;

* WE ROLL BACK TRANSACTION (1)

a持有s锁，b执行删除操作请求x，但sx互斥，b进入请求队列等待，a在请求x锁，这个时候队列中b在排队，还轮不上a，a就等待，这种循环等待出现，死锁就出现了。

下面是oracle中经常出现的场景
sessionA
test>select * from tt;
+—-+———+
| id | id_test |
+—-+———+
| 1 | 1000 |
| 3 | 1000 |
| 4 | 1234 |
| 6 | 1000 |
+—-+———+
4 rows in set (0.00 sec)

select * from t7;
+—-+——+
| id | name |
+—-+——+
| 1 | aa |
+—-+——+
1 row in set (0.01 sec)

.test>begin;
Query OK, 0 rows affected (0.00 sec)
先删除7中的id=1,sessionb中删除ttid=1

delete from t7 where id=1;
Query OK, 1 row affected (0.01 sec)
session B
delete from tt where id=1;
Query OK, 1 row affected (0.00 sec)
sessionA:
delete from tt where id=1;
等待卡住
sessionB;
delete from t7 where id=1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
产生死锁

LATEST DETECTED DEADLOCK

2016-05-09 16:47:14 7f8ee2154700
* (1) TRANSACTION:
TRANSACTION 4443, ACTIVE 36 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 0x7f8ee20f2700, query id 133 192.168.90.109 myadmin updating
delete from tt where id=1
* (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 36 page no 3 n bits 88 index PRIMARY of table test.tt trx id 4443 lock_mode X locks rec but not gap waiting
Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000000115a; asc Z;;
2: len 7; hex 3800000151037d; asc 8 Q };;
3: len 4; hex 800003e8; asc ;;

* (2) TRANSACTION:
TRANSACTION 4442, ACTIVE 52 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 12, OS thread handle 0x7f8ee2154700, query id 134 192.168.90.109 myadmin updating
delete from t7 where id=1
* (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 36 page no 3 n bits 88 index PRIMARY of table test.tt trx id 4442 lock_mode X locks rec but not gap
Record lock, heap no 10 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 00000000115a; asc Z;;
2: len 7; hex 3800000151037d; asc 8 Q };;
3: len 4; hex 800003e8; asc ;;

* (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29 page no 3 n bits 72 index PRIMARY of table test.t7 trx id 4442 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000115b; asc [;;
2: len 7; hex 39000001e00827; asc 9 ‘;;
3: len 2; hex 6161; asc aa;;

* WE ROLL BACK TRANSACTION (2)
看到这种也是因为互相请求对方不释放的资源导致的死锁

以上就是mysql死锁几种情况的测试_MySQL的内容，更多相关内容请关注PHP中文网（www.php.cn）！