Understanding Transactions
A transaction is a logical unit of work that must be either entirely completed or entirely failed. Transactions ensure data integrity by adhering to the ACID (Atomicity, Consistency, Isolation, Durability) properties:- Atomicity: Ensures that all operations within the transaction are completed successfully. If any operation fails, the transaction is aborted.
- Consistency: Ensures that the database transitions from one valid state to another.
- Isolation: Ensures that concurrent transactions do not affect each other.
- Durability: Ensures that once a transaction is committed, it is permanently recorded in the database.
Transaction Control Commands
BEGIN
The BEGIN command starts a new transaction. In MySQLi, you can use begin_transaction or the SQL command START TRANSACTION to initiate a 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
Let’s walk through a practical example of using BEGIN, COMMIT, and ROLLBACK in MySQLi with PHP.Setting Up the Database
First, ensure you have a MySQL database and table to work with. For this example, we’ll use a simple 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 with MySQLi Transactions
We’ll write a PHP script that transfers money from one account to another. If any part of the transaction fails, it will roll back the changes to ensure 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: We establish a connection to the MySQL database using MySQLi.
- Starting the Transaction: We use $conn->begin_transaction() to start 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 of the SQL operations fail, 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.
Testing the Script
To test the script, you can run it and observe the changes in the accounts table. If the transaction is successful, the balances of the accounts will be updated accordingly. If there is an error (e.g., insufficient funds or invalid account), the transaction will be rolled back, and the balances will remain unchanged.Advanced Usage
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 can be 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();
?>