PDO、mysql、事务和表锁定 [英] PDO, mysql, transactions and table locking

问题描述

为了好玩，我将应用程序中的 mysqli 扩展替换为 PDO.

For fun I am replacing the mysqli extension in my app with PDO.

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

Once in awhile I need to use transactions + table locking.

在这些情况下，根据mysql手册，语法需要有点不同.与其调用 START TRANSACTION，不如这样做...

In these situations, according to the mysql manual, the syntax needs to be a bit different. Instead of calling START TRANSACTION, you do it like so...

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 吗?或者我应该手动发送sqlSET autocommit = 0; ... etc".

My question is, how does this interact with PDO::beginTransaction? Can I use PDO::beginTransaction in this case? Or should I manually send the sql "SET autocommit = 0; ... etc".

谢谢你的建议，

推荐答案

在 MySQL 中，由于 LOCK/UNLOCK TABLES 的工作方式，开始事务与关闭自动提交不同.在 MySQL 中，LOCK TABLES 提交任何打开的事务，但关闭自动提交实际上并不是启动事务.MySQL 在这方面很有趣.

In MySQL, beginning a transaction is different than turning off autocommit, due to how LOCK/UNLOCK TABLES works. In MySQL, LOCK TABLES commits any open transactions, but turning off autocommit isn't actually starting a transaction. MySQL is funny that way.

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

In PDO, starting a transaction using beginTransaction doesn't actually start a new transaction, it just turns off autocommit. In most databases, this is sane, but it can have side effects with MySQL's mentioned behavior.

您可能不应该依赖这种行为以及它如何与 MySQL 的怪癖进行交互.如果您要处理 MySQL 的表锁定和 DDL 行为，您应该避免它.如果您想关闭自动提交，请手动将其关闭.如果要开启交易，请手动开启交易.

You probably should not rely on this behavior and how it interacts with MySQL's quirks. If you're going to deal with MySQL's behavior for table locking and DDL, you should avoid it. If you want autocommit off, turn it off by hand. If you want to open a transaction, open a transaction by hand.

您可以自由地混合使用 PDO API 方法来处理事务和 SQL 命令，而无需以其他方式处理 MySQL 的奇怪问题.

You can freely mix the PDO API methods of working with transactions and SQL commands when not otherwise working with MySQL's oddities.

这篇关于PDO、mysql、事务和表锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！