search

php / doc-en

English PHP documentation
505 stars 736 forks source link

PHP8 throws exception on commit() in PDO transactions #2868

Open Ome-Ko opened 1 year ago

Ome-Ko commented 1 year ago

Default behaviour for PDO-transactions is and has been to auto-commit. From PHP8 onwards an exception is thrown on a "manual" commit() call. The example on https://www.php.net/manual/en/pdo.transactions.php would result in "Failed: There is no active transaction" which is (also) a bit of a misleading error.

The $dbh->commit(); here (or in your code) should prolly be removed.

damianwadley commented 1 year ago

Everything I can see says the exact opposite: if you beginTransaction() and don't commit manually, it will be rolled back automatically. Not committed automatically, and certainly not that committing manually will error.

I assume you encountered this while working on some code of your own. Are you sure that there is an active transaction, as issued by beginTransaction or the equivalent BEGIN TRANSACTION statement, at the time of the call to commit()?

Ome-Ko commented 1 year ago

Did some reading up and the issue is not with with autocommit but with MySQL implicit commits. I'm using the transaction for dropping and creating tables which result in ending the transaction. So the exception seems to be accurate indeed.

damianwadley commented 1 year ago

Ah, yes, that is true: have to stick to regular DDM statements during transactions, as DDLs need to make modifications that kinda break the ability to have a transaction going.

If you haven't seen it yet, there is a list of such DDL statements at https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

Ome-Ko commented 1 year ago

Just saw that. Maybe mentioning this in the docs could safe some time for all parties involved. :-) Thanks for helping out!

damianwadley commented 1 year ago

It, uh, kinda already is mentioned...

https://www.php.net/manual/en/pdo.begintransaction.php

Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary.

Ome-Ko commented 1 year ago

Great. I never got beyond https://www.php.net/manual/en/pdo.transactions.php :-)

damianwadley commented 1 year ago

Funny, I was just thinking about that page...

If we have that about transactions and autocommits, it would make sense for details like "some statements may auto-commit a transaction" to be included there. After all, it's going through a lot of effort to talk about how transactions work, so what's another couple sentences?

I think that doing so would also mean it's not necessary for beginTransaction to have to talk about it. Or, for that matter, rollback (with the same statement) and commit (which, for some reason, instead has a note at the bottom).

All in all, it's worth giving a second thought.

Ome-Ko commented 1 year ago

It got me fooled (but I'm as outdated as PHP 7 + easy to fool).