共计 7806 个字符,预计需要花费 20 分钟才能阅读完成。
试验筹备
CREATE TABLE `c` (`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `c` VALUES ('10');
INSERT INTO `c` VALUES ('11');
INSERT INTO `c` VALUES ('13');
INSERT INTO `c` VALUES ('20');
设置事务级别 反复读(repeatable-read)
set global tx_isolation = 'repeatable-read';
查问语句
select * from c where a <= 13 for update
primary key
TABLE LOCK table `test`.`c` trx id 1226827 lock mode IX
RECORD LOCKS space id 492 page no 3 n bits 72 index PRIMARY of table `test`.`c` trx id 1226827 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000012b815; asc ;;
2: len 7; hex e50000025b0110; asc [ ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 6; hex 00000012b815; asc ;;
2: len 7; hex e50000025b011f; asc [ ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 00000012b815; asc ;;
2: len 7; hex e50000025b012e; asc [ .;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 00000012b815; asc ;;
2: len 7; hex e50000025b013d; asc [=;;
(-∞,10]
(10,11]
(11,13]
(13,20]
unique key
TABLE LOCK table `test`.`c` trx id 1226844 lock mode IX
RECORD LOCKS space id 493 page no 3 n bits 72 index idx_a of table `test`.`c` trx id 1226844 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000012b815; asc ;;
2: len 7; hex e50000025b0110; asc [ ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 6; hex 00000012b815; asc ;;
2: len 7; hex e50000025b011f; asc [ ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 00000012b815; asc ;;
2: len 7; hex e50000025b012e; asc [ .;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 00000012b815; asc ;;
2: len 7; hex e50000025b013d; asc [=;;
(-∞,10]
(10,11]
(11,13]
(13,20]
index key
TABLE LOCK table `test`.`c` trx id 1226873 lock mode IX
RECORD LOCKS space id 494 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226873 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000043b; asc ;;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 6; hex 00000000043c; asc <;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 00000000043d; asc =;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 00000000043e; asc >;;
RECORD LOCKS space id 494 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`c` trx id 1226873 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 00000000043b; asc ;;;
1: len 6; hex 00000012b861; asc a;;
2: len 7; hex b8000002490110; asc I ;;
3: len 4; hex 8000000a; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 00000000043c; asc <;;
1: len 6; hex 00000012b861; asc a;;
2: len 7; hex b800000249011f; asc I ;;
3: len 4; hex 8000000b; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 00000000043d; asc =;;
1: len 6; hex 00000012b861; asc a;;
2: len 7; hex b800000249012e; asc I .;;
3: len 4; hex 8000000d; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 00000000043e; asc >;;
1: len 6; hex 00000012b861; asc a;;
2: len 7; hex b800000249013d; asc I =;;
3: len 4; hex 80000014; asc ;;
(-∞,10]
(10,11]
(11,13]
(13,20]
查问语句
select * from c where a = 13
primary key
TABLE LOCK table `test`.`c` trx id 1226898 lock mode IX
RECORD LOCKS space id 495 page no 3 n bits 72 index PRIMARY of table `test`.`c` trx id 1226898 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 00000012b861; asc a;;
2: len 7; hex b800000249012e; asc I .;;
[13]
unique key
TABLE LOCK table `test`.`c` trx id 1226915 lock mode IX
RECORD LOCKS space id 496 page no 3 n bits 72 index idx_a of table `test`.`c` trx id 1226915 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 00000012b861; asc a;;
2: len 7; hex b800000249012e; asc I .;;
[13]
index key
TABLE LOCK table `test`.`c` trx id 1226584 lock mode IX
RECORD LOCKS space id 484 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226584 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 000000000420; asc ;;
RECORD LOCKS space id 484 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`c` trx id 1226584 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000420; asc ;;
1: len 6; hex 00000012b73c; asc <;;
2: len 7; hex d800000159012e; asc Y .;;
3: len 4; hex 8000000d; asc ;;
RECORD LOCKS space id 484 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226584 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000000421; asc !;;
(11,13]
(13,20)
设置事务级别 读提交(READ-COMMITTED)
set global tx_isolation = 'READ-COMMITTED';
查问语句
select * from c where a <= 13 for update
primary key
TABLE LOCK table `test`.`c` trx id 1226611 lock mode IX
RECORD LOCKS space id 485 page no 3 n bits 72 index PRIMARY of table `test`.`c` trx id 1226611 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000012b73c; asc <;;
2: len 7; hex d8000001590110; asc Y ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 6; hex 00000012b73c; asc <;;
2: len 7; hex d800000159011f; asc Y ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 00000012b73c; asc <;;
2: len 7; hex d800000159012e; asc Y .;;
[10,11,13]
unique key
TABLE LOCK table `test`.`c` trx id 1226671 lock mode IX
RECORD LOCKS space id 487 page no 3 n bits 72 index idx_a of table `test`.`c` trx id 1226671 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000012b787; asc ;;
2: len 7; hex a6000001500110; asc P ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 6; hex 00000012b787; asc ;;
2: len 7; hex a600000150011f; asc P ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 00000012b787; asc ;;
2: len 7; hex a600000150012e; asc P .;;
[10,11,13]
index key
TABLE LOCK table `test`.`c` trx id 1226710 lock mode IX
RECORD LOCKS space id 488 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226710 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000042a; asc *;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000b; asc ;;
1: len 6; hex 00000000042b; asc +;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 00000000042c; asc ,;;
[10,11,13]
查问语句
select * from c where a = 13 for update
primary key
TABLE LOCK table `test`.`c` trx id 1226740 lock mode IX
RECORD LOCKS space id 489 page no 3 n bits 72 index PRIMARY of table `test`.`c` trx id 1226740 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 00000012b7ba; asc ;;
2: len 7; hex a80000011c012e; asc .;;
[13]
unique key
TABLE LOCK table `test`.`c` trx id 1226763 lock mode IX
RECORD LOCKS space id 490 page no 3 n bits 72 index idx_a of table `test`.`c` trx id 1226763 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 00000012b7ba; asc ;;
2: len 7; hex a80000011c012e; asc .;;
[13]
index key
TABLE LOCK table `test`.`c` trx id 1226797 lock mode IX
RECORD LOCKS space id 491 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226797 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 000000000433; asc 3;;
RECORD LOCKS space id 491 page no 3 n bits 72 index GEN_CLUST_INDEX of table `test`.`c` trx id 1226797 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000433; asc 3;;
1: len 6; hex 00000012b815; asc ;;
2: len 7; hex e50000025b012e; asc [ .;;
3: len 4; hex 8000000d; asc ;;
[13]
正文完