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 outputper second averages calculated from the last 18 secondsbackground threadsrv_master_thread loops: 9 srv_active, 0 srv_shutdown, 191469 srv_idle
srv_master_thread log flush and writes: 191478semaphoresos 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-excllatest detected deadlock2016-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 deadlock2016-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的内容。