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)

Solution 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.

Solution 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.

Solution 3

Unfortunately, it seems that there is no way to skip the locked row in a select for update so far.

It would be great if we could use something like the Oracle 'FOR UPDATE SKIP LOCKED'.

In my case, the queries launched in parallel are both exactly the same, and contain a 'where' clause and a 'group by' on a several millions of rows...because the queries need between 20 and 40 seconds to run, that was (as I already knew) a big part of the problem.

The only -temporary and not the best- solution I saw was to move some (i.e.: millions of) rows that I would not (directly) use in order to reduce the time the query will take.

So I will still have the same behavior but I will wait less time...

I was expecting a way to not select the locked row in the select.

I don't mark this as an answer, so if a new clause from mysql is added (or discovered), I can accept it later...

Comments

[Bast]

Thanks for the answer. But, in my case, I have several processes running in //. They update data on mytable. I only want to update some data in order to be able to launch several threads in //, and make the global update faster. Of course I have extra condtions in the real where clause, but I only show a simple example here.

[Shadow]

I'm sorry, but I cannot possibly comment on a design I'm not familiar with. You need to take a look at your processes and evaluate whether the way they are locking records is appropriate or even the way those processes are designed is adequate.

[Bast]

OK. Well my different concurrent processes are doing something apparently really simple: take the first rows (which don't contain a specific flag). 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...

[Panagiotis Kanavos]

@Bast the "really simple" thing is actually a complex operation - you are trying to emulate a checkin/checkout operation. You can't emulate this with update locks as you found out. Not only will you run into timeouts, your application will have to keep a connection open for the entire duration of the operation. That's a bad idea if you try to do the long running operation on a different machine

[Shadow]

@Bast Then use a flag field instead of locking and either an isolation mode that allows you to see uncommited changes or update the flag fields outside of the long lasting transactions.

[Bast]

@PanagiotisKanavos, so what do you propose?

[Bast]

@Shadow, that's exactely what I did (see my previous comment). My transaction is only to update the flag. Then I commit (or rollback if some errors happen...). What do you mean by "isolation mode"... Mutex or something like that? because I would also lose time if so.

[Shadow]

@Bast search for mysql isolation level and you will see what I mean. But I would commit the transaction on the flag field to indicate which records a process is going to process. By flag field I meant a field that flags that a record is being processed.

[Bast]

@Shadow, I am already doing this (update a flag). That's what I told you in my two previous commits + in my EDIT. BTW: the isolation mode seems interseting - dev.mysql.com/doc/refman/5.0/en/set-transaction.html

[Bast]

thanks for your answer. Actually, this could be the solution when you are in a mono-process program... As I said, I have different concurrent processes, and the goal is to cross the entire DB. But there is no way to know which thread is selecting/locking which rows at a time t.

[Rick James]

True. And my suggestion will still encounter lock_waits and maybe deadlocks. However, my suggestion will, I hope, greatly decrease the locking, thereby making the entire process tolerable.

[Bast]

Yes, as I said in my answer below (stackoverflow.com/a/33515992/5165343), I moved millions of "useless rows" to a separate table in order to save some time...

[abdel]

i have the same problem on mysql 5.7 and i am struggling to find a solution. my table is acting as a queue. i have multiple threads reading data from the table to process rows one row at a time. therefore my query uses 'limit 1 for update'. i agree it apears there is no way to skip the locked rows except in mysql8 onwards