Select only unlocked mysql rows

Question

I locked one row in one transaction by running the following query

START TRANSACTION; SELECT id FROM children WHERE id=100 FOR UPDATE; 

And in another transaction, I have the request below

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

This means that the timeout for waiting for error blocking has been exceeded.

Here 100 is already blocked (in the first transaction) But identifiers 98.99 are not blocked. Is there any way to return records from 98.99 if only 100 is blocked by a row in the above query. The result should be lower

Id

===

98

99

===

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

Answer 1

MySQL cannot ignore locked rows in SELECT. You will need to find another way to set the line to the side as "already processed".

The easiest way is to briefly lock the line in the first request to mark it as "already processed", then unlock it and block it again for the rest of the processing - the second request will wait for a short "marker" "to complete, and you can add an explicit WHERE clause to ignore already marked lines.If you do not want to rely on the first successful operation, you may need to add a little more complexity with timestamps and thus clear after these unsuccessful operations.

Answer 2

MySQL does not have this feature. For those who are looking for this topic in general, some RDBMSs have better / clever locking features than others.

For developers limited to MySQL, the best approach is to add a column (or use an existing state column, for example) that can be set to "locked" or "in progress" or similar, do SELECT ID, * ... WHERE IN_PROGRESS != 1 FOR UPDATE; get the id of the row you want to block, enter UPDATE .. SET IN_PROGRESS = 1 WHERE ID = XX to unlock the records.

Using LOCK IN SHARE MODE almost never a solution, because as long as it allows you to read the old value, but the old value is in the process of updating, so if you are performing a non-atomic task, there is no point in even looking at this record.

Better * RDBMS recognizes this pattern (select one row to work and lock it, work on it, unlock it) and provide a more reasonable approach that allows you to search only unlocked records. For example, PostgreSQL 9.5+ provides SELECT ... SKIP LOCKED , which selects only from an unlocked subset of rows matching the query. This allows you to get an exclusive lock on the line, a service that is recorded until completion, and then update and unlock the corresponding record without blocking other threads / consumers from the opportunity to work independently.

* “Better” here means in terms of atomic updates, multi-user architecture, etc. and not necessarily “better designed” or “better.” Do not try to run a flamvar there.

Answer 3

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

The solution is to perform SELECT in lock mode using LOCK IN SHARE MODE :

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