Mysql select for update - 它没有锁定目标行。我如何确保它确实如此？

Question

所以 select for update 的语法是这样的

SELECT *     //1st query
FROM test
WHERE id = 4 FOR UPDATE;

UPDATE test    //2nd query
SET parent = 100
WHERE id = 4;

我猜锁定部分是第一行。

因此，当第一组查询执行时，我不应该能够选择和修改带有 id = 4 的行(顺便说一句，它是主键)。但是，在我更新任何内容之前，我仍然能够选择带有 id = 4 的行，这意味着另一个线程可能会进入并尝试在第二行命中之前选择并更新同一行，从而导致并发问题.

但是当我像下面这样锁定整个表时

LOCK TABLES test WRITE;

其他事务处于挂起状态，等待锁释放。我想使用 SELECT FOR UPDATE 而不是表锁的唯一原因是这里引用的原因 https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html

如果我只是在这里引用它们，则如下

LOCK TABLES does not play well with transactions. Even if you use the "SET autommit=0" syntax you can find undesired side effects. For instance, issuing a second LOCK TABLES query within a transaction will COMMIT your pending changes:

SET autocommit=0;
LOCK TABLES foo WRITE;
INSERT INTO foo (foo_name) VALUES ('John');
LOCK TABLES bar WRITE; -- Implicit commit
ROLLBACK; -- No effect: data already committed

In many cases, LOCK TABLES can be replaced by SELECT ... FOR UPDATE which is fully transaction aware and doesn't need any special syntax:

START TRANSACTION;
SELECT COUNT(*) FROM foo FOR UPDATE; -- Lock issued
INSERT INTO foo (foo_name) VALUES ('John');
SELECT COUNT(*) FROM bar FOR UPDATE; -- Lock issued, no side effects
ROLLBACK; -- Rollback works as expected

因此，如果我可以在实际更新发生之前访问为更新选择的行，那么 SELECT FOR UPDATE 锁定到底是什么？另外，如何测试我的应用程序中的行是否被锁定？ (它显然不适用于我编写的第一组查询)

表是用InnoDB引擎创建的

弗朗西斯科的解决方案

下面的两种解决方案都会导致 parent 为 1

UPDATE test
SET parent = 99
WHERE id = 4;
COMMIT;

START TRANSACTION;
SELECT *
FROM test
WHERE id = 4 FOR UPDATE;
UPDATE test
SET parent = 98
WHERE id = 4;   //don't commit 

START TRANSACTION;
SELECT *
FROM test
WHERE parent = 98 FOR UPDATE; //commit did not happens so the id=4 document would still be parent = 99
UPDATE test
SET parent = 1
WHERE id = 4;
COMMIT;           //parent = 1 where id = 4

另一个只是将父条件更改为 99 而不是 98

UPDATE test
SET parent = 99
WHERE id = 4;
COMMIT;

START TRANSACTION;
SELECT *
FROM test
WHERE id = 4 FOR UPDATE;
UPDATE test
SET parent = 98
WHERE id = 4;      //Don't commit

START TRANSACTION;
SELECT *
FROM test
WHERE parent = 99 FOR UPDATE;     //targets parent = 99 this time but id=4 still results in parent =1
UPDATE test
SET parent = 1
WHERE id = 4;
COMMIT;

第一组查询运行时，就好像 id=4 文档已首先提交给 parent = 98。但是，第二组查询运行时就好像 id=4 文档还没有提交给 parent = 99。我如何在这里保持一致性？

Answer 1

SELECT FOR UPDATE 锁定您选择更新的行，直到您创建的事务结束。其他事务只能读取该行，但只要 select for update 事务仍然打开，它们就不能更新它。

为了锁定行:

START TRANSACTION;
SELECT * FROM test WHERE id = 4 FOR UPDATE;
# Run whatever logic you want to do
COMMIT;

上面的事务将处于事件状态并且会锁定该行直到它被提交。

为了测试它，有不同的方法。我使用两个终端实例对其进行了测试，每个实例都打开了 MySQL 客户端。

在 第一个终端 上运行 SQL:

START TRANSACTION;
SELECT * FROM test WHERE id = 4 FOR UPDATE;
# Do not COMMIT to keep the transaction alive

在第二个终端你可以尝试更新行:

UPDATE test SET parent = 100 WHERE id = 4;

由于您在第一个终端 上创建了一个 select for update，上面的查询将等待 select for update 事务被提交，否则它将超时。

回到第一个终端并提交事务:

COMMIT;

检查第二个终端，您将看到更新查询已执行(如果它没有超时)。