The MySQL server supports transactions depending on the storage engine used. Since MySQL 5.5, the default storage engine is InnoDB. InnoDB has full ACID transaction support.
Transactions can either be controlled using SQL or API calls. It
is recommended to use API calls for enabling and disabling the
autocommit mode and for committing and
rolling back transactions.
Example 7.32 Setting
autocommit mode with SQL and through the API
<?php mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $mysqli = new mysqli("example.com", "user", "password", "database"); /* Recommended: using API to control transactional settings */ $mysqli->autocommit(false); /* Won't be monitored and recognized by the replication and the load balancing plugin */ $mysqli->query('SET AUTOCOMMIT = 0');
Optional feature packages, such as the replication and load
balancing plugin, can easily monitor API calls. The replication
plugin offers transaction aware load balancing, if transactions
are controlled with API calls. Transaction aware load balancing
is not available if SQL statements are used for setting
autocommit mode, committing or rolling back a
Example 7.33 Commit and rollback
<?php mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $mysqli = new mysqli("example.com", "user", "password", "database"); $mysqli->autocommit(false); $mysqli->query("INSERT INTO test(id) VALUES (1)"); $mysqli->rollback(); $mysqli->query("INSERT INTO test(id) VALUES (2)"); $mysqli->commit();
Please note, that the MySQL server cannot roll back all statements. Some statements cause an implicit commit.