MySQL exclusive lock encountered a problem little note

MySQL version: 5.5.34

When you use Select for Update to lock, you encounter a gap lock that locks the entire table (it should have been locked only in part of the range row).

Specific example:

delivery_id	Createtime
1	100
2	101
3	102
4	103

Session1:

Select delivery_id from table where createtime>102 for update;

Session2:

Select delivery_id from table where createtime<102 for update; (You need to wait for the lock to be released at this time)

The Createtime field is indexed.

There is no need to wait for a lock. I have tried before, but the environment may be different (at that time the MySQL version and the specific configuration environment may differ).

Later on the internet said to increase the amount of data to try, and then I will increase the data to 5000 lines or not. The problem is that the createtime in my generated records is too low, only one is 100,101,102 and the other is 103. The data is then regenerated to generate the createtime randomly. Try again, it's normal at this time.

Some people say that the data is small, MySQL execution plan will choose a full table scan, but I use explain view is useful to the index.

When the first data volume increases to 5000, the estimate is also an issue for the execution plan selection.

MySQL exclusive lock encountered a problem little note