时间戳

首页 > 解决方案 > 在 InnoDB 中具有表锁的 MySQL/InnoDB 事务

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

问题描述

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

这个应用程序是用 PHP 编写的。

对于查询，我使用 PDO。

MySQL 数据库配置为 InnoDB。

我需要的

SELECT ... FROM tableA;

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

条件：

这在我看来是一个非常简单的问题。但是我无法弄清楚如何正确地做到这一点。所以我的问题是：

最好的方法是什么？

这是我当前计划的大纲，非常简化：

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;
}

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

为什么会这样？

我对完全不同的方法持开放态度

我愿意接受 PHP、MySQL、PDO 和 InnoDB 框架条件下的任何建议。

谢谢你！

编辑 1 (2018-06-01)

我觉得我的问题/疑问需要更多澄清。

初始点：

如果有两个表，t1和t2。

t1具有多列非唯一值。

t2的细节与此问题无关。

我想做的事：

一步步：

  1. 从t1中选择多列和多行。

  2. 在 PHP 中分析检索到的数据。根据该分析的结果整理了一个数据集。

  3. 将数据集的一部分插入到t1中，将部分数据集插入到t2中。

附加信息：

想法：

我的想法是：

  1. 我需要将插入到 2 个表中成为原子的。--> 我将为此使用事务。像这样的东西：

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

  2. 我需要确保没有其他连接写入或读取t1。这就是我决定需要 LOCK TABLES 的地方。

  3. 假设我必须使用 LOCK TABLES，我遇到了 LOCK TABLES 不支持事务的问题。这就是为什么我决定采用此处提出的解决方案（https://dev.mysql.com/doc/refman/8.0/en/lock-tables-and-transactions.html）以及stackoverflow上的多个答案。

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

编辑 2 (2018-06-01)

此过程不会经常运行。因此，对高性能和效率没有太大的需求。当然，这也意味着其中两个过程相互推断的可能性很小。不过，我想确保不会发生任何事情。

标签： phpmysqlpdolockinginnodb

解决方案

情况1：

BEGIN;
INSERT ..
INSERT ..
COMMIT;

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

如果有任何失败，你仍然需要 try/catch 来处理它。

不要LOCK TABLES在 InnoDB 表上使用。

不要打扰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。您也可以吃蛋糕。也就是说，看起来好像插入被阻塞了，但是您会获得并行发生的性能优势。魔法。

推荐阅读