SELECT ... LIMIT ... FOR UPDATE behavior in MySQL 5.7

MySQL 5.7 using InnoDB.

Simple test schema:

create table test(
    id int primary key,
    name varchar(8)
);

Insert 50 rows:

insert into test values (1, 'Test 1'), (2, 'Test 2') .... till 50 rows

I want to SELECT ... LIMIT OFST, LMT ... FOR UPDATE so I can lock LMT rows.

Test 1:

Transaction 1:

begin 
    select * from test limit 0, 1 for update; (1)
    sleep(10); -- locking for 10 seconds for sake of testing
commit

Returned rows from (1):

id|name  |
--+------+
 1|Test 1|

Transaction 2:

begin 
    select * from teste where id = 2 for update; (1)
commit

Transaction 2 returns immediately, without having to wait 10 seconds for Transaction 1 to commit. Ok, expected.

Test 2:

Transaction 1:

begin 
    select * from test limit 0, 1 for update;
    sleep(10);
commit;

Returned rows from (1):

id|name  |
--+------+
 1|Test 1|

Transaction 2:

begin 
    select * from test limit 1, 1 for update; (1)
commit;

Returned rows from (1):

id|name  |
--+------+
 2|Test 2|

Even tho Transaction 2 returned a different result set, it had to wait 10 seconds for Transaction 1 to commit. Why does that happen? Is there any workaround to it? If it were MySQL 8.0+, a simple SKIP LOCKED would resolve my scenario.

I'm working on a system need to select vouchers from a pool of codes that live in a table so it can sell them. This is a legacy system.

The system does the following query:

select
    *
from
    vouchers v
where
    v.status = 1
limit
    0, ? -- limit inferred by programming language (it can be 1 to 50)
for update

The problem is that, when there is a high throughput, the application starts to collapse because of all the transactions waiting for others to commit (due to the locks). How to avoid this?

Solution

MySQL manual on innodb locks set by sql statements has the key points that describe the behaviour you experience.

Row level locks are in fact index record locks.
Locking reads lock all rows they encounter while searching for the rows to retrieve, even if the row is later excluded from the resultset.
If there is a suitable unique index to determine exactly which row(s) need locking, only those rows get locked.
If there is no suitable index to use for a locking read, then the statement must scan the entire table, effectively locking all rows.

Based on the above rules, the 1st test's 1st transaction starts a table scan locking rows probably in their physical order. The limit part stops the locking after the first record.

This is why the 2nd transaction can return immediately, as it uses the PK to retrieve and thus lock the row with id=2.

However, the 2nd transaction of the 2nd test blocks as in the absence of an index, MySQL has to start reading the entire table from the first row as it cannot determine the second one without scanning the first. It tries to lock the first row, but has to wait until the 1st transaction commits.