Database Transactions

Introduction

Database transactions are crucial in ensuring data integrity and consistency. A transaction is a sequence of operations performed as a single logical unit of work. A transaction must exhibit four properties, known as ACID properties: Atomicity, Consistency, Isolation, and Durability.

ACID Properties

Let's delve into the ACID properties:

Atomicity: Ensures that all operations within a transaction are completed successfully. If any operation fails, the entire transaction is rolled back.
Consistency: Ensures that a transaction brings the database from one valid state to another, maintaining database invariants.
Isolation: Ensures that the operations of a transaction are isolated from other transactions, preventing concurrent transactions from affecting each other.
Durability: Ensures that once a transaction is committed, the changes are permanent, even in the case of a system failure.

Starting a Transaction in PHP

To demonstrate database transactions in PHP, we'll use MySQLi. The following code shows how to start a transaction:


                    <?php

                    $conn = new mysqli("localhost", "username", "password", "database");

                    

                    // Check connection

                    if ($conn->connect_error) {

                        die("Connection failed: " . $conn->connect_error);

                    }

                    

                    // Start transaction

                    $conn->begin_transaction();

                    ?>

Performing Operations within a Transaction

Within a transaction, you can perform multiple database operations. If all operations are successful, you can commit the transaction. Otherwise, you can roll back the transaction:


                    <?php

                    $conn = new mysqli("localhost", "username", "password", "database");

                    

                    // Check connection

                    if ($conn->connect_error) {

                        die("Connection failed: " . $conn->connect_error);

                    }

                    

                    // Start transaction

                    $conn->begin_transaction();

                    

                    try {

                        // First operation

                        $conn->query("INSERT INTO accounts (name, balance) VALUES ('Alice', 1000)");

                        

                        // Second operation

                        $conn->query("INSERT INTO accounts (name, balance) VALUES ('Bob', 1500)");

                        

                        // Commit transaction

                        $conn->commit();

                    } catch (Exception $e) {

                        // Rollback transaction

                        $conn->rollback();

                        echo "Transaction failed: " . $e->getMessage();

                    }

                    

                    $conn->close();

                    ?>

Example: Transferring Money Between Accounts

Consider a scenario where you want to transfer money between two accounts. This operation involves multiple steps: deducting the amount from one account and adding it to another. Both operations must succeed or fail together to maintain data consistency:


                    <?php

                    $conn = new mysqli("localhost", "username", "password", "database");

                    

                    // Check connection

                    if ($conn->connect_error) {

                        die("Connection failed: " . $conn->connect_error);

                    }

                    

                    // Start transaction

                    $conn->begin_transaction();

                    

                    $fromAccount = 1;

                    $toAccount = 2;

                    $amount = 500;

                    

                    try {

                        // Deduct from source account

                        $conn->query("UPDATE accounts SET balance = balance - $amount WHERE id = $fromAccount");

                        

                        // Add to destination account

                        $conn->query("UPDATE accounts SET balance = balance + $amount WHERE id = $toAccount");

                        

                        // Commit transaction

                        $conn->commit();

                        echo "Transaction successful!";

                    } catch (Exception $e) {

                        // Rollback transaction

                        $conn->rollback();

                        echo "Transaction failed: " . $e->getMessage();

                    }

                    

                    $conn->close();

                    ?>

Conclusion

Database transactions are essential for maintaining data integrity and consistency. By using transactions, you can ensure that a series of operations either complete successfully or have no effect at all. This tutorial demonstrated the basics of transactions in PHP using MySQLi, including starting a transaction, performing operations, committing, and rolling back.