MySQL 使用 select... for update 隔离行的最小示例
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12758710/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Minimal example of using select... for update to isolate rows
提问by Jesvin Jose
UPDATE: There is now SKIP LOCKEDand NOWAITfor Mysql and Postgres.
更新：现在SKIP LOCKED和NOWAIT用于 Mysql 和 Postgres。
Old question follows.
老问题如下。
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.
我希望并发事务从表中选择一行，将其标记为“脏”，以便其他事务无法选择它，然后执行事务的其余部分。
I had trouble using select... for updatefor this purpose as the second transaction contends for the same. Please provide a minimal example for different transactions to select distinct rows.
我在select... for update为此目的使用时遇到了麻烦，因为第二个事务也争用了相同的内容。请为不同的交易提供一个最小的例子来选择不同的行。
My data is:
我的数据是：
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)
采纳答案by Joachim Isaksson
SELECT ... FOR UPDATElocks 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.
SELECT ... FOR UPDATE以独占模式锁定行，这意味着在第一个选择完成或回滚之前，第二个选择无法继续。这是因为第二个选择的结果可能会受到您锁定的行的内容的影响，因此它需要获得该行的读锁以进行检查。
If you create a UNIQUE INDEXon for example id, you could do;
UNIQUE INDEX例如id，如果您创建一个on ，您可以这样做；
select * from SolrCoresPreallocated where id=1 for update;
in the first transaction and;
在第一笔交易中并且；
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.
在第二个中独立，因为唯一索引让第二个选择找到正确的行，而无需对第一个进行读锁定。
EDIT: To get a "free" row as quickly as possible, the only way really is to do two transactions;
编辑：要尽快获得“免费”行，唯一的方法就是进行两次交易；
- BEGIN/SELECT FOR UPDATE/UPDATE to busy/COMMIT to get the row.
- BEGIN/<process row>/UPDATE to free/COMMIT to process the row and release it.
- BEGIN/SELECT FOR UPDATE/UPDATE to busy/COMMIT 来获取行。
- BEGIN/<process row>/UPDATE to free/COMMIT 处理该行并释放它。
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.
这意味着如果进程失败并回滚将更新行以释放的事务，您可能需要补偿操作，但由于 MySQL（或标准 SQL）没有“获取下一个解锁行”的概念”，你没有太多选择。
