试验筹备

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 IXRECORD LOCKS space id 492 page no 3 n bits 72 index PRIMARY of table `test`.`c` trx id 1226827 lock_mode XRecord 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 IXRECORD LOCKS space id 493 page no 3 n bits 72 index idx_a of table `test`.`c` trx id 1226844 lock_mode XRecord 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 IXRECORD LOCKS space id 494 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226873 lock_mode XRecord 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 gapRecord 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 IXRECORD 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 gapRecord 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 IXRECORD 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 gapRecord 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 IXRECORD LOCKS space id 484 page no 4 n bits 72 index idx_a of table `test`.`c` trx id 1226584 lock_mode XRecord 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 gapRecord 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 recRecord 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 IXRECORD 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 gapRecord 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 IXRECORD 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 gapRecord 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 IXRECORD 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 gapRecord 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 IXRECORD 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 gapRecord 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 IXRECORD 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 gapRecord 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 IXRECORD 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 gapRecord 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 gapRecord 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]