How to start and end transaction in mysqli?

How to start and end transaction in mysqli?

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

The Mechanics of MySQLi Transactions: Start, Commit, and Rollback

Your understanding of the basic flow is correct, but there is a significant architectural flaw in the pattern you presented that could cause silent data loss or inconsistent states in a real application. You identified autocommit(FALSE) as the trigger to start a transaction and commit() as the point of success. However, relying solely on these two functions without handling failures leaves your database vulnerable.

In MySQLi, transactions are not just about setting a flag; they are about atomicity. When you execute $mysqli->autocommit(FALSE), you explicitly tell the server to stop auto-committing queries immediately. Any subsequent SQL statements are buffered within the current transaction context until you decide to either finalize them with commit() or abort them entirely with rollback().

The critical missing piece in your example is error handling. In a production environment, the moment one of your UPDATE statements fails—whether due to a syntax error, a foreign key constraint violation, or a deadlock—the entire transaction must be rolled back. If you simply proceed to the next line and call commit() regardless of whether an error occurred, you risk committing a partial state where some rows were updated but others were not. This violates the ACID properties that make transactions useful in the first place.

The Atomic Unit of Work

Consider the logic inside your block:

$mysqli->autocommit(FALSE);
$mysqli->query('UPDATE `table` SET `col`=2');
$mysqli->query('UPDATE `table1` SET `col1`=3;'); // Note the semicolon here is syntax error for query()
$mysqli->commit();

There are two immediate technical issues here. First, passing a string with a trailing semicolon to $mysqli->query() will throw an exception or trigger a warning because the semicolon is not part of the SQL statement syntax expected by the function. Second, and more importantly, there is no check for errors between the updates and the commit.

If the second update fails, your code should never reach $mysqli->commit(). Instead, you must catch the exception or check the error status immediately after each query. If an error occurs, you call $mysqli->rollback() to undo all changes made since autocommit(FALSE) was set. Failing to do so results in data inconsistency: table is updated, but table1 remains unchanged because its update failed.

Handling Exceptions Correctly

To make this robust, you need to wrap your transaction logic in a try-catch block. This ensures that if any query within the transaction fails, the database state is restored to what it was before the transaction started.

try {
    $mysqli->autocommit(FALSE); // Start transaction
    
    // Execute updates
    $mysqli->query('UPDATE `table` SET `col`=2');
    $mysqli->query('UPDATE `table1` SET `col1`=3');
    
    // Only commit if no errors occurred
    $mysqli->commit();
} catch (mysqli_sql_exception $e) {
    // Rollback on any error to maintain atomicity
    $mysqli->rollback();
    // Handle the exception or log it appropriately
    throw $e; 
}

// Resume normal autocommit mode for subsequent queries
$mysqli->autocommit(TRUE);

Notice that $mysqli->autocommit(TRUE) is called after the try-catch block. This is essential because once a transaction ends (via commit or rollback), you usually want to return to the default behavior where each query commits immediately. If you leave autocommit(FALSE) set, every subsequent query will wait for an explicit command, which can lead to long-running transactions and lock contention issues.

Transaction Scope and Isolation

It is also worth noting that a transaction begins exactly when the first statement is executed after disabling autocommit, not necessarily at the line where autocommit(FALSE) is called if you have already issued queries in the current scope. However, calling autocommit(FALSE) effectively resets the session to a non-autocommit state, ensuring that all future statements belong to a new transactional unit until resolved.

If an exception occurs during the execution of multiple queries, only the most recent statement might be rolled back depending on the isolation level and storage engine (specifically MyISAM does not support transactions, while InnoDB does). Assuming you are using InnoDB, the rollback operation will undo all changes made since the transaction started.

For larger applications, managing these manual commits and rollbacks manually can become error-prone. Frameworks like Laravel provide a convenient DB::transaction() helper that automatically handles the start, commit, and rollback logic based on exception handling. If you are building a custom application without such helpers, strict adherence to try-catch patterns is non-negotiable for data integrity.

Our network