Database Transactions

Introduction to Database Transactions

A database transaction is a sequence of operations performed as a single logical unit of work. A transaction must be either fully completed or fully failed, ensuring data integrity. Transactions are essential in scenarios where multiple related operations need to succeed or fail together, such as transferring money between accounts.

ACID Properties

Transactions are governed by four key properties known as ACID:

• Atomicity: Ensures that all operations within the transaction are completed; if any operation fails, the transaction is aborted.
• Consistency: Ensures that a transaction brings the database from one valid state to another, maintaining database rules.
• Isolation: Ensures that transactions occur independently of one another, even when they are executed concurrently.
• Durability: Ensures that once a transaction has been committed, it remains so, even in the event of a system failure.

Implementing Transactions in PHP with MySQLi

To implement a transaction in PHP, you can use the following steps:

1. Begin the transaction using the begin_transaction() method.
2. Execute the necessary queries.
3. If all queries are successful, commit the transaction with commit().
4. If any query fails, rollback the transaction using rollback().

Here's a sample implementation:

    <?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";
    
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    
    // Begin transaction
    $conn->begin_transaction();
    
    try {
        // Execute queries
        $conn->query("INSERT INTO Accounts (username, balance) VALUES ('Alice', 1000)");
        $conn->query("INSERT INTO Accounts (username, balance) VALUES ('Bob', 500)");
    
        // Example of a potential failure
        // Uncommenting the line below will trigger a rollback
        // throw new Exception("Simulated error");
    
        // Commit transaction
        $conn->commit();
        echo "Transaction completed successfully.";
    } catch (Exception $e) {
        // Rollback transaction
        $conn->rollback();
        echo "Transaction failed: " . $e->getMessage();
    }
    
    // Close connection
    $conn->close();
    ?>
                

Expected Output

Or, if an error occurs:

Example: Money Transfer Between Accounts

Here's an example of a transaction for transferring money between two accounts:

    <?php
    $conn = new mysqli($servername, $username, $password, $dbname);
    $conn->begin_transaction();
    
    try {
        $from_account = "Alice";
        $to_account = "Bob";
        $amount = 100;
    
        // Deduct amount from the sender's account
        $conn->query("UPDATE Accounts SET balance = balance - $amount WHERE username = '$from_account'");
        
        // Add amount to the receiver's account
        $conn->query("UPDATE Accounts SET balance = balance + $amount WHERE username = '$to_account'");
    
        $conn->commit();
        echo "Transfer completed successfully.";
    } catch (Exception $e) {
        $conn->rollback();
        echo "Transfer failed: " . $e->getMessage();
    }
    
    $conn->close();
    ?>
                

Expected Output

Or, if an error occurs:

Summary

This tutorial introduced database transactions in PHP, emphasizing their importance in maintaining data integrity through the ACID properties. You learned how to implement transactions using MySQLi, ensuring that a series of database operations either complete successfully or not at all.