PDO, mysql, transactions and table locking

Question

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;

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

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,

Answer 1

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. So, you'd want to run your code as-is, not using the PDO helper functions.

You can freely mix the PDO API methods of working with transactions and SQL commands otherwise.