How do I make sure (using PHP and MySQL) that when I commit a transaction, the entire transaction is successful?

Question

Suppose I'm a PHP application and I start an innodb transaction with mysqli_begin_transaction and it returns true. Then I do a series of inserts. As far as I am aware --- as a PHP application ---, all inserts went in perfectly. (Assume I checked all possible error codes.) It is my understanding that before I commit, the entire transaction could be rolled back --- due to a deadlock or something. Suppose that before I commit I delay a period of time doing something not MySQL related. Suppose further that --- during this delay --- MySQL gets into a situation where it must roll back my entire transaction. I claim I wouldn't know that it was rolled back because I'm not at this moment invoking any MySQL procedure. (Would the MySQL driver in PHP somehow discover so and throw an exception? I fear it would not, but I don't know.) When I'm ready to commit, it seems to me that MySQL will have no transaction pending relative to my connection, so I will be committing a new and empty transaction. MySQL reports no error when I commit an empty transaction, so I have no way of noticing that my entire transaction was rolled back.

Question. What is a small sample of code where I can be sure that the responsibility of my data in completely in MySQL's hands and not on mine? (In other words, how do I make sure (using PHP and MySQL) that when I commit a transaction, the entire transaction is successfully in MySQL's hands?)

Reference. Previously, I used to trust the return boolean from a mysqli_commit. It is my understanding now that I cannot trust it. The answer in this linked-question might be saying that all I need is that try-catch with mysqli_report not silenced. But I'm not convinced. Since I cannot easily reproduce an implicit rollback, I'm looking for two things --- (1) a sample of code where I can be sure the commit takes place as expected, (2) a deterministic way to reproduce an implicit rollback, if possible. It seems to me that (2) is not easy, so I'm researching (1) first.

Versions. I'm running MySQL 5.7.38-log and PHP 7.4.

Answer 1

A DB transaction, as the name implies, happens in the database. PHP has no way of knowing what MySQL does unless MySQL informs the client (PHP) about it. MySQL can only inform the client when it performs an action on the server. So in this regard, communication between PHP and MySQL resembles HTTP communication where the client makes a request and expects a response.

DB transactions ensure that the data is committed as a single atomic operation (part of ACID). This means that all DML operations within a transaction are treated as a single DML operation even when made up of multiple DML commands. Either all of them succeed or none of them succeeds. You can trust that the database will not commit part of the data.

So as we have established in the previous two paragraphs, MySQL will always tell you whether an operation succeeded or failed and it will never commit part of the transaction, the question arises: when and how can PHP tell MySQL to commit the transaction? The answer is pretty simple: send COMMIT SQL command whenever all DML operations you wanted to be part of the transaction were executed successfully.

Using mysqli (it's pretty much the same in PDO), it would look something like this:

$mysqli->begin_transaction();
$stmt = $mysqli->prepare("INSERT INTO foo(text) VALUES(?)");
$stmt->execute(['bar']);
// more DML operations
$mysqli->commit();

That's it! If any of the prepared statements fail, PHP will throw an exception based on the error received as a response from MySQL. Just make sure you have mysqli error reporting enabled! If commit() fails, then the whole transaction fails (C in ACID).

Without error reporting enabled, you could have one of the queries fail without noticing it. If you'd proceed with a commit, you would end up committing changes without the one that failed.

The reason why we have a ROLLBACK command is that MySQL does not implicitly rollback the queries that were part of the transaction and have successfully been executed. As long as the connection session remains open, MySQL will wait for either COMMIT or ROLLBACK to end the transaction. If you would like to discard the changes and proceed with some other operations or even retry the whole transaction again using the same connection, you need to rollback the transaction. A try-catch in PHP is helpful to achieve this. For more information see my other answer: How to start and end transaction in mysqli?

---

A word of caution: DB transactions are ACID, but certain DDL commands trigger implicit commit. If you are unaware of this and you execute DDL command as part of your transaction, you could end the transaction prematurely and cause data inconsistency.

Answer 2

Do turn off "auto-reconnect". If the network hiccups in the middle of your transaction, the already-performed DMLs will be rolled back. But, after the auto-reconnect, you may be in autocommit mode and each DML is its own transaction.

Do test every DML and DDL statement. Do test after Commit, especially if using Galera and other clustering solutions.

In most cases, it is "correct" to jump back to the beginning of Begin and start over.

With MySQL 8.0, some DDL commands can be inside a transaction.