MySQL Transactions & Data Integrity in Laravel

“Without Data, you’re just another person with an opinion.” - W. Edwards Deming

Key takeaways

• Use DB::transaction() (closure) for most cases - it auto-commits or rolls back on exception.
• Laravel emulates nested transactions with savepoints when the DB supports them (e.g., MySQL/InnoDB). Keep nesting shallow - savepoints add complexity.
• For concurrency control use pessimistic locks (lockForUpdate() / sharedLock()) or implement optimistic locking (version column + conditional update) depending on contention patterns.
• DB::transaction(..., $attempts) will retry the closure when a database deadlock occurs; it does not retry on arbitrary exceptions - plan accordingly.
• Avoid DDL/implicit-commit statements inside transactions and avoid external network I/O inside transactions (or dispatch those jobs ->afterCommit()).

Index

1. Why transactions & ACID matter
2. MySQL (InnoDB) specifics you must know
3. Laravel 12 transaction API (quick reference + examples)
4. Nested transactions & savepoints (how Laravel handles them)
5. Locks: pessimistic (lockForUpdate / sharedLock) vs optimistic (versioning)
6. Deadlocks, retries, and how to handle them safely
7. Common pitfalls (DDL, implicit commits, jobs & events) + how to avoid them
8. Testing transactions
9. Advanced patterns (distributed transactions, sagas)
10. Practical examples
11. Stats
12. Interesting Facts
13. FAQs
14. Conclusion

1 - Why transactions & ACID matter

Transactions give you atomicity (all-or-nothing), consistency (DB constraints upheld), isolation (concurrent work doesn’t clobber you) and durability (committed changes survive). For business-critical flows - payments, inventory decrement, multi-table writes - wrap related DB actions in transactions to avoid partial state.

2 - MySQL (InnoDB) specifics you must know

• Use InnoDB for transactions, row-level locking, and foreign keys. Many features (transactional semantics, FK checks) depend on it.
• Isolation level influences visibility and anomalies (dirty/read/write skew). MySQL default is REPEATABLE READ (but you can change per-session or server). Test your workload under the isolation level you use.
• Savepoints are supported by InnoDB and allow emulated nested transactions. DDL will often cause implicit commits - don’t run CREATE TABLE / ALTER TABLE inside a transaction you expect to keep open.

3 - Laravel 12 transaction API (reference + examples)

Auto-managed closure (the usual)

use Illuminate\Support\Facades\DB;

DB::transaction(function () {
    // Eloquent or Query Builder operations here
    $user = User::create([...]);
    $order = Order::create(['user_id' => $user->id, 'total' => 100]);
    $order->items()->createMany($items);
});
// on exception -> rollback; on success -> commit

• Laravel docs: DB::transaction() auto-commits or auto-rolls back and can accept a second param for deadlock retry attempts.

Manual begin/commit/rollback

DB::beginTransaction();

try {
    // multiple operations spanning functions/classes
    $invoice = Invoice::create([...]);
    // maybe call services that modify DB
    DB::commit();
} catch (\Throwable $e) {
    DB::rollBack();
    throw $e; // rethrow or handle
}

Retry on deadlock

DB::transaction(function () {
    // high-contention updates
}, 5); // try up to 5 times if a deadlock occurs

4 - Nested transactions & savepoints

• MySQL doesn’t support true nested transactions, but InnoDB supports SAVEPOINT. Laravel counts transaction nesting levels and uses savepoints under the hood where supported. That means you can call DB::beginTransaction() multiple times in the call stack - Laravel will create savepoints rather than starting separate DB transactions.
• Gotchas: savepoints increase complexity; behavior around RELEASE SAVEPOINT or committing a nested level has been the subject of issues in the past - be careful and test. Avoid deep nesting; prefer letting a single outer transaction orchestrate the unit of work.

DB::beginTransaction();             // outer
try {
    // do something
    DB::beginTransaction();         // inner -> creates SAVEPOINT
    try {
        // do something that may fail
        DB::commit();               // releases savepoint (emulated)
    } catch (\Throwable $innerEx) {
        DB::rollBack();             // rollback to savepoint
    }
    DB::commit();                   // commit outer
} catch (\Throwable $e) {
    DB::rollBack();
}

5 - Locks: pessimistic vs optimistic

Pessimistic locking (when you must block others)

Laravel provides lockForUpdate() and sharedLock() on query builder / Eloquent -
these map to SELECT ... FOR UPDATE and shared locks. Wrap them in a transaction
for correctness. Use for critical updates (balances, inventory).

Example - safe money transfer

DB::transaction(function () use ($fromId, $toId, $amount) {
    $from = DB::table('accounts')->where('id', $fromId)->lockForUpdate()->first();
    $to   = DB::table('accounts')->where('id', $toId)->lockForUpdate()->first();

    if ($from->balance < $amount) {
        throw new \RuntimeException('Insufficient funds');
    }

    DB::table('accounts')->where('id', $fromId)->decrement('balance', $amount);
    DB::table('accounts')->where('id', $toId)->increment('balance', $amount);
});

Optimistic locking (low-contention, high throughput)

Laravel doesn't provide built-in optimistic locking. Implement it with a version (or use
updated_at ) column and perform a conditional update (WHERE id = ? AND version =
?) and check affected rows. If 0 rows updated → conflict→ retry/respond.

Optimistic example

// migration: $table->unsignedInteger('version')->default(0);

$updated = DB::table('tickets')
    ->where('id', $id)
    ->where('version', $clientVersion)
    ->update([
        'status' => 'sold',
        'version' => $clientVersion + 1,
    ]);

if (! $updated) {
    // conflict — return 409, or retry read->apply pattern
}

Optimistic locking is efficient when collisions are rare.

“You can have all of the fancy tools, but if your data quality is not good, you’re
nowhere.” - Veda Bawo

6 - Deadlocks, retries, and safe side-effects

Deadlocks can happen in high-concurrency workloads (two transactions lock rows in opposite order). Use DB::transaction(..., $attempts) to let Laravel retry on deadlocks. But only deadlocks are automatically retried; arbitrary exceptions are not. Also be careful with side effects inside the closure (external HTTP calls, emails) because retries re-run the closure.

Safe pattern

• Keep transactions small and DB-only (no external HTTP).
• If you need to call external systems, commit first or dispatch jobs >afterCommit() (see section below).
• If you must retry, log each attempt and use exponential backoff on retries outside DB closure.

7 - Common pitfalls & how to avoid them

1) DDL / implicit commits
Running DDL (e.g., CREATE TABLE, some ALTER TABLE) inside a transaction can trigger an implicit commit in MySQL - your transaction semantics break. Don’t perform schema changes inside app transactions. Laravel docs warn about this.

2) External I/O inside transactions
Avoid external network calls (HTTP, SMTP) inside transactions - failures or retries are complex. If you must dispatch jobs or send mails after commit, use ->afterCommit():
ProcessOrderJob::dispatch($order)->afterCommit();
This ensures the job is queued only after the DB transaction commits. (Also configurable via after_commit queue option.)

3) Events dispatched inside transactions
Recent Laravel versions added improved behavior so events can be aware of transactions; still, be careful with queued listeners - use afterCommit or dispatch from an event listener that's transactional-aware.

4) Cross-connection atomicity
Laravel transactions are per database connection. If your use-case requires atomicity across multiple database servers, you’ll need a distributed transaction coordinator (2PC/XA) or a different architecture (saga patterns). In practice: avoid multi-DB atomic transactions unless you really need them.

8 -Testing transactions

• Use Laravel’s testing tools (RefreshDatabase, DatabaseTransactions trait when appropriate) for reproducible tests. Tests often wrap a test in a transaction and roll it back so DB state is clean. See Laravel database testing docs.

“Data is a living thing. It must be nurtured and protected.” - Thomas Redman

9 - Advanced: distributed transactions & sagas

• Two-phase commit (XA/2PC) exists but is complex and painful (coordinator, locking, failure modes). Most modern apps prefer compensating transactions / saga patterns for multi-service workflows. If you absolutely need multi-DB atomicity, research XA or a distributed transaction manager - but prefer architecture that avoids cross-DB transactions.

10 - Practical examples

A. Create order + decrement stock

DB::transaction(function () use ($userData, $cartItems) {
    $user = User::create($userData);
    $order = Order::create(['user_id' => $user->id, 'total' => array_sum(array_column($cartItems, 'price'))]);

    foreach ($cartItems as $item) {
        // lock product row and update stock safely
        $product = Product::where('id', $item['product_id'])->lockForUpdate()->first();

        if ($product->stock < $item['qty']) {
            throw new \RuntimeException("Out of stock for product {$product->id}");
        }

        $product->decrement('stock', $item['qty']);
        $order->items()->create([
            'product_id' => $product->id,
            'price' => $item['price'],
            'qty' => $item['qty'],
        ]);
    }

    // dispatch email after commit to avoid race conditions
    SendOrderConfirmation::dispatch($order)->afterCommit();
});

B. Optimistic update with retry

function updateNameOptimistic(int $id, string $newName, int $currentVersion) {
    $affected = DB::table('profiles')
        ->where('id', $id)
        ->where('version', $currentVersion)
        ->update([
            'name' => $newName,
            'version' => $currentVersion + 1,
            'updated_at' => now(),
        ]);

    if (! $affected) {
        // conflict detected
        throw new \RuntimeException('Conflict: profile updated by another request. Try again.');
    }
}

C. Manual savepoint example (nested transaction emulation)

DB::beginTransaction(); // start outer
try {
    // work A

    DB::beginTransaction(); // creates savepoint
    try {
        // work B that may fail
        DB::commit(); // releases inner savepoint
    } catch (\Throwable $e) {
        DB::rollBack(); // rollback to savepoint (undo B only)
        // handle or rethrow
    }

    // continue work A
    DB::commit();
} catch (\Throwable $e) {
    DB::rollBack();
    throw $e;
}

11 - Stats

• Default MySQL (InnoDB) isolation level: REPEATABLE READ. https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html
• DB::transaction($closure, 5) - the second parameter tells Laravel how many times to retry on deadlock (example 5). https://laravel.com/docs/12.x/database

12 - Interesting facts

• Savepoints let you “roll back partially” within the same transaction, but you can’t magically remove a set of changes in the middle without undoing everything above it (savepoints act like a stack).https://dev.mysql.com/doc/refman/9.0/en/savepoint.html
• Some SQL statements (DDL) trigger an implicit commit in MySQL - executing them inside an open transaction will commit the whole transaction behind Laravel’s back. Laravel warns about this.

13 - FAQs

Q - Will DB::transaction() retry my closure on any exception?
A - No. The optional second parameter sets how many times to retry for deadlocks.Other exceptions will not trigger automatic retry. Plan side effects with that in mind.

Q - Can I dispatch a queued job from inside a transaction?
A - Yes - but prefer ->afterCommit() to ensure jobs are queued only after the transaction successfully commits. Otherwise the job might run before DB changes are durable.

Q - Are nested transactions safe?
A - Laravel emulates nested transactions using savepoints when supported; it works but adds complexity - avoid deep nesting and test savepoint behavior on
your MySQL version.

Q - Do schema changes rollback if my transaction fails?
A - No. DDL often causes implicit commits - don’t rely on rolling back schema changes. Use migrations instead of runtime DDL.

14 - Conclusion

• Keep the transaction small and DB-only (no HTTP/mail during the transaction). Use afterCommit() for jobs.
• Use lockForUpdate() where you need exclusive access, or optimistic locking when collisions are rare.
• Add deadlock retry logic: DB::transaction($closure, $attempts) and add logging/backoff.
• Avoid DDL/statements that trigger implicit commit inside transactions.

About the Author: Vatsal is a web developer at AddWebSolution. Building web magic with Laravel, PHP, MySQL, Vue.js & more. Blending code, coffee, and creativity to bring ideas to life.