PDO、MySQL、事务和表锁定

Question

为了好玩，我正在使用PDO替换我的应用程序中的mysqli扩展。

偶尔需要使用事务+表锁定。

在这些情况下，根据mysql手册，语法需要略有不同。而不是调用START TRANSACTION，你可以这样做...

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

(http://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-transactions.html)

我的问题是，这与PDO::beginTransaction如何交互？在这种情况下我能使用PDO::beginTransaction吗？还是应该手动发送SQL“SET autocommit = 0; ...等等”。

谢谢建议，

- Travis

Answer 1

当您调用PDO::beginTransaction()时，它会关闭自动提交。

因此，您可以执行以下操作：

$db->beginTransaction();
$db->exec('LOCK TABLES t1, t2, ...');
# do something with tables
$db->commit();
$db->exec('UNLOCK TABLES');

在执行 commit() 或 rollBack() 后，数据库将恢复为自动提交模式。

- Olhovsky

---

这并不是我的经历（使用带有InnoDB表的mysql 5.6.27），'LOCK TABLES'命令基本上提交了之前开始的事务，并且所有后续的命令都是单独提交的（即没有事务行为）。 - mils

并非所有的数据库都允许在DDL语句上操作事务：有些会生成错误，而其他一些（包括MySQL）将在遇到第一个DDL语句后自动提交事务。 - Rain

Answer 2

我花了很多时间解决这个问题，但是PHP文档在这方面的描述最多只能算模糊。在使用MySQL InnoDB表的情况下，有几点需要注意：

1. PDO::beginTransaction不仅仅关闭自动提交，通过测试Olhovsky提供的答案并不能使回滚操作生效，也就是说没有事务行为。这意味着问题并不简单。

2. 开始一个事务可能会锁定已使用的表... 我迫切地希望有人告诉我这是错误的，但以下是可能的原因：此评论显示在启动事务后表无法访问，但并未被锁定。此PHP文档页面在结尾处提到：

“...在事务处于活动状态时，您可以确保在处理期间没有其他人进行更改。”

对我来说，这种行为非常聪明，也为PDO提供了足够的灵活性以应对每个数据库，毕竟这是目的。但如果确实是这样，那么它的文档记录就相当不足，并且应该被称为其他名称，以避免与真正的数据库事务混淆，后者并不意味着锁定。

如果您需要高并发的工作负载并希望获得确定性，则Charles的答案可能是最好的选择；使用显式查询手动完成数据库操作，然后您可以按照数据库的文档进行操作。

更新 我已经运行了一个生产服务器，使用PDO事务功能一段时间，最近使用了AWS的Aurora数据库（完全兼容MySQL，但构建为自动缩放等）。我已经向自己证明了这两点： - 使用PDO :: beginTransaction（）可以使事务（仅能提交所有数据库更改）正常工作。简而言之，我知道许多脚本在其数据库选择/更新过程中失败了，但数据完整性已得到维护。 - 没有发生表锁定，我有一个索引重复错误来证明这一点。

因此，进一步得出结论，这些函数的行为似乎会根据数据库引擎（以及可能的其他因素）而发生变化。据我所知，无论是从经验还是文档上看，都没有办法通过编程方式知道正在发生什么...哇...

- Jamie Robinson

---

"...在编程中，没有办法以程序的方式知道发生了什么...哇..." - 这是我现在在Stack上最喜欢的引语。 - Richard Tyler Miles

Answer 3

在MySQL中，开始一个事务与关闭自动提交不同，因为它涉及到LOCK/UNLOCK TABLES的工作方式。在MySQL中，LOCK TABLES会提交任何打开的事务，但关闭自动提交并没有实际开始一个事务。MySQL就是这样有趣。

在PDO中，使用beginTransaction开始一个事务并没有实际开始一个新的事务，它只是关闭了自动提交。在大多数数据库中，这是合理的，但它可能会对MySQL的上述行为产生副作用。

你可能不应该依赖这种行为以及它与MySQL的怪癖相互作用的方式。如果你要处理MySQL的表锁定和DDL的行为，你应该避免使用它。如果你想关闭自动提交，可以手动关闭它。如果你想打开一个事务，可以手动打开。

当不与MySQL的奇特行为一起使用时，可以自由混合PDO API方法来处理事务和SQL命令。

- Charles

---

开始一个事务与关闭自动提交是不同的，但使用PDO时并不是。当您调用[PDO :: beginTransaction（）]（http://php.net/manual/en/pdo.begintransaction.php）时，它会关闭自动提交。 - Olhovsky

好的，我已经相应地更新了我的答案。我迫不及待地等待MySQL在事务内获得合理的DDL和锁行为的那一天......也许并不是。我已经在一段时间前转向Postgres了。 - Charles

请问您能否查看一下这个帖子？https://dev59.com/X6fja4cB1Zd3GeqPw4S8 我仍然无法理解使用PDO::beginTransaction()时，是否需要加上$this->pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,0);的区别。您能帮忙解答一下吗？非常感谢！ - tonix