当前位置:主页 > 数据库 > MySQL >

mysql死锁几种情况的测试_MySQL

时间:2019-11-09 | 栏目: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 OUTPUTPer second averages calculated from the last 18 secondsBACKGROUND THREAD

srv_master_thread loops: 9 srv_active, 0 srv_shutdown, 191469 srv_idle

srv_master_thread log flush and writes: 191478SEMAPHORES

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-exclLATEST 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

您可能感兴趣的文章:

相关文章