首页 > mysql > MySQL InnoDB“SELECT FOR UPDATE” - SKIP LOCKED等效

MySQL InnoDB“SELECT FOR UPDATE” - SKIP LOCKED等效 (MySQL InnoDB “SELECT FOR UPDATE” - SKIP LOCKED equivalent)

问题

当我们使用InnoDB表在MySQL中进行“SELECT FOR UPDATE”时,有没有办法跳过“锁定行”?

例如:终端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> 

同时,终端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> 

因此,如果我启动一个查询强制它选择其他行,那很好。

但有没有办法跳过锁定的行?

我想这应该是并发过程中的一个冗余问题,但我没有找到任何解决方案。


编辑:实际上,我的不同并发进程显然做了一些非常简单的事情:

  1. 取第一行(不包含特定标志 - 例如:“WHERE myflag_inUse!= 1”)。

  2. 一旦我得到“select for update”的结果,我就更新标志并提交行。

所以我只想选择尚未锁定的行和myflag_inUse!= 1 ...


以下链接帮助我理解为什么我得到超时,但不知道如何避免它:

MySQL'选择更新'行为


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)

解决方法

正如我在本文中解释的那样,MySQL 8.0引入了对SKIP LOCKED和NO WAIT的支持。

SKIP LOCKED对于实现作业队列(也称为批处理队列)很有用,这样您就可以跳过已被并发事务锁定的锁。

NO WAIT对于避免等待并发事务释放我们也有兴趣锁定的锁定非常有用。如果没有NO WAIT,我们要么必须等到锁被释放(在当前持有锁的事务的提交或释放时间)或锁获取超时。NO WAIT的作用类似于锁定超时,其值为0

有关SKIP LOCK和NO WAIT的更多详细信息,请查看此文章

问题

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)

解决方法

As I explained in this article, 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 like a lock timeout with a value of 0.

For more details about SKIP LOCK and NO WAIT, check out this article.

相似信息