使用select ...进行更新以隔离行的最小示例 [英] Minimal example of using select... for update to isolate rows

问题描述

更新:Mysql和Postgres现在有SKIP LOCKED和NOWAIT.

UPDATE: There is now SKIP LOCKED and NOWAIT for Mysql and Postgres.

接下来是老问题.

我希望并发事务从表中选择一行，将其标记为脏"，以便其他事务无法选择，然后执行其余的事务.

I want concurrent transactions to select a row from the table, marking it as "dirty" so that other transactions cannot select it, then performing the rest of the transaction.

我很难使用select... for update来实现此目的，因为第二笔交易也是如此.请提供一个不同交易选择不同行的最小示例.

I had trouble using select... for update for this purpose as the second transaction contends for the same. Please provide a minimal example for different transactions to select distinct rows.

我的数据是:

mysql> select * from SolrCoresPreallocated;
+----+-------------+-----+-----+
| id | used_status | sid | cid |
+----+-------------+-----+-----+
|  1 |           0 |   0 | 400 |
|  2 |           0 |   0 | 401 |
|  3 |           0 |   0 | 402 |
|  4 |           0 |   0 | 403 |
|  5 |           0 |   0 | 404 |
|  6 |           0 |   0 | 405 |
+----+-------------+-----+-----+
6 rows in set (0.00 sec)

这东西不能按预期工作:

And this stuff is not working as expected:

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

mysql> select * from SolrCoresPreallocated order by id limit 1 for update;
+----+-------------+-----+-----+
| id | used_status | sid | cid |
+----+-------------+-----+-----+
|  1 |           0 |   0 | 400 |
+----+-------------+-----+-----+
1 row in set (0.00 sec)

...set the used_status to 1
...perform the rest of the operations

...作为第二笔交易

...as the second transaction onward

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

mysql> select * from SolrCoresPreallocated order by id limit 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  rollback;
Query OK, 0 rows affected (0.00 sec)

推荐答案

SELECT ... FOR UPDATE以独占模式锁定行，这意味着第二个选择要等到第一个选择完成或回滚才能继续.这是因为第二选择的结果可能会受到您锁定的行内容的影响，因此它需要获得对该行的读取锁定才能进行检查.

SELECT ... FOR UPDATE locks the row(s) in exclusive mode, which means the second select cannot proceed until the first one has completed or rolled back. This is since the second select's result could be affected by the content of the row you've locked, so it needs to get a read lock to the row to check.

如果您在例如id上创建UNIQUE INDEX，则可以这样做；

If you create a UNIQUE INDEX on for example id, you could do;

select * from SolrCoresPreallocated where id=1 for update;

在第一笔交易中；

select * from SolrCoresPreallocated where id=2 for update;

在第二个中独立进行，因为唯一索引使第二个选择在不读取锁定第一行的情况下找到正确的行.

in the second one independently, since the unique index lets the second select find the correct row without read-locking the first one.

要尽快获得免费"行，唯一的方法实际上是进行两次交易；

To get a "free" row as quickly as possible, the only way really is to do two transactions;

• 开始/选择更新/更新到忙/提交以获取行.
• BEGIN/<处理行>/UPDATE释放/COMMIT处理该行并释放它.

这意味着您可能需要采取补偿措施，以防进程失败并回滚将更新该行以释放该行的事务，但是由于MySQL(或标准SQL)没有获取该行"的概念，下一个未锁定的行"，则没有太多选择.

This means that you may need compensating actions in case a process fails and rolls back the transaction that would UPDATE the row to free, but since MySQL (or standard SQL for that matter) doesn't have a notion of "get the next unlocked row", you don't have many options.

这篇关于使用select ...进行更新以隔离行的最小示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！