php - InnoDB中具有表锁的MySQL/InnoDB事务

Question

我做了很多研究，发现了很多有关所有相关主题的信息。但是，我不确定我现在是否了解如何正确地将所有这些信息放在一起。

该应用程序是用PHP编写的。

对于查询，我使用PDO。

MySQL数据库配置为InnoDB。

我需要的

SELECT ... FROM tableA;

// PHP looks at what comes back and does some logic.
INSERT INTO tableA ...;
INSERT INTO tableB ...;

条件：


INSERT必须是原子的。如果其中之一失败，我想回滚。
SELECT与INSERT之间的对tableA的读/写操作不允许发生。


在我看来，这似乎是一个非常简单的问题。但是我不知道如何正确执行此操作。所以我的问题是：

最好的方法是什么？

这是我当前计划的概要，已大大简化：

try {
  SET autocommit = 0;

  BLOCK TABLES tableA WRITE, tableB WRITE;

  SELECT ... FROM tableA;

  INSERT INTO tableA ...;
  INSERT INTO tableB ...;

  COMMIT;

  UNLOCK TABLES;

  SET autocommit = 1;
}

catch {
  ROLLBACK;

  UNLOCK TABLES;

  SET autocommit = 1;
}     

我觉得还有很多可以做得更好的方法，但我不知道该如何做：/

为什么会这样呢？


如果INSERT失败，我需要某种事务来进行回滚。
我需要锁定tableA以确保没有其他INSERT或UPDATE发生。
事务和表锁不能很好地协同工作
（ https://dev.mysql.com/doc/refman/8.0/en/lock-tables-and-transactions.html）
我想在整个应用程序其余部分中使用自动提交作为标准，这就是为什么我在最后将其设置回“ 1”的原因。
我真的不太确定：但是我在某个地方找到了一个东西，那就是在锁定一个表之后，我只能（从当前连接内）查询该表直到解锁为止（这对我来说没有意义）。这就是为什么我也锁定了tableB的原因，尽管如此我完全不需要了。


我愿意接受完全不同的方法

我愿意在PHP，MySQL，PDO和InnoDB框架条件下提出任何建议。

谢谢！



编辑1（2018-06-01）

我觉得我的问题需要进一步澄清。

初始点：

如果有两个表，则为t1和t2。

t1具有多列非唯一值。

t2的细节与此问题无关。

我想做的事：

一步步：


从t1选择多个列和行。
在PHP中分析检索到的数据。根据此分析的结果，将数据集放在一起。
将数据集的一部分插入到t1中，并将其一部分插入到t2中。


附加信息：


插入2个表中的内容必须是原子的。这可以使用事务来实现。
在步骤1和步骤3之间不允许发生来自其他连接的INSERT。这非常重要，因为到t1的每一次INSERT操作都必须完全了解表的当前状态。我将对此进行更详细的描述。我暂时将t2排除在外，以使事情更容易理解。

想象一下以下事件序列（连接con1和con2）：


con1：在t1的xyz中选择...
con1：PHP处理信息。
con2：在uvw的t1中选择...；
con2：PHP处理信息。
con1：插入t1 ...;
con2：插入t1 ...


因此，两个连接的t1处于相同状态。但是，他们选择不同的信息。 Con1获取收集的信息，对其进行一些逻辑处理，然后将数据插入t1中的新行。 Con2的功能相同，但使用的信息不同。

问题是这样的：两个连接都基于不考虑任何其他连接插入到t1的计算来插入数据，因为当它们从t1读取时不存在该信息。

Con2可能已经在t1中插入了满足con1的SELECT语句的WHERE条件的行。换句话说：如果con2较早插入了其行，则con1可能创建了完全不同的数据以插入到t1中。这就是说：两个INSERT可能已经完全使彼此的插入无效。

这就是为什么我要确保一次仅一次连接可以处理t1中的数据。在当前连接完成之前，不允许其他任何连接写入，也不允许其他任何连接读取。

我希望这可以澄清一些事情...：/


想法：

我的想法是：


我需要将INSERT放入2个表中。 ->我将为此使用事务。像这样：

try {
  $pdo->beginTransaction();
  // INSERT INTO t1 ...
  // INSERT INTO t2 ...
  $pdo->commit();
}
catch (Exception $e) {
  $pdo->rollBack();
  throw $e;
}

我需要确保没有其他连接写入或读取t1。这是我决定需要锁定表的地方。
假设我必须使用LOCK TABLES，我面临的问题是LOCK TABLES不支持事务。这就是为什么我决定使用此处提出的解决方案（ https://dev.mysql.com/doc/refman/8.0/en/lock-tables-and-transactions.html）以及在stackoverflow上使用多个答案的原因。


但是我对代码的外观不满意，这就是为什么我来这里问这个（同时相当漫长）的问题。



编辑2（2018-06-01）

此过程不会经常运行。因此，对高性能和高效率的需求不大。当然，这也意味着其中两个过程相互推断的机会非常小。 Stil，我想确保什么都不会发生。

Answer 1

情况1：

BEGIN;
INSERT ..
INSERT ..
COMMIT;

直到提交后，其他连接才会看到插入的行。也就是说， BEGIN...COMMIT使两个插入物“原子化”。

如果有任何失败，您仍然需要尝试/捕获来处理它。

不要在InnoDB表上使用 LOCK TABLES。

不要为 autocommit烦恼； BEGIN..COMMIT覆盖它。

我的陈述适用于（可能）所有框架。 （除了有些没有“ try”和“ catch”。）

情况2：锁定行以预期可能会对其进行修改：

BEGIN;
SELECT ... FROM t1 FOR UPDATE;
... work with the values SELECTed
UPDATE t1 ...;
COMMIT;

这样可以使其他人远离 SELECTed行，直到 COMMIT之后。

情况3：有时，IODKU在单个原子语句中做两件事很有用：

INSERT ...
    ON DUPLICATE KEY UPDATE ...

代替

BEGIN;
SELECT ... FOR UPDATE;
if no row found
    INSERT ...;
else
    UPDATE ...;
COMMIT;

第4类：经典银行业务示例：

BEGIN;
UPDATE accounts SET balance = balance - 1000.00 WHERE id='me';
... What if crash occurs here? ...
UPDATE accounts SET balance = balance + 1000.00 WHERE id='you';
COMMIT;

如果系统在两个 UPDATEs之间崩溃，则第一个更新将被撤消。这可以防止系统失去对资金转移的跟踪。

情况5：也许接近OP的要求。它主要是案例2和案例1的结合。

BEGIN;
SELECT ... FROM t1 FOR UPDATE;   -- see note below
... work with the values SELECTed
INSERT INTO t1 ...;
COMMIT;

关于情况5的注释： SELECT..FOR UPDATE必须包含您不希望其他连接看到的任何行。这具有将另一个连接延迟到该连接 COMMITs之前的效果。 （是的，这很像 LOCK TABLES t1 WRITE。）

情况6：需要在BEGIN..COMMIT内部进行的“处理”将花费很长时间。 （例如：典型的在线购物车。）

这需要InnoDB事务之外的锁定机制。一种方法（对购物车有用）是在一些额外的表中使用一行，然后让所有人检查它。另一种方法（在单个连接中更实用）是使用 GET_LOCK('foo')及其朋友。

一般讨论;一般交流

以上所有示例仅锁定涉及的行，而不锁定整个表。这使操作的侵入性大大降低，并允许系统处理更多的活动。

另外，请阅读有关MVCC的信息。这是一种常规技术，即使在其他连接正在修改表的情况下，也可以使某个连接在某个瞬间看到表的值。

“防止插入”-使用MVCC，如果您启动 SELECT，就像在获取所有正在查看的内容时及时获取快照一样。在完成 INSERTs所在的事务后，您才会看到 SELECT。您也可以吃蛋糕也可以吃。就是说，插入似乎被阻塞了，但是您可以从并行执行的性能中受益。魔法。