MySQL/InnoDB事务与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 ...;

条件：

• 插入必须是原子的。如果其中一个失败了我想退回去。
• 在SELECT和INSERT from/INSERT tableA之间不允许从/写到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;
}     

我觉得还有很多事情可以做得更好，但我不知道怎么做。

为什么要这样？

• 如果插入失败，我需要某种事务来执行回滚。
• 我需要锁定tableA，以确保没有发生其他插入或更新。
• 事务和表锁不能很好地一起工作(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的细节与这个问题无关。

我想做的是：

一步步地：

1. 从t1中选择多个列和行。
2. 在PHP中分析检索到的数据。根据这一分析的结果，建立了一个数据集。
3. 将数据集的部分插入到t1中，部分插入到t2中。

其他信息：

• 插入到两个表中的必须是原子的。这可以使用事务来实现。
• 在步骤1和步骤3之间，不允许从不同连接插入任何内容。这一点非常重要，因为每次插入到t1中都必须充分了解表的当前状态。我将更详细地描述这一点。为了让事情更容易理解，我将暂时不把t2扯进来。 想象一下这一系列事件(connections、con1和con2)：

1. con1: SELECT ... FROM t1 WHERE xyz;
2. con1: PHP processes the information.
3. con2: SELECT ... FROM t1 WHERE uvw;
4. con2: PHP processes the information.
5. con1: INSERT INTO t1 ...;
6. con2: INSERT INTO t1 ...;

因此，这两个连接都看到t1处于相同的状态。然而，他们选择了不同的信息。Con1获取所收集的信息，对其执行一些逻辑操作，然后将数据插入到t1中的新行中。Con2做同样的事情，但是使用不同的信息。

问题是:两个基于计算的连接INSERTed数据都没有考虑到插入到t1中的其他连接，因为这些信息在从t1读取时不存在。

Con2可能已经将一行插入到t1中，这将满足con1的SELECT-语句的WHERE-条件。换句话说:如果con2更早地插入它的行，con1可能会创建完全不同的数据插入到t1中。这就是说:这两个插入可能使彼此的插入完全无效。

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

我希望这能澄清一点..。:/

思想：

我的想法是：

1. 我需要使插入到两个表原子。->我会用一笔交易来完成这件事。就像这样： 尝试{$pdo->初级事务处理()；//插入到t1 . //插入到t2 . $pdo->commit()；} catch (异常$e) { $pdo->rollBack()；抛出$e}；
2. 我需要确保没有其他连接写入或从t1读取。这就是我决定需要锁桌的地方。
3. 假设我必须使用锁表，我面临的问题是锁表不知道事务。这就是为什么我决定采用这里提出的解决方案(https://dev.mysql.com/doc/refman/8.0/en/lock-tables-and-transactions.html)，以及对堆栈溢出的多个答案。

但是我对代码的样子并不满意，这就是为什么我来这里问这个(同时也是相当长的)问题。

编辑2 (2018-06-01)

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

Answer 1

案例1:

BEGIN;
INSERT ..
INSERT ..
COMMIT;

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

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

不要在LOCK TABLES表上使用InnoDB。

不必费心使用autocommit；BEGIN..COMMIT会重写它。

我的声明适用于(可能)所有框架。(但有些人没有“尝试”和“捕捉”。)

Case 2：锁定一行，以便可能修改它：

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

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

Case 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。你也可以把蛋糕吃了。也就是说，似乎插入被阻塞了，但是您可以获得它们并行发生的性能好处。魔术。