MySQL读锁与FOR UPDATE

Question

MySQL读锁与FOR UPDATE

4

以下假设在MySQL 5.6上以InnoDB表的事务中执行所有语句。同时，col1和col2共同形成唯一键。

如果我在Session 1中执行SELECT * FROM table WHERE col1 = 1 AND col2 = 1 FOR UPDATE，我将获取该行的数据并锁定该行。因此，如果我在Session 2中执行相同的语句，它将等待释放该锁或超时之前不会执行任何操作。到目前为止，一切顺利。

现在假设SELECT ... FOR UPDATE返回一个空集，并且我们尝试在另一个会话中插入一些内容：

-- Session 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `table` WHERE `col1` = 1 AND `col2` = 1 FOR UPDATE;
Empty set (0.01 sec)
-- Do whatever else takes some time

-- Session 2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `table` SET `col1` = 1, `col2` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

这种行为符合我的预期。然而，如果我们这样做：

-- Session 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `table` WHERE `col1` = 1 AND `col2` = 1 FOR UPDATE;
Empty set (0.00 sec)

-- Session 2
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM `table` WHERE `col1` = 1 AND `col2` = 1 FOR UPDATE;
Empty set (0.00 sec)

mysql> INSERT INTO `table` SET `col1` = 1, `col2` = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

现在，我真正想要的是，对于相同（尚不存在的）行上的第二个SELECT ... FOR UPDATE也能像INSERT INTO一样等待。如何在不锁定整个表的情况下实现这一点，因为其他行需要保持可访问性。

- Anpan

我不认为你想要的是可能的，因为你对由SQL语句设置的锁没有太多的控制权。当你使用InnoDB时，插入语句总是会生成一个独占意向锁，并且select for update也会请求独占锁。 - Shadow

手头的问题是一个竞态条件。会话1将使用select for update检查特定列是否已经存在，如果不存在，则创建并保存该列。然而，会话2看不到即将出现的行，因为处理发生在MySQL外的php脚本中。在这一行实际存在之前，可能需要几毫秒的时间。我当然可以绕过这个问题，但按照我上面所说的方式做将是一个更干净的解决方案，我个人认为。 - Anpan

我理解你的问题，但MySQL的解决方案是针对受影响的语句使用不同类型的锁。只要在选择语句中使用了for update子句，你就会陷入困境。显然，如果你使用了共享模式锁，行为将会有所不同。然而，我不知道你的业务需求，所以我不知道这种方法是否适合你。 - Shadow

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Rick James · Accepted Answer

当一个事务运行时间超过几秒钟时，你就会遇到问题。编写代码要尽快完成。你的第一个代码示例没有使用innodb_lock_wait_timeout，默认为荒谬的50秒。

你必须编写代码来捕获错误，比如你得到的两个不同的错误，并采取逃避行动。通常的做法是重新开始整个事务。那时，另一个会话很可能已经完成了它正在做的事情，你应该立即通过。

如果你需要一个非常长的锁，比如一个购物车，在其中用户在网页之间反复跳转数分钟，你必须使用一些其他机制来“锁定”，而不是InnoDB的事务锁定。

如果你想进一步描述你的应用程序，我们可以进一步讨论这个问题。