In this article, we will delve into transaction management in MySQLi, covering the fundamentals, essential commands, and practical examples to help you effectively manage transactions in your PHP applications.
Understanding Transactions
A transaction in a database system is a sequence of operations performed as a single logical unit of work. A transaction must adhere to the ACID properties to ensure data integrity:- Atomicity: Ensures that all operations within the transaction are completed successfully. If any operation fails, the entire transaction is aborted.
- Consistency: Ensures that the database transitions from one valid state to another.
- Isolation: Ensures that transactions are isolated from each other until they are completed.
- Durability: Ensures that once a transaction is committed, it is permanently recorded in the database.
Key Transaction Control Commands in MySQLi
BEGIN
The BEGIN command initiates a new transaction. In MySQLi, you can start a transaction using the begin_transaction method or the SQL command START TRANSACTION.COMMIT
The COMMIT command saves all changes made during the transaction to the database. Once committed, the changes become permanent and visible to other transactions.ROLLBACK
The ROLLBACK command undoes all changes made during the transaction. This is useful for reverting the database to its previous state in case of errors or inconsistencies.Using Transactions in MySQLi with PHP
To illustrate the use of transactions in MySQLi, let’s walk through a practical example involving a simple banking system. We’ll create a PHP script that handles money transfers between accounts, ensuring that all operations are performed atomically.Setting Up the Database
First, set up a MySQL database and create a table for storing account information. We’ll use an accounts table with fields for id, name, and balance.
CREATE DATABASE bank;
USE bank;
CREATE TABLE accounts (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
balance DECIMAL(10, 2)
);
INSERT INTO accounts (name, balance) VALUES ('Alice', 1000.00);
INSERT INTO accounts (name, balance) VALUES ('Bob', 1000.00);
PHP Script for Transaction Management
We’ll write a PHP script that transfers money from one account to another, ensuring that the transaction is atomic. If any part of the transaction fails, it will be rolled back to maintain data consistency.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "bank";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
function transferMoney($fromAccount, $toAccount, $amount) {
global $conn;
// Begin transaction
$conn->begin_transaction();
try {
// Deduct amount from sender's account
$sql = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("di", $amount, $fromAccount);
$stmt->execute();
if ($stmt->affected_rows === 0) {
throw new Exception("Sender's account not found or insufficient funds.");
}
// Add amount to receiver's account
$sql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("di", $amount, $toAccount);
$stmt->execute();
if ($stmt->affected_rows === 0) {
throw new Exception("Receiver's account not found.");
}
// Commit transaction
$conn->commit();
echo "Transaction successful!";
} catch (Exception $e) {
// Rollback transaction on error
$conn->rollback();
echo "Transaction failed: " . $e->getMessage();
}
$stmt->close();
}
// Example usage
transferMoney(1, 2, 100.00);
$conn->close();
?>
Explanation
- Connecting to the Database: Establish a connection to the MySQL database using MySQLi.
- Starting the Transaction: Use $conn->begin_transaction() to initiate a new transaction.
- Executing SQL Statements:
- Deduct the amount from the sender’s account.
- Add the amount to the receiver’s account.
- Error Handling: If any SQL operation fails, an exception is thrown.
- Committing the Transaction: If all operations succeed, $conn->commit() is called to save the changes.
- Rolling Back the Transaction: If an exception is caught, $conn->rollback() is called to revert the changes.
Advanced Transaction Management
In more complex scenarios, you might need to handle nested transactions or savepoints. MySQLi supports these advanced transaction control features as well.Nested Transactions and Savepoints
Savepoints allow you to set intermediate points within a transaction to which you can roll back without affecting the entire transaction. This is useful for partial error recovery.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "bank";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
function transferMoneyWithSavepoint($fromAccount, $toAccount, $amount) {
global $conn;
// Begin transaction
$conn->begin_transaction();
try {
// Deduct amount from sender's account
$sql = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("di", $amount, $fromAccount);
$stmt->execute();
if ($stmt->affected_rows === 0) {
throw new Exception("Sender's account not found or insufficient funds.");
}
// Set savepoint
$conn->savepoint("deducted");
// Add amount to receiver's account
$sql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("di", $amount, $toAccount);
$stmt->execute();
if ($stmt->affected_rows === 0) {
// Rollback to savepoint if receiver's account is not found
$conn->rollback_to("deducted");
throw new Exception("Receiver's account not found. Rolled back to deduct step.");
}
// Commit transaction
$conn->commit();
echo "Transaction successful!";
} catch (Exception $e) {
// Rollback transaction on error
$conn->rollback();
echo "Transaction failed: " . $e->getMessage();
}
$stmt->close();
}
// Example usage
transferMoneyWithSavepoint(1, 3, 100.00);
$conn->close();
?>
Explanation of Savepoints
- Setting a Savepoint: Use $conn->savepoint(“deducted”) to set a savepoint after deducting the amount from the sender’s account.
- Rolling Back to Savepoint: If the receiver’s account is not found, roll back to the “deducted” savepoint using $conn->rollback_to(“deducted”).
Handling Concurrency with Transactions
In a multi-user environment, concurrent transactions can lead to issues such as dirty reads, non-repeatable reads, and phantom reads. MySQL provides different isolation levels to handle these concurrency issues:- READ UNCOMMITTED: Allows dirty reads.
- READ COMMITTED: Prevents dirty reads but allows non-repeatable reads.
- REPEATABLE READ: Prevents dirty and non-repeatable reads but allows phantom reads.
- SERIALIZABLE: Prevents dirty, non-repeatable, and phantom reads.
Setting Isolation Levels in MySQLi
You can set the isolation level for a transaction in MySQLi using the SET TRANSACTION ISOLATION LEVEL statement.
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "bank";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
function setIsolationLevel($level) {
global $conn;
$sql = "SET TRANSACTION ISOLATION LEVEL $level";
if (!$conn->query($sql)) {
throw new Exception("Failed to set isolation level: " . $conn->error);
}
}
// Example usage
try {
setIsolationLevel('REPEATABLE READ');
echo "Isolation level set successfully!";
} catch (Exception $e) {
echo $e->getMessage();
}
$conn->close();
?>
Explanation of Isolation Level Setting
- Setting Isolation Level: Use the SET TRANSACTION ISOLATION LEVEL statement to set the desired isolation level.
- Error Handling: If setting the isolation level fails, an exception is thrown.