Contents

• Introduction
• The A in ACID is Atomicity
• A Practical Example
• The Laravel DB::transaction Solution
• Manual Transaction Control in Laravel
• Beyond Laravel
• Conclusion

Introduction

Software engineers love acronyms. From SOLID (principles), to ACID properties, we learn and memorise these ideas in the early stages of our careers but sometimes things only truly click into place when we encounter a situation where we need to apply them.

I had that sort of 'aha' moment with the ACID properties in database management. The problem I was dealing with was simple: "what can I do if I have to perform multiple, related database operations and there's the possibility that one of them might fail?" Let's say you're creating a user and their initial profile, a product and some metadata, an order and its line items, or a subscription plan and its associated features and limits. What happens if the first operation succeeds, but the second one fails.

In my case, I was left with inconsistent, 'dangling' data, specifically, a plan with no limits or features. This wasn't just messy, it caused a bunch of bugs and left me dealing with a data integrity mess to clean up.

Somewhere in the back of my mind, I knew that databases has some built-in feature to handle this. In this post, we will introduce database atomicity, explore why it's important, and see how Laravel's DB::transaction helper makes it easy to ensure that our database operations are robust and reliable.

The A in ACID is Atomicity

If you're not already familiar with the ACID properties, they are guarantees for reliable database interactions:

1. Atomicity
2. Consistency
3. Isolation
4. Durability

Our focus will be on atomicity. It's also known as the all or nothing principle. An atomic transaction is an indivisible, irreducible series of database operations. This means that:

• if all operations within the transactions succeed, the changes are committed to the database
• if any single one of the operations in the transaction fails (e.g. due to a constraint violation), then all operations performed within that transaction so far are undone. The database is returned to the state it was in before the transaction started.

It guarantees that you won't be left in a state where only part of a logical unit of work has been completed.

One of the best explanations I have heard of this, one that goes in-depth and touches on how databases work on an infrastructural level, is in Hussein Nassar's Database Fundamentals course on Udemy. It's a paid course, but it's one of three courses from him I have taken that I believe are absolutely essential for backend developers.

A Practical Example

You can find the full code for the post in the GitHub repo

Taking that plan and features example, imagine that we don't use a transaction when creating a new subscription plan and its features. Our Laravel code might look something like the example below, using the Query Builder via the DB facade:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Exception;

class PlanController extends Controller
{
    public function store(Request $request)
    {
        $request->validate([
			'plan_name' => ['required', 'string', 'max:255', 'unique:plans'],
			'plan_price' => ['required', 'numeric', 'gt:0'],
			'plan_features' => ['required', 'string'],
		]);

        $planName = $request->input('plan_name');
        $planPrice = $request->input('plan_price');
        $features = $request->input('plan_features');

        $features = $features ? explode(',', $features) : [];

        $planId = null;

        try {
            $planId = DB::table('plans')->insertGetId([
                'name' => $planName,
                'price' => $planPrice,
            ]);

            // a simulated error during feature creation
            if (in_array('simulate_error', $features)) {
                 throw new Exception("Error creating features!");
            }

            if ($planId && !empty($features)) {
                $featureData = [];
                foreach ($features as $feature) {
                    if ($feature !== 'simulate_error') {
                         $featureData[] = [
                            'plan_id' => $planId,
                            'description' => $feature,
                         ];
                    }
                }
                if (!empty($featureData)) {
                    Log::info("Attempting to insert " . count($featureData) . " features for plan ID: {$planId}");
                    DB::table('plan_features')->insert($featureData);
                    Log::info("Features inserted successfully.");
                }
            }

            Log::info("Plan and features creation process completed successfully for plan ID: {$planId}");
            return response()->json(['message' => 'Plan created successfully!', 'plan_id' => $planId], 201);

        } catch (Exception $e) {
            Log::error("Error creating plan or features: " . $e->getMessage());

            return response()->json(['message' => 'Failed to create plan: ' . $e->getMessage()], 500);
        }
    }
}

The simulated error is there to highlight the exact problem we are trying to address.

graph TD
    A[Start: Receive Plan Request] --> B[Insert basic plan into plans table];
    B --> C{Input features include 'simulate_error'?};
    C -- Yes --> D[Throw Exception!];
    C -- No --> E[Prepare & Insert features into plan_features table];
    D --> F[<strong>Left with orphaned plan];
    E --> G[Success];
    F --> H[End];
    G --> H[End];

If an error, simulated or otherwise, happens after the plan is created but before or during feature creation, we're left with a row in the plans table with some or none of its associated features i.e. a dangling plan.

If, for example, the store method was supposed to return a JsonResource with a plan and its features, this may lead to errors as we try to access these non-existent plan features. We could delete the plan in the catch block, but that adds complexity and additional points of failure.

The Laravel DB::transaction Solution

Fortunately, Laravel provides an elegant way of handling this using the DB::transaction method. It accepts a closure containing the database operations that should be treated as a single, atomic unit.

Let's refactor the above example to use transactions:

// everything else remains the same
try {
    $result = DB::transaction(function () use ($planName, $planPrice, $features) {
        $planId = DB::table('plans')->insertGetId([
            'name' => $planName,
            'price' => $planPrice,
        ]);

        // a simulated error during feature creation
        if (in_array('simulate_error', $features)) {
            throw new Exception("Error creating features!");
        }

        if ($planId && !empty($features)) {
            $featureData = [];
            foreach ($features as $feature) {
                if ($feature !== 'simulate_error') {
                    $featureData[] = [
                        'plan_id' => $planId,
                        'description' => $feature,
                    ];
                }
            }
            if (!empty($featureData)) {
                Log::info("Attempting to insert " . count($featureData) . " features for plan ID: {$planId}");
                DB::table('plan_features')->insert($featureData);
                Log::info("Features inserted successfully.");
            }
        }

        Log::info("Plan and features creation process completed successfully for plan ID: {$planId}");
    }, 5);

    return response()->json(['message' => 'Plan created successfully!', 'result' => $result], 201);
} catch (Exception $e) {
    Log::error("Transaction failed and rolled back: " . $e->getMessage());

    return response()->json(['message' => 'Failed to create plan: ' . $e->getMessage()], 500);
}

In the updated code, we keep the same creation flow, but all the operations are performed inside a transaction closure. We can even specify the number of retries in the event of a deadlock (in this case, 5). We catch any exceptions that may be thrown in the DB::transaction closure, in which case Laravel automatically issues a ROLLBACK.

[logs] │ Transaction failed and rolled back: Error creating features!

Having the transaction there means that the database undoes the plan insert query, leaving our data in a consistent state.

The above description is simplistic. In reality, the database uses the write-ahead log to keep the changes in "limbo" and only applies them to the filesystem after the commit.

Manual Transaction Control in Laravel

For all the benefits of Laravel's DB::transaction closure method, there will be times when you may want to take the wheel and manually manage the transaction lifecycle. In this case, Laravel provides all the necessary tools to (potentially) shoot yourself in the foot.

The relevant methods are:

• DB::beginTransaction(): starts a new database transaction
• DB::commit(): saves all the operations performed since beginTransaction() permanently to the database.
• DB::rollBack(): undoes all operations performed since beginTransaction().

Here's how our plan/features example would look using manual transaction control:

try {
    DB::beginTransaction();
    // #! the code to create the plan and its features remains unchanged

    // If we reached this point, everything went well. Commit the transaction.
    DB::commit();

    return response()->json(['message' => 'Plan created successfully!', 'plan_id' => $planId], 201);
} catch (Exception $e) {
    // An error occurred! Roll back ALL changes since beginTransaction().
    DB::rollBack();
    Log::error("Transaction failed and rolled back: " . $e->getMessage());

    return response()->json(['message' => 'Failed to create plan: ' . $e->getMessage()], 500);
}

This is clearly clunkier than just using DB::transaction. So, why would anyone do this?

• Complex logic: maybe there are more complex decisions you need to make to determine whether to commit or rollback.
• External interactions: If you need to interact with non-transactional systems (e.g. external APIs) within the block and potentially rollback based on their responses.

In both of these situations, it is assumed that you absolutely know what you are doing. You alone would be responsible for calling DB::commit() or DB::rollback(). If you forget either, your DB connection may stay open with uncommitted transactions. Hence, in most cases, the DB::transaction closure is safer and more readable.

Beyond Laravel

I would be remiss to point out that, while Laravel's DB facade and Eloquent ORM provide convenient ways to interact with the database, atomicity itself is a core feature of RDMS like MySQL, PostgreSQL, SQLite, etc. Under the hood, Laravel uses PHP's database connectivity layers (e.g. PDO) to send standard SQL commands to the database server. The transaction commands (BEGIN TRANSACTION, COMMIT, and ROLLBACK) are part of the SQL standard.

We can see this in action with a simplified example using raw PDO in PHP, completely outside of Laravel, to show the same atomic principle at work:

try {
    $pdo->beginTransaction();
    echo "PDO: BEGIN TRANSACTION\n";

    $sqlPlan = "INSERT INTO plans (name, price) VALUES (?, ?)";
    $stmtPlan = $pdo->prepare($sqlPlan);
    $stmtPlan->execute([$planName, $planPrice]);
    $planId = $pdo->lastInsertId();
    echo "PDO: Plan created with ID: {$planId}\n";

    // #! A simulated error during feature creation
    if (in_array('simulate_error_pdo', $features)) {
        throw new Exception("PDO: Simulated error creating features!");
    }

    if ($planId && !empty($features)) {
        $sqlFeatures = "INSERT INTO plan_features (plan_id, description) VALUES (?, ?)";
        $stmtFeatures = $pdo->prepare($sqlFeatures);

        foreach ($features as $feature) {
             if ($feature !== 'simulate_error_pdo') {
                echo "PDO: Inserting feature '{$feature}' for plan ID: {$planId}\n";
                $stmtFeatures->execute([$planId, $feature]);
             }
        }
        echo "PDO: Features inserted.\n";
    }

    $pdo->commit();
    echo "PDO: COMMIT\n";
    echo "Plan and features created successfully (PDO Example)!\n";
}

Breakdown

The try-block shows a similar flow to the above, Laravel-based examples, with the key difference being that we don't use Laravel's abstractions.

When we call $pdo->beginTransaction(), the driver sends a command like:

• START TRANSACTION; (Common in MySQL)
• BEGIN TRANSACTION; (PostgreSQL)
• BEGIN; (shorthand in some DBs)

This is the same even with DB::beginTransaction() and DB::transaction in Laravel.

When you commit ($pdo->commit()), it sends:

• COMMIT;

And, on rollback ($pdo->rollBack()) , it sends:

• ROLLBACK;

While frameworks like Laravel provide useful abstractions (mostly so that we don't shoot ourselves in the foot), the power of atomic transactions comes directly from the database itself. Understanding this can help us appreciate why these abstractions work, allowing us to carry this understanding beyond the framework, and know when to reach for certain tools in the developer's toolbox.

Conclusion

This encounter was an important illustration, for me, of the importance of bridging the gap between simply knowing something theoretically, and discovering why you need it in practice. Without the A in ACID, we risk corrupting our database with incomplete (dangling) data.

Laravel's DB::transaction facade method provides a simple and straightforward way to leverage database transactions, but it's only as powerful as the underlying tech. Before you reach for these abstractions, take a moment to investigate how and why they work. The database itself gives us the tools to ensure that the "all or nothing" principle is upheld. Frameworks come and go, but the fundamentals will never change.

If you've made it this far, I hope that this has been informative and enjoyable. If you liked it, don't hesitate to share it with a friend who might find it interesting. If you have any questions, comments, or suggestions (or just want to say hi), Mastodon is where you'll find me.