How to start and end transaction in mysqli?

Update Novembre 2020: @Dharman gave a better answer with more details about transactions in mysqli, just check it instead: https://stackoverflow.com/a/63764001/569101 👇

Well according to the php doc, you're right.

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("CREATE TABLE Language LIKE CountryLanguage");

/* set autocommit to off */
$mysqli->autocommit(FALSE);

/* Insert some values */
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");

/* commit transaction */
$mysqli->commit();

/* drop table */
$mysqli->query("DROP TABLE Language");

/* close connection */
$mysqli->close();
?>

In the example above:

• the CREATE TABLE is auto committed because it's the default behaviour.
• the INSERT INTO aren't auto committed because of the autocommit(FALSE).
• the DROP TABLE is auto committed because the autocommit(FALSE) was reset by the ->commit();.

j0k is mainly right, except in the drop table.

The auto commit is not turned on with the ->commit()

Instead, the DROP TABLE is a DDL query, and DDL queries are always implicitly committed and will commit all your previously non committed work.

So, if you did not commit the work, the DDL query would force this commit.

How to use transactions in mysqli?

Prerequisite

In order for the transactions to behave properly you should enable exception error reporting. Otherwise mysqli will not report errors and the transaction will not be performed correctly. Alternatively, you could manually check each query, but that is not recommended. To connect properly with mysqli use the following 3 lines:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'user', 'pass', 'dbname');
$mysqli->set_charset('utf8mb4'); // always set the charset

Transactions only work with transactional tables. Make sure that your table storage engine supports transactions. For example, MyISAM ignores the commit/rollback.

Transactions

There are two possible ways to create a transaction using mysqli. By default all queries/statements are committed as soon as they are performed. You can either switch autocommit off or use a one-time-only transaction.

Transactions are committed to the database in the following situations:

• when calling commit
• after setting autocommit=1
• when starting another transaction
• when performing DDL query
• and in a few other situations. For more information see Statements That Cause an Implicit Commit

Using autocommit(false)

If you turn autocommit off, you decide when you want to commit, but calling commit() does not switch autocommit back on.

//Start transaction
$mysqli->autocommit(false);

$mysqli->query('INSERT INTO director(name) VALUE("Steven Spielberg")');

$directorId = $mysqli->insert_id;
$movieTitle = 'Jurassic Park';
$stmt = $mysqli->prepare('INSERT INTO movie(title, directorId) VALUE(?,?)');
$stmt->bind_param('ss', $movieTitle, $directorId);
$stmt->execute();

$mysqli->commit();
// Changes are committed, but autocommit is not switched back on

// Following queries are still transactional.
// They will not be committed unless you call commit or switch autocommit back on
$mysqli->query('INSERT INTO director(name) VALUE("James Cameron")');

$directorId = $mysqli->insert_id;
$movieTitle = 'Titanic';
$stmt = $mysqli->prepare('INSERT INTO movie(title, directorId) VALUE(?,?)');
$stmt->bind_param('ss', $movieTitle, $directorId);
$stmt->execute();

$mysqli->autocommit(true);
// All queries are committed and everything that follows will be immediately committed.

Using begin_transaction()

You can start a one-time-only transaction using begin_transaction(). This does not set autocommit=false so when you call commit() you end the transaction without starting a new one.

//Start transaction 
$mysqli->begin_transaction();

$mysqli->query('INSERT INTO director(name) VALUE("Steven Spielberg")');

$directorId = $mysqli->insert_id;
$movieTitle = 'Jurassic Park';
$stmt = $mysqli->prepare('INSERT INTO movie(title, directorId) VALUE(?,?)');
$stmt->bind_param('ss', $movieTitle, $directorId);
$stmt->execute();

$mysqli->commit();
// Changes are committed and the transaction has ended

// Following queries will be committed one by one as soon as they are peformed.
$mysqli->query('INSERT INTO director(name) VALUE("James Cameron")');

$directorId = $mysqli->insert_id;
$movieTitle = 'Titanic';
$stmt = $mysqli->prepare('INSERT INTO movie(title, directorId) VALUE(?,?)');
$stmt->bind_param('ss', $movieTitle, $directorId);
$stmt->execute();

Performing DDL statements

Some SQL statements trigger an explicit commit but do not affect the value of autocommit.

//Start transaction 
$mysqli->autocommit(false);

$mysqli->query('INSERT INTO director(name) VALUE("Steven Spielberg")');

$directorId = $mysqli->insert_id;
$movieTitle = 'Jurassic Park';
$stmt = $mysqli->prepare('INSERT INTO movie(title, directorId) VALUE(?,?)');
$stmt->bind_param('ss', $movieTitle, $directorId);
$stmt->execute();

// The following will call commit but it will not set autocommit=true
$mysqli->query('TRUNCATE TABLE movie_genre');
// if you want to switch autocommit back on, you have to call: 
$mysqli->autocommit(true);

Rollback

If an exception occurs then PHP will end execution of the script and the code will never reach the commit statement. However, in some situations, you might want to roll back the transaction explicitly, for example to avoid calling commit accidentally somewhere else in the code.

Here is an example of what such a transaction would look like. The second query tries to insert into a non-existent table which means that mysqli will throw an exception. Instead of letting PHP script die, we catch the exception and roll back the transaction. The value 4 will never be inserted into the database because both queries were rolled back.

try {
    // Start transaction
    $mysqli->begin_transaction();

    $mysqli->query('INSERT INTO some_table(col2) VALUE(4)');
    $mysqli->query('INSERT INTO does_not_exist(col2) VALUE(4)');

    // Commit changes
    $mysqli->commit();
} catch (\Throwable $e) {
    // Something went wrong. Rollback
    $mysqli->rollback();
    // Rethrow the exception so that PHP does not continue
    // with the execution and the error can be logged in the error_log
    throw $e;
}