Guide to MySQLi Transactions in PHP

Commit or roll back a transaction

Wrap related changes in one transaction so they either all succeed or all roll back.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$connection->begin_transaction();

try {
    $connection->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $connection->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    $connection->commit();
} catch (mysqli_sql_exception $e) {
    $connection->rollback();
    throw $e;
}
?>

SQL concepts: UPDATE · WHERE · INSERT · DELETE

1. What are Transactions?

Transactions ensure a series of SQL operations are executed as a single unit. Either all operations succeed, or none of them do, which helps maintain data integrity. If an error occurs, the transaction can be rolled back to prevent partial changes.

2. Starting a Transaction in MySQLi

In MySQLi, you can start a transaction using the begin_transaction() method.

config.php Database connection object $connection is taken from config.php file.

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

3. Committing and Rolling Back Transactions

After performing database operations, you can either commit the transaction or roll it back in case of an error.

$connection->commit();    // Commit the transaction
$connection->rollback(); // Roll back if there is an error

4. TCL (Transaction Control Language)

Transaction Control Language (TCL) commands manage transactions within a database. The key TCL commands include:

COMMIT: Saves all changes made during the transaction permanently to the database.
ROLLBACK: Reverts the changes made during the transaction, undoing all modifications.
SAVEPOINT: Creates a point within a transaction that can be rolled back to without affecting the entire transaction.

5. Handling Exceptions in MySQLi Transactions

To manage errors, use try-catch blocks. This ensures that if an exception is thrown, the transaction will be rolled back to prevent incomplete changes.

try {
    $connection->begin_transaction();
    $stmt = $connection->prepare("UPDATE student SET mark = mark + 10 WHERE id = ?");
    $stmt->bind_param("i", $id);
    $id = 1;
    $stmt->execute();
    $connection->commit();
    echo "Number of records updated: " . $stmt->affected_rows;
} catch (Exception $e) {
    $connection->rollback();
    error_log($e->getMessage());
    echo "Transaction could not be completed.";
}

6. Savepoints in MySQLi Transactions

Savepoints allow partial rollbacks within a transaction, giving more flexibility when managing complex operations.

$connection->query("SAVEPOINT savepoint1");
// Perform additional operations
$connection->query("ROLLBACK TO savepoint1"); // Roll back to the savepoint
$connection->commit(); // Commit the final changes

7. Example: Inserting Multiple Records Using Transactions

Transactions are useful when inserting multiple records to ensure all records are successfully inserted. If an error occurs during any insert, all changes can be rolled back.

try {
    $connection->begin_transaction();
    $stmt = $connection->prepare("INSERT INTO student (name, class, mark, gender) VALUES (?, ?, ?, ?)");
    $stmt->bind_param("ssis", $name, $class, $mark, $gender);

    $name = 'Alice'; $class = 'Five'; $mark = 88; $gender = 'Female';
    $stmt->execute();
	echo "Number of records added : " . $stmt->affected_rows;
	echo "<br>Insert ID : ".$connection->insert_id;
    $name = 'Bob'; $class = 'Three'; $mark = 91; $gender = 'Male';
    $stmt->execute();

	echo "<br>Number of records added : " . $stmt->affected_rows;
	echo "<br>Insert ID : ".$connection->insert_id;
	$connection->commit();
    echo "<br>Records inserted successfully!";
} catch (Exception $e) {
    $connection->rollback();
    error_log($e->getMessage());
    echo "Transaction could not be completed.";
}

8. Example: Updating Multiple Records Using Transactions

This example demonstrates how to use transactions when updating multiple records, ensuring that either all updates occur or none at all.

try {
    $connection->begin_transaction();
    $stmt = $connection->prepare("UPDATE student SET mark = mark + 5 WHERE class = ?");
    $stmt->bind_param("s", $class);

    $class = '10th';
    $stmt->execute();

    $class = '12th';
    $stmt->execute();

    $connection->commit();
    echo "Marks updated successfully!";
} catch (Exception $e) {
    $connection->rollback();
    error_log($e->getMessage());
    echo "Transaction could not be completed.";
}

9. Example: Deleting Records Using Transactions

Use transactions to safely delete records. If any step in the process fails, you can roll back all changes.

try {
    $connection->begin_transaction();
    $stmt = $connection->prepare("DELETE FROM student WHERE id = ?");
    $stmt->bind_param("i", $id);
    
    $id = 3;
    $stmt->execute();
    echo "<br> Number of records deleted : " . $stmt->affected_rows;
    $connection->commit();
    echo "Record deleted successfully!";
} catch (Exception $e) {
    $connection->rollback();
    echo "Failed to delete record: " . $e->getMessage();
}

10. Nested Transactions and Complex Queries

Though MySQL doesn't support true nested transactions, you can simulate them using savepoints. This is useful when different parts of the code need to roll back independently of the overall transaction.

try {
    $connection->begin_transaction();
    
    // First operation
    $connection->query("INSERT INTO student (name, class, mark, gender) 
		VALUES ('John', '8th', 90, 'Male')");
    $connection->query("SAVEPOINT savepoint1");
    
    // Second operation, which might fail
    $connection->query("INSERT INTO student (name, class, mark, gender) 
		VALUES ('Jane', '9th', 85, 'Female')");
    
    // If something fails, rollback to savepoint1
    $connection->query("ROLLBACK TO savepoint1");
    
    // Commit the rest of the transaction
    $connection->commit();
} catch (Exception $e) {
    $connection->rollback();
    error_log($e->getMessage());
    echo "Transaction could not be completed.";
}

11. The Effect of DDL on Transactions

In MySQL, DDL (Data Definition Language) commands like `CREATE`, `ALTER`, and `DROP` automatically trigger a commit. This means that any transaction active before executing a DDL statement will be committed, and the DDL changes cannot be rolled back. As a result, DDL operations should be handled carefully within a transactional context.

12. Comparison Between DDL and DML in Transactions

The following table outlines the differences between DDL and DML (Data Manipulation Language) operations with respect to transactions:

Type of Operation	Definition	Transactional Behavior
DDL (Data Definition Language)	Includes commands like `CREATE`, `ALTER`, and `DROP` that define or modify database structures.	Implicitly commits any ongoing transaction. Cannot be rolled back. Modifies the database schema.
DML (Data Manipulation Language)	Includes commands like `INSERT`, `UPDATE`, and `DELETE` that manipulate data within tables.	Can be used within a transaction. Can be rolled back before commit. Only modifies data, not the schema.

13. Best Practices for Transaction Management

Keep transactions short to minimize locking and potential conflicts with other processes.
Always handle exceptions with try-catch blocks to ensure that transactions are rolled back when needed.
Use savepoints for better control over partial rollbacks in complex operations.
Avoid holding transactions open while waiting for user input, as this can lead to locking issues and degraded performance.

Conclusion

MySQLi transactions are crucial for ensuring the integrity and reliability of your database operations. By using transactions, you can prevent data inconsistencies, handle errors gracefully, and maintain data consistency. Always use proper error handling and rollback strategies to ensure smooth execution in production environments.

MySQL DUMP of student table

Podcast on MySQL database management using MySQLi connector

MYSQLI Functions mysqli_num_rows() Number of rows in result set SELECT query UPDATE query

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here