What are Transactions?

A transaction in the context of database management is a sequence of operations performed as a single logical unit of work. A transaction is characterized by four key properties, commonly referred to as ACID properties:

Atomicity: Ensures that all operations within a transaction are completed successfully; otherwise, the transaction is aborted. If any part of the transaction fails, the entire transaction fails and the database state is left unchanged.
Consistency: Ensures that the database transitions from one valid state to another valid state. This means that any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
Isolation: Ensures that the operations in a transaction are isolated from other operations. This means that transactions occurring concurrently will not interfere with each other.
Durability: Ensures that once a transaction is committed, it will remain so, even in the event of a system failure. This means that the results of the transaction are permanent and persist in the database.

Transactions are fundamental to maintaining the integrity and reliability of a database. They are particularly useful in scenarios where multiple operations need to be executed as a unit to maintain consistency and accuracy.

Transaction Concepts and Use Cases

Concepts

BEGIN TRANSACTION: Starts a new transaction.
COMMIT: Saves all the changes made in the transaction to the database.
ROLLBACK: Undoes all the changes made in the current transaction.

Use Cases

Transactions are critical in various scenarios, such as:

Financial Systems: Ensuring that all steps in a financial transaction, such as debiting one account and crediting another, are completed successfully.
Order Processing Systems: Ensuring that all steps in processing an order, such as updating inventory, charging the customer, and creating a shipment, are completed successfully.
Batch Processing: Ensuring that a series of database operations are completed as a unit, such as migrating data from one table to another.

Example Use Case

Consider a banking system where money is transferred from one account to another. The steps involved might be:

Debit amount from the sender’s account.
Credit amount to the receiver’s account.

Both these steps need to be completed successfully for the transaction to be valid. If one step fails, the other should not proceed.

Transactions in MySQLi

MySQLi (MySQL Improved) is an extension for PHP that allows for interactions with MySQL databases. MySQLi provides support for transactions, enabling developers to implement ACID-compliant operations in their applications.

Implementing Transactions in MySQLi

To implement transactions in MySQLi, you follow these basic steps:

Start a transaction using begin_transaction() or autocommit(FALSE).
Perform the necessary operations using standard SQL queries.
Commit the transaction using commit() if all operations are successful.
Rollback the transaction using rollback() if any operation fails.

Code Example: Transfer Money Between Accounts

Let’s consider a simple example of transferring money between two bank accounts.

Create a MySQLi Connection

				
					<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "bank_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Perform a Transaction

				
					<?php
$sender_id = 1;
$receiver_id = 2;
$amount = 100.00;

try {
    // Start transaction
    $conn->begin_transaction();

    // Step 1: Debit sender's account
    $sql = "UPDATE accounts SET balance = balance - ? WHERE account_id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("di", $amount, $sender_id);
    if (!$stmt->execute()) {
        throw new Exception("Could not debit sender's account");
    }

    // Step 2: Credit receiver's account
    $sql = "UPDATE accounts SET balance = balance + ? WHERE account_id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("di", $amount, $receiver_id);
    if (!$stmt->execute()) {
        throw new Exception("Could not credit receiver's account");
    }

    // Commit transaction
    $conn->commit();
    echo "Transaction completed successfully";

} catch (Exception $e) {
    // Rollback transaction on error
    $conn->rollback();
    echo "Transaction failed: " . $e->getMessage();
}

$conn->close();
?>

Explanation

Start Transaction: begin_transaction() starts a new transaction.
Debit Sender’s Account: The balance of the sender’s account is reduced by the transfer amount. If this operation fails, an exception is thrown.
Credit Receiver’s Account: The balance of the receiver’s account is increased by the transfer amount. If this operation fails, an exception is thrown.
Commit Transaction: If both operations succeed, the transaction is committed using commit().
Rollback Transaction: If any operation fails, the transaction is rolled back using rollback().

Best Practices

Error Handling: Always handle errors and exceptions to ensure the transaction can be rolled back in case of failure.
Atomicity: Ensure all operations within a transaction are part of the logical unit of work.
Isolation Levels: Understand and use appropriate transaction isolation levels to balance performance and data integrity.
Testing: Thoroughly test transactions to ensure they handle all edge cases and error conditions correctly.

Conclusion

Transactions are a cornerstone of reliable and consistent database operations. They ensure that a series of operations are completed successfully as a single unit, maintaining data integrity and consistency. MySQLi provides robust support for transactions, allowing developers to implement complex and reliable database operations in PHP applications. By understanding and utilizing transactions effectively, you can enhance the reliability and maintainability of your database-driven applications.

What are Transactions?