MySQL InnoDB "SELECT FOR UPDATE" - SKIP LOCKED equivalent

Question

Is there any way to skip "locked rows" when we make "SELECT FOR UPDATE" in MySQL with an InnoDB table?

E.g.: terminal t1

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from mytable ORDER BY id ASC limit 5 for update;
+-------+
| id    |
+-------+
|     1 |
|    15 |
| 30217 |
| 30218 |
| 30643 |
+-------+
5 rows in set (0.00 sec)

mysql>

At the same time, terminal t2:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from mytable where id>30643 order by id asc limit 2 for update;
+-------+
| id    |
+-------+
| 30939 |
| 31211 |
+-------+
2 rows in set (0.01 sec)

mysql> select id from mytable order by id asc limit 5 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

So if I launch a query forcing it to select other rows, it's fine.

But is there a way to skip the locked rows?

I guess this should be a redundant problem in the concurrent process, but I did not find any solution.

EDIT: In reality, my different concurrent processes are doing something apparently really simple:

take the first rows (which don't contain a specific flag - e.g.: "WHERE myflag_inUse!=1").
Once I get the result of my "select for update", I update the flag and commit the rows.

So I just want to select the rows which are not already locked and where myflag_inUse!=1...

The following link helps me to understand why I get the timeout, but not how to avoid it:

MySQL 'select for update' behaviour

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.5.46                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| version                 | 5.5.46-0ubuntu0.14.04.2 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | debian-linux-gnu        |
+-------------------------+-------------------------+
7 rows in set (0.00 sec)

Just don't use SELECT FOR UPDATE. Taking update locks for a long time is a bad idea and really only useful when using cursors (not a good idea either). You can't use it to emulate a checkin/checkout mechanism — Panagiotis Kanavos
@PanagiotisKanavos, I don't want to emulate this mechanism. I just want to select for update by skipping the locked rows. — Bast
@RickJames, what do you mean by fixing the transaction? commit/rollback?? if yes, actually I have a quite complex sql query running, and this is already taking some time... So all the concurrent requests are always waiting on the other select for update queries... — Bast
Show us the other transaction; let's discuss what can be done for it. That is, this question is "blaming the victim"; let's go after the "villain". — Rick James

Vlad Mihalcea Vlad Mihalcea · Accepted Answer · 2019-04-16T07:10:23

MySQL 8.0 introduced support for both SKIP LOCKED and NO WAIT.

SKIP LOCKED is useful for implementing a job queue (a.k.a batch queue) so that you can skip over locks that are already locked by a concurrent transaction.

NO WAIT is useful for avoiding waiting until a concurrent transaction releases the locks that we are also interested in locking.

Without NO WAIT, we either have to wait until the locks are released (at commit or release time by the transaction that currently holds the locks) or the lock acquisition times out. NO WAIT acts as a lock timeout with a value of 0.

For more details about SKIP LOCK and NO WAIT.

MySQL InnoDB "SELECT FOR UPDATE" - SKIP LOCKED equivalent

5 Answers