mysql - 使 INSERT ... SELECT 语句成为原子的

Question

我有两个表:一个存储数据，另一个存储锁以指示用户何时对该数据进行操作。我想从第一个表中选择一些项目，使它们符合几个条件并且在另一个表中没有相应的锁，然后将这些项目的锁添加到第二个表中。由于许多用户可能同时尝试锁定项目，因此有必要以原子方式完成此操作。

我已经编写了下面的 SQL 语句来尝试执行此操作，但我收到错误消息 Deadlock found when trying to get lock;。

INSERT INTO table2 (id, user, date)
    SELECT id, ?, NOW()
    FROM table1
    LEFT JOIN table2 USING id
    WHERE locked IS NULL AND <several conditions on table1>
    ORDER BY date 'DESC'
    LIMIT 15;

有没有办法在不锁定表的情况下使它成为一个原子操作？目前我正在使用事务并在不成功时重新尝试，但我想知道这是否可以避免。我在 InnoDB 中使用 MySQL 5.0.95 版。

谢谢

编辑

经过进一步思考，我意识到虽然锁定 table1 是 Not Acceptable ，但我可以锁定 table2。因为我实际上不能在语句中锁定表(因为如果我选择锁定其中一个表我必须锁定所有表)我可以改为使用 GET_LOCK 创建一个互斥锁以防止多个进程同时调用此代码。我还没有机会测试这种方法，但感觉它可能是一种比事务更轻量级的解决方案。

Answer 1

没有。这就是交易的全部内容。他们在一个原子操作中组织一堆语句，这些语句要么作为一个整体成功，要么作为一个整体失败。

Here你可以找到一些关于 optimistic 和 pesimistic 锁的解释，也许你会觉得有用。 Here您可以找到有关 InnoDB 中使用的锁定机制(悲观锁定)的一些详细信息。 Here您可以找到有关如何在 mysql 中实现乐观锁定的指南。