MySQL选择更新-它没有锁定目标行.如何确定呢? [英] Mysql select for update - it is not locking the target rows. How do I make sure it does?

问题描述

所以选择更新的语法类似于

So the syntax for select for update is something like

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

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

我猜锁定部分是第一行.

I am guessing the locking part is the first line.

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

So when the first set of queries executes, I should not be able to select and modify the row with id = 4 (it is primary key by the way). However, I am still able to select row with id = 4 before I update anything, meaning another thread could probably come in and try to select and update the same row before second row hits, leading to concurrency problem.

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

But when I lock the entire table like below

LOCK TABLES test WRITE;

其他事务正在挂起，并等待直到锁被释放.我想使用 SELECT FOR UPDATE 而不是表锁的唯一原因是由于

Other transactions are pending and waits until the lock is released. Only reason why I would like to use SELECT FOR UPDATE instead of table lock is because of the reason referenced here https://dev.mysql.com/doc/refman/5.7/en/lock-tables-and-transactions.html

如果我在这里引用它们，则如下所示

If I just quote them here, it is as below

LOCK TABLES在事务处理中表现不佳.即使你使用使用"SET autommit = 0"语法可以发现不希望的副作用.为了实例，在事务内发出第二个LOCK TABLES查询将提交您的待定更改:

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

在许多情况下，可以用SELECT ... FOR UPDATE替换LOCK TABLES完全了解事务，不需要任何特殊语法:

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 锁定到底是什么?另外，如何测试行是否在我的应用程序中被锁定?(显然，在我编写的第一组查询中不起作用)

So if I can access the rows selected for update BEFORE actual update occurs, what exactly is SELECT FOR UPDATE locking? Also how can I test that the rows are being locked in my application? (it is obviously not working in the first set of queries that I have written)

该表是使用InnoDB引擎创建的

Francisco的解决方案

Francisco's solution

以下两种解决方案均导致父级为1

Both the solutions below results in parent being 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

Another one just change the parent conditional to 99 instead of 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一样.在这里​​如何保持一致性?

The first sets of query runs as if id=4 document has been committed to parent = 98 first. However, the second sets of query runs as if id=4 document has NOT been committed to parent = 99. How do I maintain consistency here?

推荐答案

SELECT FOR UPDATE 锁定您选择要更新的行，直到创建的事务结束.其他事务只能读取该行，但是只要更新事务的选择仍处于打开状态，它们就无法更新该行.

A SELECT FOR UPDATE locks the row you selected for update until the transaction you created ends. Other transactions can only read that row but they cannot update it as long as the select for update transaction is still open.

为了锁定行:

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

上面的事务将保持活动状态，并将行锁定直到提交.

The transaction above will be alive and will lock the row until it is committed.

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

In order to test it, there are different ways. I tested it using two terminal instances with the MySQL client opened in each one.

在第一个终端上，运行SQL:

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

在第二终端上，您可以尝试更新该行:

On the second terminal you can try to update the row:

UPDATE test SET parent = 100 WHERE id = 4;

由于您在第一个终端上创建了一个更新选择，所以上面的查询将一直等待，直到提交更新选择事务或超时.

Since you create a select for update on the first terminal the query above will wait until the select for update transaction is committed or it will timeout.

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

COMMIT;

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

Check the second terminal and you will see that the update query was executed (if it did not timed out).

这篇关于MySQL选择更新-它没有锁定目标行.如何确定呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！