Database Transactions: Ensuring Data Integrity with PDO

Previously in this course, we covered connecting models to the database and executing secure queries. While individual queries are fine for simple tasks, real-world applications often require multiple related operations that must succeed or fail as a single unit. This lesson teaches you how to use transactions in PHP to guarantee data integrity using PDO.

The Problem: Partial Data Updates

Imagine your MVC application processes an order. You need to do two things:

1. Insert a new record into the orders table.
2. Update the inventory table to decrease the stock count.

If the first step succeeds but the second step fails (perhaps due to a database connection hiccup), you are left with an order placed but no inventory change. This leaves your database in an inconsistent state. A transaction solves this by treating these operations as an "atomic" unit: either all steps succeed, or none of them do.

First Principles: How Transactions Work

A database transaction follows a simple lifecycle that ensures your data remains reliable. You can read more about the conceptual foundations of these operations in ACID Properties in Relational Databases: A Practical Guide.

1. BEGIN: You tell the database, "I am starting a group of operations."
2. EXECUTE: You perform your SQL queries as usual.
3. COMMIT: If everything went well, you tell the database to save all changes permanently.
4. ROLLBACK: If any error occurs, you tell the database to undo all changes since the "BEGIN" step.

Implementing Transactions with PDO

PHP's PDO extension makes this straightforward. We wrap our logic inside a try-catch block to handle potential errors gracefully.

PHP
public function processOrder(int $productId, int $quantity): bool 
{
    try {
        #6A9955">// 1. Begin the transaction
        $this->db->beginTransaction();

        #6A9955">// 2. Perform operations
        $stmt1 = $this->db->prepare("INSERT INTO orders(product_id, qty) VALUES(?, ?)");
        $stmt1->execute([$productId, $quantity]);

        $stmt2 = $this->db->prepare("UPDATE inventory SET stock = stock - ? WHERE id = ?");
        $stmt2->execute([$quantity, $productId]);

        #6A9955">// 3. Commit if everything succeeded
        $this->db->commit();
        return true;

    } catch (Exception $e) {
        #6A9955">// 4. Rollback on any failure
        $this->db->rollBack();
        error_log("Transaction failed: " . $e->getMessage());
        return false;
    }
}

Hands-on Exercise: Atomic User Registration

For your current project, let’s ensure that when a new user registers, both the users table and a user_profiles table are populated.

1. Open your UserModel.php.
2. Wrap your insertUser and createProfile methods inside a single transaction.
3. If createProfile fails, ensure the user record is removed by calling rollBack().
4. Test this by intentionally passing invalid data to the second query to verify that the first query is not saved.

Common Pitfalls

• Forgetting the Rollback: If an exception occurs and you don't call rollBack(), the connection might remain in an "open transaction" state, potentially locking tables and causing issues for subsequent requests.
• Mixing Storage Engines: Ensure your MySQL tables use InnoDB. Older engines like MyISAM do not support transactions.
• Overusing Transactions: Transactions hold locks on database rows. Keep the code inside the block as brief as possible to avoid performance bottlenecks.
• Silent Failures: Always log the exception message inside your catch block. If you just return false, debugging why a transaction failed will be nearly impossible.

FAQ

Q: Do I need transactions for a single INSERT query? A: No. A single query is atomic by default in most modern database engines. Transactions are specifically for grouping multiple dependent operations.

Q: What happens if the PHP script crashes mid-transaction? A: PDO handles this automatically. When the database connection closes, the database engine detects the uncommitted transaction and performs an implicit rollback.

Q: Can I nest transactions? A: Most databases do not support true nested transactions. PDO will throw an exception if you try to start a new transaction while one is already active.

Recap

We’ve learned that transactions are essential for maintaining data integrity in complex processes. By using beginTransaction(), commit(), and rollBack() within a try-catch block, we ensure that our application remains consistent, even when errors strike. This is a critical skill, as discussed in Introduction to Transactions: Ensuring Data Consistency in PostgreSQL, which applies equally well to our MySQL implementation.

Up next: We will implement advanced routing constraints to make your MVC application's URLs more dynamic and professional.