关于mysql:mysql-lock-实验

41次阅读

共计 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]

正文完
mysql
发表至： mysql
2023-04-25
 0
关于mysql:索引
一条SQL查询语句是如何执行的
关于mysql:给新手学习MySQL的建议
关于mysql:再见了MySQL性能被-MariaDB-吊打