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:

1. take the first rows (which don't contain a specific flag - e.g.: "WHERE myflag_inUse!=1").

2. 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)

Answer 1

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.

Answer 2

This appears to now exist in MySQL starting in 8.0.1:

https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/

Starting with MySQL 8.0.1 we are introducing the SKIP LOCKED modifier which can be used to non-deterministically read rows from a table while skipping over the rows which are locked. This can be used by our booking system to skip orders which are pending. For example:

However, I think that version is not necessarily production ready.