PDO :: ATTR_AUTOCOMMIT ignores non-transactional INSERT / UPDATE

Question

I scratch my head for a while ...

I have a PDO object with pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,0); since I want to use FOR UPDATE with some InnoDB tables. When reading the MySQL documentation, FOR UPDATE will only block read lines if:

• You are in a transaction
• You are not in a transaction and set autocommit=0 was released

So, I use ATTR_AUTOCOMMIT to allow the PDO to block rows. In any case, this causes the INSERT and UPDATE statements to not apply. These statements have nothing to do with FOR UPDATE ; they simply work through the same PDO with prepared statements.

My MySQL query log looks like this:

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

PHP / PDO does not complain, but selecting from a table indicates that no data was written.

The queries that I run were completed in seconds; only the ATTR_AUTOCOMMIT change has been ATTR_AUTOCOMMIT . Removing this option makes everything work again. Transactions work fine with the option autocommit=0 .

Are there additional calls that require creating a PDO on the object ( commit() , is it correct that this is not a transaction) in order to make changes? Basically, I want a simple PDO object, but with the ability to lock rows outside of transactions for InnoDB tables (the background for why is too long and boring for here).

I'm sure this is something stupid. I miss the scratches.

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 are in a transaction (you just did not start it with PDO), rollback, etc. also still available. It is highly debatable whether this error is (not being able to call commit() directly).