Select only unlocked rows mysql

Question

I have locked one row in one transaction by following query

START TRANSACTION;

SELECT id FROM children WHERE id=100 FOR UPDATE;

And in another transaction i have a query as below

START TRANSACTION;

SELECT id FROM children WHERE id IN (98,99,100) FOR UPDATE;

It gives error lock wait timeout exceeded.

Here 100 is already locked (in first transaction ) But the ids 98,99 are not locked.Is there any possibility return records of 98,99 if only 100 is row locked in above query.So result should be as below

Id

===

98

99

===

Id 100 should be ignored because 100 is locked by a transaction.

Answer 1

Looks like SKIP LOCKED option mentioned in a previous answer is now available in MySQL. It does not wait to acquire a row lock and allows you to work with rows that are not currently locked.

From MySQL 8.0.0 Release Notes/Changes in MySQL 8.0.1:

InnoDB now supports NOWAIT and SKIP LOCKED options with SELECT ... FOR SHARE and SELECT ... FOR UPDATE locking read statements. NOWAIT causes the statement to return immediately if a requested row is locked by another transaction. SKIP LOCKED removes locked rows from the result set. See Locking Read Concurrency with NOWAIT and SKIP LOCKED.

Sample usage (complete example with outputs can be found in the link above):

START TRANSACTION;
SELECT * FROM tableName FOR UPDATE SKIP LOCKED;

Also, it might be good to include the warning in the Reference Manual here as well:

Queries that skip locked rows return an inconsistent view of the data. SKIP LOCKED is therefore not suitable for general transactional work. However, it may be used to avoid lock contention when multiple sessions access the same queue-like table.

Answer 2

MySQL does not have a way to ignore locked rows in a SELECT. You'll have to find a different way to set a row aside as "already processed".

The simplest way is to lock the row briefly in the first query just to mark it as "already processed", then unlock it and lock it again for the rest of the processing - the second query will wait for the short "marker" query to complete, and you can add an explicit WHERE condition to ignore already-marked rows. If you don't want to rely on the first operation being able to complete successfully, you may need to add a bit more complexity with timestamps and such to clean up after those failed operations.