如何在 mysql 中使用 UPDATE 自动声明行或资源

Question

我有一个资源表（比如说汽车），我想以原子方式声明它。然后我想要有关我刚刚声明的资源的信息。

如果每个用户只能使用一个资源，我可以执行以下技巧：

UPDATE cars SET user = 'bob' WHERE user IS NULL LIMIT 1
SELECT * FROM cars WHERE user = 'bob'

这样，我可以原子地声明该资源，然后我可以看到我刚刚声明了哪一行。

当“鲍勃”可以认领多辆车时，这不起作用。我意识到我可以获取鲍勃已经认领的汽车列表，认领另一辆，然后再次SELECT看看发生了什么变化，但这感觉很黑客。

我想知道的是，是否有某种方法可以查看我刚刚用上次更新更新了哪些行？

如果做不到这一点，是否还有其他技巧可以自动声明一行？我真的想避免使用SERIALIZABLE隔离级别。如果我这样做：

1 SELECT id FROM cars WHERE user IS NULL
2 <here, my PHP or whatever picks a car id>
3 UPDATE cars SET user = 'bob' WHERE id = <the one i picked>

REPEATABLE READ 在这里就足够了吗？换句话说，我能否保证其他一些事务不会声明我的软件在第 2 步中选择的行？

Answer 1

UPDATE cars SET user = 'bob' WHERE id = 123 AND user IS NULL;

更新查询返回更改的行数。如果没有更新，您就知道该车已被其他人认领。

或者，您可以使用 SELECT ... FOR UPDATE。

Answer 2

我不确定为什么这些答案中有这么多错误信息，但答案很简单（即使是高级 SQL 主题）。这就是几乎所有 RDBMS 中“锁定”的用途。确切的语法取决于供应商和版本，有些提供尝试向用户隐藏锁定的语法（通常当选择和更新都在同一查询中时）。

对于 MySQL，您首先要使用 SELECT ... FROM ... FOR UPDATE;，它告诉数据库在它返回的每条记录上设置独占锁。

重要不要锁定超出绝对需要的行！使“SELECT FOR UPDATE”查询尽可能细化，并自由使用“WHERE”和“LIMIT”子句。

此后，当与数据库的同一连接对先前锁定的同一行发出 UPDATE ... 时，该锁定将被释放，其他人可以再次访问该行。

假设您有一个作业队列，其中有一个“状态”字段，用于设置每个作业的进度状态。 0 表示已排队，1 表示正在进行，2 表示完成，3 表示失败，等等。

每个运行者都可以通过发出以下命令自动获取要运行的作业（以便没有两个运行者尝试执行同一个作业） ：

SELECT ID, * FROM JOBS WHERE STATUS = 0 LIMIT 1 FOR UPDATE;

然后

UPDATE JOBS SET STATUS = 1 WHERE JOBS.ID = X;

它就可以运行作业，并在完成后更新数据库：

UPDATE JOBS SET STATUS = [2|3] WHERE JOBS.ID = X;

重要

来自 MySQL 文档：

当事务提交或回滚时，由 LOCK IN SHARE MODE 和 FOR UPDATE 查询设置的所有锁都会被释放。

如果启用自动提交，SELECT FOR UPDATE 不会锁定记录。禁用自动提交或（最好）使用 START TRANSACTION;选择...从...进行更新；更新 ...;结束交易；

Answer 3

update cars  
set @id = id, user= 'bob' 
where user is null

保证是原子的，@id 会告诉你更新的最后一行是什么。

Answer 4

您可以使用的一件事是 SELECT FOR UPDATE。这将让您进行选择，了解您选择的内容，然后更新这些值。当事务完成时，锁被释放。

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "test");

mysqli_autocommit($link, FALSE);

$result = mysqli_query($link, "SELECT id FROM cars WHERE user IS NULL");

// do something with the results

mysqli_query($link, "UPDATE cars SET user = 'bob' WHERE id = <the one that was picked>");

mysqli_commit($link);

mysqli_close($link);
?>