pdo mysql 事务,PDO，mysql，事务和表锁定

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

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

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;

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".

Thanks for the advice,

解决方案

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.

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.

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.

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