Mysql deadlock test _ MySQL

A holds the s lock, B executes the Delete request x, but sx is mutually exclusive. B enters the Request queue and waits. a is requesting the x lock. At this time, B is queuing in the queue, if the round does not reach a, a will wait. This kind of loop wait will occur, and the deadlock will occur. 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 |
+ -- + --- +
| 5 | 1234 |
+ -- + --- +
1 row in set (0.01 sec)
SessionB:
Test & gt; delete from tt where id_test = 1234;
Stuck
SessionA:
Test & gt; delete from tt where id_test = 1234;
Query OK, 1 row affected (0.00 sec)
Sessionb is killed, and a executes
View lock Information
(None)> show engine innodb status \ G
*****************1. row*****************
Type: InnoDB
Name:

Status: 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: 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-exclLATEST DETECTED DEADLOCK

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 73757021356d756d; 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 holds the s lock, B executes the Delete request x, but sx is mutually exclusive. B enters the Request queue and waits. a is requesting the x lock. At this time, B is queuing in the queue, if the round does not reach a, a will wait. This kind of loop wait will occur, and the deadlock will occur.

The following are common scenarios in oracle:
SessionA
Test> select * from tt;
+ -- + --- +
| Id | id_test |
+ -- + --- +
| 1 | 1000 |
| 3 | 1000 |
| 5 | 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)
First, delete id = 1 in 7 and ttid = 1 in sessionb.

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;
Waiting to get stuck
SessionB;
Delete from t7 where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Deadlock

LATEST DETECTED DEADLOCK

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 201710000115a; 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 201710000115a; 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 201710000115b; asc [;;
2: len 7; hex 39000001e00827; asc 9 ';;
3: len 2; hex 6161; asc aa ;;

*We roll back transaction (2)
The deadlock is also caused by mutual requests to the other party for resources not released.

The above is the content of test_mysql in several cases of mysql deadlock. For more information, please follow the PHP Chinese Network (www.php1.cn )!