带有 FOR UPDATE 的 MySQL 读锁

Question

以下假设所有语句都在 MySQL 5.6 上的事务中执行，并带有 InnoDB 表。此外，col1 和 col2 一起构成一个唯一的 key 。

如果我在 session 1 中执行 SELECT * FROM table WHERE col1 = 1 AND col2 = 1 FOR UPDATE，我会从该行获取数据并在该行上获得独占锁。因此，如果我在 session 2 上执行相同的语句，它会等到锁被释放后再做任何事情或遇到超时。到目前为止，还不错。

现在假设 SELECT ... FOR UPDATE 返回一个空集，我们尝试在另一个 session 中插入一些东西:

-- 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 相同的方式等待> 会。我如何在不锁定整个表的情况下实现这一点，这是 Not Acceptable ，因为其他行需要保持可访问性。

Answer 1

当您让事务运行超过几秒钟时，您就是在自找麻烦。编写您的代码以更快地完成。您的第一个代码示例不是 innodb_lock_wait_timeout，它默认为长得可笑的 50 秒。

您必须编写代码来捕获错误——例如您遇到的两个不同的错误——并采取规避措施。该操作通常是重新开始整个交易。到那时，另一个 session 很可能已经完成了它正在做的任何事情，你应该“立即”完成。

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

如果您想进一步描述您的应用，我们可以进一步讨论。