mysql - 使用 select...for update 来隔离行的最小示例

Question

更新:现在有用于 Mysql 和 Postgres 的 SKIP LOCKED 和 NOWAIT。

下面是老问题。

---

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

我在使用 select... for update 时遇到了麻烦，因为第二个事务争用相同的内容。请提供选择不同行的不同事务的最小示例。

我的数据是:

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)

而且这些东西没有按预期工作:

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

...作为以后的第二笔交易

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)

Answer 1

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

如果你在 id 上创建一个 UNIQUE INDEX，你可以这样做；

select * from SolrCoresPreallocated where id=1 for update;

在第一笔交易中；

select * from SolrCoresPreallocated where id=2 for update;

在第二个中是独立的，因为唯一索引让第二个选择找到正确的行而不用读锁定第一个。

编辑:要尽快获得“免费”行，真正唯一的方法是进行两次交易；

• BEGIN/SELECT FOR UPDATE/UPDATE 到 busy/COMMIT 以获取行。
• BEGIN/ /UPDATE to free/COMMIT 处理行并释放它。

这意味着您可能需要补偿操作，以防进程失败并回滚将更新行以释放的事务，但由于 MySQL(或与此相关的标准 SQL)没有“获取下一个解锁行”，你没有太多选择。