PDO::ATTR_AUTOCOMMIT ignores non-transactional INSERT/UPDATE

Question

Been scratching my head on this for a while....

I have a PDO object with pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,0); as I want to use FOR UPDATE with some InnoDB tables. Reading the MySQL documentation, FOR UPDATE will only lock read rows if:

1. You are in a transaction
2. You are not in a transaction and set autocommit=0 has been issued

So, I am using ATTR_AUTOCOMMIT to allow the PDO object to lock rows. In either case, this is causing INSERT and UPDATE statements to not apply. These statements are nothing to do with FOR UPDATE they are just running through the same PDO object with prepared statements.

My MySQL query log looks like:

xxx    Connect   user@host
xxx    Query     set autocommit=0
xxx    Query     INSERT INTO foo_tbl (bar, baz) VALUES ('hello','world')
xxx    Quit

PHP/PDO doesn't complain, but selecting from the table shows that the data hasn't been written.

The queries that I am running have been run thousands of time prior; only the ATTR_AUTOCOMMIT change has been made. Removing that option makes everything work again. Transactions are working fine with the option autocommit=0 too.

Are there additional calls that need making on the PDO object (commit() complains rightly that it isn't in a transaction) to make the changes stick? Basically, I want a plain PDO object but with the option to lock rows outside of transactions for InnoDB tables (the background to why is too long and boring for here).

I'm sure this is something stupid I am missing scratches head

Answer 1

    $db = new PDO('mysql:dbname=test');
    $db->setAttribute(PDO::ATTR_AUTOCOMMIT,0);
    var_dump($db->query('SELECT @@autocommit')->fetchAll()); //OK
    $db->query("INSERT INTO foo (bar) VALUES ('a');");
    $db->query("COMMIT;");//do by SQL rather then by interface / PDO-method

But essentially, you're in a transaction (you just haven't started it with PDO), a rollback etc. is also still available. It's quite debatable whether this is a bug (not being able to call commit() directly).