Guide to MySQLi Transactions in PHP

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();
    echo "Transaction failed: " . $e->getMessage();
}

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();
    echo "Transaction failed: " . $e->getMessage();
}

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();
    echo "Transaction failed: " . $e->getMessage();
}

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();
    echo "Transaction failed: " . $e->getMessage();
}

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

Subscribe to our YouTube Channel here