autocommit

This article covers the mysqli_autocommit() function in PHP, which turns MySQLi's autocommit mode on or off. You'll learn what autocommit does, both the object-oriented and procedural syntax, and how to combine it with mysqli_commit() and mysqli_rollback() to run safe, all-or-nothing transactions.

What autocommit means

A transaction is a group of SQL statements that should either all succeed or all fail together. By default, MySQLi runs in autocommit mode: every single statement is its own transaction and is saved (committed) to the database the moment it runs. There is no way to undo it afterward.

mysqli_autocommit() lets you turn that automatic behavior off. Once autocommit is disabled, your statements are held in a pending transaction until you decide what to do with them:

• Call mysqli_commit() to make all pending changes permanent.
• Call mysqli_rollback() to discard them, leaving the database untouched.

This is what makes "all-or-nothing" updates possible — for example, transferring money between two accounts where both the debit and the credit must succeed, or neither should.

How to use the mysqli_autocommit() function

Using the mysqli_autocommit() function is very simple. You just need to call the function and pass in a valid MySQLi connection and a boolean value that represents the autocommit state.

Parameters:

• connection (procedural) / $mysqli (OOP): The MySQLi connection object.
• mode (bool): TRUE to enable autocommit, FALSE to disable it.

Return value: Returns TRUE on success, FALSE on failure.

Note: MySQLi supports both object-oriented and procedural syntax. The OOP method is $mysqli->autocommit($mode); the procedural equivalent is mysqli_autocommit($mysqli, $mode). Both do exactly the same thing — pick whichever style your codebase already uses. See the PHP MySQLi overview for more on the two styles.

Here is a basic example using the object-oriented style:

How to use the mysqli_autocommit() function

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

$mysqli->autocommit(FALSE);

$mysqli->query("INSERT INTO users (name, email) VALUES ('John', '[email protected]')");
$mysqli->query("UPDATE users SET name='John Doe' WHERE id=1");

$mysqli->commit();

$mysqli->autocommit(TRUE);

$mysqli->close();
?>

In this example, we create a new MySQLi object and disable autocommit by calling the autocommit() function with an argument of FALSE. We then execute two queries to insert and update data in a users table. We commit the transaction by calling the commit() function of the MySQLi object.

We then re-enable autocommit by calling the autocommit() function with an argument of TRUE. Finally, we close the MySQLi connection using the close() method of the MySQLi object.

Procedural syntax

The same logic written in the procedural style passes the connection as the first argument to each function:

<?php
$link = mysqli_connect("localhost", "username", "password", "database");
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    exit();
}

// Turn off autocommit so the statements form one transaction
mysqli_autocommit($link, FALSE);

mysqli_query($link, "INSERT INTO users (name, email) VALUES ('John', '[email protected]')");
mysqli_query($link, "UPDATE users SET name='John Doe' WHERE id=1");

mysqli_commit($link);          // make both changes permanent

mysqli_autocommit($link, TRUE); // restore default behavior
mysqli_close($link);
?>

Rolling back on error

The real value of disabling autocommit is the ability to undo a partly finished transaction when something goes wrong. The example below wraps two related updates in a transaction and rolls back if either query fails, so the database is never left in a half-updated state:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

$mysqli->autocommit(FALSE); // begin a transaction

$ok = $mysqli->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
$ok = $ok && $mysqli->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");

if ($ok) {
    $mysqli->commit();
    echo "Transfer completed.";
} else {
    $mysqli->rollback(); // discard both updates
    echo "Transfer failed and was rolled back.";
}

$mysqli->autocommit(TRUE);
$mysqli->close();
?>

Because autocommit is off, neither UPDATE is saved until commit() is called. If the second query fails, rollback() discards the first one too, guaranteeing that money is never deducted without being credited.

Advanced usage

The mysqli_autocommit() function operates at the connection level. When you switch autocommit from FALSE to TRUE, MySQLi automatically commits any pending transaction. This behavior is useful when managing multiple independent transactions sequentially within the same script. Here is an example:

Advanced usage of PHP autocommit()

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

// Start and commit the first transaction
$mysqli->autocommit(FALSE);
$mysqli->query("INSERT INTO users (name, email) VALUES ('John', '[email protected]')");
$mysqli->commit();

// Start and commit the second transaction
$mysqli->autocommit(FALSE);
$mysqli->query("UPDATE users SET name='John Doe' WHERE id=1");
$mysqli->commit();

// Re-enable autocommit for subsequent queries
$mysqli->autocommit(TRUE);

$mysqli->close();
?>

In this example, we disable autocommit and execute an INSERT query. We then explicitly commit the first transaction. Next, we disable autocommit again to start a second transaction, execute an UPDATE query, and commit it. Finally, we re-enable autocommit and close the MySQLi connection.

Conclusion

The mysqli_autocommit() function gives you control over when MySQLi saves your changes. Disable it with FALSE to start a transaction, run your statements, then either mysqli_commit() to make them permanent or mysqli_rollback() to discard them. This pattern is essential whenever several statements must succeed or fail as a unit. Always re-enable autocommit (or close the connection) when you are done, so later queries behave as expected and your data stays consistent.

Practice