使INSERT ... SELECT语句成为原子 [英] Making an INSERT ... SELECT statement atomic

问题描述

我有两个表:一个存储数据，另一个存储锁，以指示用户何时对该数据进行操作.我想从第一个表中选择一些项目，以便它们与多个条件匹配，并且在另一个表中没有相应的锁，然后将这些项目的锁添加到第二个表中.由于许多用户可能会同时尝试锁定项目，因此必须自动完成此操作.

I have two tables: one stores data and the other stores locks to indicate when a user is operating on that data. I'd like to select some number of items from the first table, such that they match several conditions and do not have a corresponding lock in the other table, and then add locks for these items to the second table. Since many users may simultaneously attempt to lock items it will be necessary for this to be done atomically.

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

I've written the SQL statement below to attempt to do this, but I receive the error 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;

有什么方法可以在不锁定表的情况下使它成为原子操作?当前，我正在使用事务，如果不成功，则尝试重新尝试，但是我对这是否可以避免很感兴趣.我正在将MySQL 5.0.95版与InnoDB一起使用.

Is there any way to make this an atomic operation without locking the tables? Currently I'm using a transaction and reattempting if it's unsuccessful, but I'm interested in whether this is avoidable. I'm using MySQL version 5.0.95 with InnoDB.

谢谢

编辑

经过进一步的思考，我意识到锁定表1是不可接受的，但是我可以锁定表2.由于我实际上无法在语句中锁定表(因为如果选择锁定其中一个表，必须锁定所有表)，所以我可以改用GET_LOCK创建互斥体，以防止多个进程同时调用此代码.我还没有机会测试这种方法，但是感觉它可能比事务处理更轻巧.

Having given this some further thought I've realised that whilst locking table1 is unacceptable, I can lock table2. Since I can't actually lock the table in the statement (since I have to lock all tables if I choose to lock one of them) I can instead use GET_LOCK to create a mutex preventing multiple processes calling this code simultaneously. I've not yet had a chance to test this approach, but it feels like it might be a more lightweight solution than transactions.

推荐答案

否.这就是交易的全部内容.他们通过一个原子操作来组织一堆语句，这些语句总体上成功或失败.

No. This is what the transactions are all about. They organize a bunch of statements in one atomic operation which either succeeds or fails as a whole.

这里查找有关乐观和悲观锁定的一些说明，也许您会发现它们很有用. 在这里，您可以找到有关InnoDB中使用的锁定机制(悲观锁定). 此处，您可以找到有关如何实施乐观锁定在mysql中.

Here you could find some explanation for optimistic and pesimistic lock which perhaps you could find useful. Here you could find some details about the locking mechanism used in InnoDB (pesimistic locking). Here you could find guidelines on how to implement the optimistic locking in mysql.

这篇关于使INSERT ... SELECT语句成为原子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！