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

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.