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.

Bouleversement

Do you use InnoDB for row table locking? – Overestimate

Answer 1

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.

Spindle

One problem is that in between marking it as "already processed" and unlocking then locking for actual processing, the process may fail, and we now have a row that is marked "already processed", but nothing actually processed it. – Alexis

@Alexis Aye, this gets very complex when various failure modes are taken into account. To solve the particular problem you mention, the row could be initially marked "processing" with a timestamp, and another process could come along and unmark any rows that have been marked "processing" for too long. – Spindle

Answer 2

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.

Haig

This is now also available in MariaDB 10.6+ – Farris

MySQL ^8 now supports this – Acaudal

Answer 3

MySQL does not have this feature. For anyone searching for this topic in general, some RDBMS have better/smarter locking features than others.

For developers constrained to MySQL, the best approach is to add a column (or use an existing, e.g., status column) that can be set to "locked" or "in progress" or similar, execute a SELECT ID, * ... WHERE IN_PROGRESS != 1 FOR UPDATE; to get the row ID you want to lock, issue UPDATE .. SET IN_PROGRESS = 1 WHERE ID = XX to unlock the records.

Using LOCK IN SHARE MODE is almost never the solution because while it'll let you read the old value, but the old value is in the process of being updated so unless you are performing a non-atomic task, there's no point in even looking at that record.

Better* RDBMS recognize this pattern (select one row to work on and lock it, work on it, unlock it) and provide a smarter approach that lets you only search unlocked records. For example, PostgreSQL 9.5+ provide SELECT ... SKIP LOCKED which only selects from within the unlocked subset of rows matching the query. That lets you obtain an exclusive lock on a row, service that record to completion, then update & unlock the record in question without having to block other threads/consumers from being able to work independent of yourself.

*Here "better" means from the perspective of atomic updates, multi-consumer architecture, etc. and not necessarily "better designed" or "overall better." Not trying to start a flamewar here.

Triphthong

Answer 4

As per http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

The solution is to perform the SELECT in a locking mode using LOCK IN SHARE MODE:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

Percale

In my case lock in share mode returns all 3 rows(see my 2nd query),but i need only 2 rows which are not locked.locked row should not be return; – Bouleversement

have you think about filtering result based on ID which you retrieve in your first call? You may find more info in bugs.mysql.com/bug.php?id=49763 – Emory