MySQLi commit - Commit Transaction

Learn MySQLi commit() method. Commit the current transaction to the database and save changes permanently using PHP. This tutorial explores how to use mysqli_commit() to manage transactions effectively.

Introduction

In database management, transactions allow multiple queries to run as a single unit of work. If any part of the transaction fails, you can roll back all changes, maintaining data integrity. The MySQLi commit() method is used to save all changes made during the current transaction permanently to the database.

Transactions are crucial for applications where data accuracy is critical, such as banking, e-commerce, or any system with multi-step data operations. Using commit(), developers explicitly confirm the successful completion of a transaction.

Prerequisites

Basic knowledge of PHP and MySQL.
MySQL server installed and running (version 5.0+ recommended).
PHP environment with MySQLi extension enabled.
Access to a MySQL database with appropriate user privileges.
Understanding of PHP procedural or object-oriented MySQLi usage.

Setup Steps

Create a database and a table for testing:

CREATE DATABASE testdb;
USE testdb;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50) UNIQUE
);

Connect to the MySQL database using PHP MySQLi:

$mysqli = new mysqli("localhost", "username", "password", "testdb");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

Enable autocommit mode off to start transaction:

$mysqli->autocommit(FALSE);

How MySQLi commit() Method Works

The commit() method commits the current database transaction. In the default autocommit mode, every SQL statement is committed immediately. To manage transactions manually, you disable autocommit and explicitly call commit() to save changes.

Syntax

bool mysqli::commit ( void )

Returns: TRUE on success or FALSE on failure.

Explained Example

This example demonstrates inserting two users into a table within a transaction. If both insertions succeed, the transaction is committed. Otherwise, it rolls back.

<?php
$mysqli = new mysqli("localhost", "username", "password", "testdb");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Turn off autocommit mode to start the transaction
$mysqli->autocommit(FALSE);

try {
    $sql1 = "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')";
    $sql2 = "INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')";

    if (!$mysqli->query($sql1)) {
        throw new Exception("Error: " . $mysqli->error);
    }

    if (!$mysqli->query($sql2)) {
        throw new Exception("Error: " . $mysqli->error);
    }

    // Commit the transaction
    if ($mysqli->commit()) {
        echo "Transaction committed successfully.";
    } else {
        throw new Exception("Commit failed");
    }
} catch (Exception $e) {
    // Rollback the transaction
    $mysqli->rollback();
    echo "Transaction rolled back: " . $e->getMessage();
}

$mysqli->close();
?>

Explanation:

Autocommit is turned off to start the transaction.
Two insert queries are run.
If either query fails, an exception is thrown, and the transaction is rolled back.
If both succeed, commit() saves the changes permanently.

Best Practices

Always disable autocommit before managing transactions manually.
Use try-catch blocks for error handling to catch failures and rollback properly.
Commit only after all related queries succeed.
Rollback transactions if any query fails to maintain data integrity.
Close the connection when done to release resources.
Test transaction logic thoroughly to avoid unexpected data issues.

Common Mistakes

Forgetting to disable autocommit mode before calling commit().
Not rolling back on failures, potentially leaving partial changes saved.
Assuming autocommit mode handles transactions automatically.
Failing to check the return value of commit() for success.
Running queries outside the transaction management flow.

Interview Questions

Junior Level

Q1: What does the mysqli_commit() method do?
A: It saves all changes made during the current transaction permanently to the database.
Q2: How do you start a transaction in MySQLi?
A: By turning off autocommit mode using $mysqli->autocommit(FALSE);.
Q3: What happens if you don’t call commit() after disabling autocommit?
A: The changes won’t be saved permanently until you call commit() or the connection closes, risking data loss.
Q4: Which PHP extension provides the commit() method?
A: The MySQLi extension.
Q5: Can you commit multiple queries at once?
A: Yes, by running queries inside a transaction and then calling commit() once all succeed.

Mid Level

Q1: Explain the difference between autocommit mode ON and OFF in MySQLi.
A: Autocommit ON commits every SQL statement immediately; autocommit OFF lets you manually commit or rollback transactions.
Q2: How do you handle errors when using commit() in PHP?
A: Use try-catch blocks to detect query failures and call rollback() if commit() fails.
Q3: What is the default autocommit behavior in MySQLi?
A: By default, autocommit is enabled (ON).
Q4: Is commit() available in both procedural and object-oriented MySQLi?
A: Yes, it’s available as mysqli_commit($link) procedure and $mysqli->commit() in OO style.
Q5: What happens if you close the MySQLi connection before calling commit() after disabling autocommit?
A: The transaction is automatically rolled back, and changes are lost.

Senior Level

Q1: How does MySQLi handle savepoints in transactions, and how is it related to the commit() method?
A: Savepoints allow partial rollbacks within a transaction. commit() finalizes all changes since the transaction began or since the last savepoint.
Q2: In a high concurrency environment, what considerations affect the use of commit() in MySQLi?
A: Transactions should be short to reduce locking time; ensure proper isolation levels to prevent deadlocks before committing.
Q3: How would you ensure atomicity using MySQLi transactions and commit() with multiple related queries?
A: Disable autocommit, execute all queries, check for errors, call commit() if all succeed, otherwise rollback.
Q4: How can you handle multi-database transactions given MySQLi’s single connection limitation when using commit()?
A: Use distributed transaction managers or 2-phase commits externally, since MySQLi itself does not support distributed transactions directly.
Q5: Discuss the impact of exceptions thrown after commit(), and how to handle them properly.
A: Once commit() is called, changes are permanent; subsequent exceptions cannot rollback the transaction. Use error management to ensure commit only after safe operations.

FAQ

Q: What if commit() returns FALSE?
A: It indicates the commit failed. You should handle the failure by logging errors and possibly retry or rollback the transaction.
Q: Does commit() end the transaction?
A: Yes, after commit(), the database saves changes and closes the transaction.
Q: Can I use commit() without disabling autocommit?
A: No, because with autocommit ON, each query is immediately committed automatically.
Q: How do I rollback a transaction?
A: Use $mysqli->rollback(); after disabling autocommit but before commit.
Q: Does PHP automatically rollback uncommitted transactions on script termination?
A: Yes, when the connection is closed or the script ends, uncommitted transactions are rolled back.

Conclusion

The MySQLi commit() method is essential for managing transaction control in PHP applications. Manually handling transactions with commit() and rollback() ensures database consistency and reliable data operations. Always remember to disable autocommit before starting transactions, check for errors carefully, and commit only after all operations succeed. This practice is fundamental for building robust, enterprise-ready applications that interact with MySQL databases.

MySQLi commit Method