Mysql的版本是8.0.23
首先設定事務隔離級別set transaction_isolation="REPEATABLE-READ";
創建測驗表，并插入測驗資料
create table t_lock_1 (a int primary key);
insert into t_lock_1 values(10),(11),(13),(20);
開啟一個事務，查詢a>=11的資料
begin;
select * from t_lock_1 where a>=11 for update;
另外新建一個鏈接查看鎖的情況，查看鎖定的區間是11,(11,13],(13,20],(20,+∞)，被鎖的區間是沒有問題的
3 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 11, OS thread handle 139629588756224, query id 111 localhost root
TABLE LOCK table `dev`.`t_lock_1` trx id 24377 lock mode IX
RECORD LOCKS space id 61 page no 4 n bits 72 index PRIMARY of table `dev`.`t_lock_1` trx id 24377 lock_mode X locks rec but not gap
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 000000005f2c; asc _,;;
2: len 7; hex 8200000102011d; asc ;;
RECORD LOCKS space id 61 page no 4 n bits 72 index PRIMARY of table `dev`.`t_lock_1` trx id 24377 lock_mode X
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 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000d; asc ;;
1: len 6; hex 000000005f2c; asc _,;;
2: len 7; hex 8200000102012a; 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 000000005f2c; asc _,;;
2: len 7; hex 82000001020137; asc 7;;
回滾上一個事務，開啟新事務，這次查詢a>=13的資料
begin;
select * from t_lock_1 where a>=13 for update;
按照剛才的邏輯，這次被鎖的區間應該是13,(13,20],(20,+∞)，但是查看鎖資訊時，發現全表都被鎖住了
2 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 11, OS thread handle 139629588756224, query id 115 localhost root
TABLE LOCK table `dev`.`t_lock_1` trx id 24378 lock mode IX
RECORD LOCKS space id 61 page no 4 n bits 72 index PRIMARY of table `dev`.`t_lock_1` trx id 24378 lock_mode X
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 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 000000005f2c; asc _,;;
2: len 7; hex 82000001020110; 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 000000005f2c; asc _,;;
2: len 7; hex 8200000102011d; 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 000000005f2c; asc _,;;
2: len 7; hex 8200000102012a; 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 000000005f2c; asc _,;;
2: len 7; hex 82000001020137; asc 7;;
再次回滾事務并開啟新事務，這次查詢a>13的資料
begin;
select * from t_lock_1 where a>13 for update;
再次查看鎖資訊，發現被鎖的區間是(13,20),(20,+∞)，這次的被鎖區間也是沒有問題的
2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 139629588756224, query id 119 localhost root
TABLE LOCK table `dev`.`t_lock_1` trx id 24379 lock mode IX
RECORD LOCKS space id 61 page no 4 n bits 72 index PRIMARY of table `dev`.`t_lock_1` trx id 24379 lock_mode X
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 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000014; asc ;;
1: len 6; hex 000000005f2c; asc _,;;
2: len 7; hex 82000001020137; asc 7;;
現在的疑問就是為什么查詢a>=13時，innodb會將全表都鎖住，求助各位大佬能給解釋一下
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/266092.html
標籤：MySQL
上一篇：oracle里怎么把字串最后一個逗號后面的內容替換掉
下一篇：mongodb容量預估