Mysql查询快捷键f8不生效_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)

看到这种也是因为互相请求对方不释放的资源导致的死锁