First of all, set the transaction isolation level set transaction_isolation="REPEATABLE - READ";
Create a test table and insert test data
The create table t_lock_1 (a int primary key);
Insert into t_lock_1 values (10), (11), (13), (20);
Open a transaction, query a>=11 data
The begin.
Select * from t_lock_1 where a>=11 for update;
In addition to create a new link to view the lock, check the locking range is 11, (11, 13), (13, 20], (20 + up), locked range is no problem of
3 the lock struct (s), the heap size of 1136, four row lock (s)
MySQL thread id 11, OS thread handle 139629588756224, query id 111 localhost root
The TABLE LOCK TABLE ` dev `. ` t_lock_1 ` TRX id 24377 LOCK mode IX
RECORD the 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 the lock, heap no 3 PHYSICAL Record: n_fields 3; Compact format. The info bits (0
Zero: len 4; Hex 8000000 b; Asc;;
1: len (6); Hex 000000005 f2c; Asc _,;;
2: len 7; Hex 8200000102011 d; Asc;;
RECORD the 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 the lock, heap the no 1 PHYSICAL Record: n_fields 1; Compact format. The info bits (0
Zero: len 8; Hex 73757072656 d756d; Asc supremum;;
Record the lock, heap no 4 PHYSICAL Record: n_fields 3; Compact format. The info bits (0
Zero: len 4; Hex 8000000 d; Asc;;
1: len (6); Hex 000000005 f2c; Asc _,;;
2: len 7; Hex 8200000102012 a; Asc *;;
Record the lock, heap no 5 PHYSICAL Record: n_fields 3; Compact format. The info bits (0
Zero: len 4; Hex 80000014; Asc;;
1: len (6); Hex 000000005 f2c; Asc _,;;
2: len 7; Hex 82000001020137; Asc 7;;
A transaction rollback, open a new transaction, the query a>
=13 data
The begin.
Select * from t_lock_1 where a>=13 for update;
According to the logic, just 13, this time should be locked in the interval (13, 20], (20 + up), but check the lock information, found that the full table is locked
2 lock struct (s), heap size 1136, 5 row lock (s)
MySQL thread id 11, OS thread handle 139629588756224, query id 115 localhost root
The TABLE LOCK TABLE ` dev `. ` t_lock_1 ` TRX id 24378 LOCK mode IX
RECORD the 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 the lock, heap the no 1 PHYSICAL Record: n_fields 1; Compact format. The info bits (0
Zero: len 8; Hex 73757072656 d756d; Asc supremum;;
Record the lock, heap no 2 PHYSICAL Record: n_fields 3; Compact format. The info bits (0
Zero: len 4; Hex 8000000 a; Asc;;
1: len (6); Hex 000000005 f2c; Asc _,;;
2: len 7; Hex 82000001020110; Asc;;
Record the lock, heap no 3 PHYSICAL Record: n_fields 3; Compact format. The info bits (0
Zero: len 4; Hex 8000000 b; Asc;;
1: len (6); Hex 000000005 f2c; Asc _,;;
2: len 7; Hex 8200000102011 d; Asc;;
Record the lock, heap no 4 PHYSICAL Record: n_fields 3; Compact format. The info bits (0
Zero: len 4; Hex 8000000 d; Asc;;
1: len (6); Hex 000000005 f2c; Asc _,;;
2: len 7; Hex 8200000102012 a; Asc *;;
Record the lock, heap no 5 PHYSICAL Record: n_fields 3; Compact format. The info bits (0
Zero: len 4; Hex 80000014; Asc;;
1: len (6); Hex 000000005 f2c; Asc _,;;
2: len 7; Hex 82000001020137; Asc 7;;
Roll back the transaction again and open a new transaction, the query a> 13 data
The begin.
Select * from t_lock_1 where a> 13 for update;
Look again at the lock information, found that the interval is locked (13, 20), (20 + up), the locked interval is also no problem
2 the lock struct (s), heap size 1136, 2 row lock (s)
MySQL thread id 11, OS thread handle 139629588756224, query id 119 localhost root
The TABLE LOCK TABLE ` dev `. ` t_lock_1 ` TRX id 24379 LOCK mode IX
RECORD the 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 the lock, heap the no 1 PHYSICAL Record: n_fields 1; Compact format. The info bits (0
Zero: len 8; Hex 73757072656 d756d; Asc supremum;;
Record the lock, heap no 5 PHYSICAL Record: n_fields 3; Compact format. The info bits (0
Zero: len 4; Hex 80000014; Asc;;
1: len (6); Hex 000000005 f2c; Asc _,;;
2: len 7; Hex 82000001020137; Asc 7;;
Now the question is why query a>=13, innodb will full table lock, for help, to explain