Skip to content
← Blog

web development

Laravel Database Transactions, ACID & Concurrency Guide

zobir13 min read
Laravel Database Transactions, ACID & Concurrency Guide

Database Transactions & Concurrency Control: A Complete Laravel Guide

Master ACID Properties, Race Conditions, Locking Strategies, and Deadlock Prevention with Laravel & MySQL

Backend Development · Laravel · MySQL · Database Engineering


Introduction

Building a reliable backend is not just about writing clean code or creating fast APIs. It is about ensuring that your application behaves correctly, even when thousands of users interact with the same data simultaneously.

Imagine an e-commerce platform where two customers try to purchase the last available product. Or a banking application where multiple transactions attempt to withdraw money from the same account.

Without proper database management, these situations can cause lost updates, duplicate orders, inconsistent data, and financial errors.

In this comprehensive guide, we'll explore how to build reliable Laravel applications using database transactions, ACID principles, concurrency control, and locking mechanisms.

What You'll Learn

  • How database transactions work in Laravel and MySQL.

  • The four ACID properties and why they matter.

  • How concurrency leads to race conditions.

  • How to implement pessimistic and optimistic locking.

  • How atomic database operations prevent lost updates.

  • What causes deadlocks and how to handle them.

  • How database isolation levels affect concurrent transactions.

  • Best practices for production Laravel applications.


01. Understanding Database Transactions

What Is a Database Transaction?

A database transaction is a sequence of database operations executed as one logical unit of work.

The fundamental principle is:

Either all required operations succeed, or none of their changes are committed.

Transactions protect applications from partially completed operations that could leave data in an inconsistent state.

Real-World Example: Bank Transfer

Imagine two bank accounts:

Account

Initial Balance

Ahmed

$1,000

Youssef

$500

Ahmed wants to transfer $200 to Youssef.

The application needs to perform two operations:

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

The Problem

What happens if the first query succeeds but the second one fails?

Ahmed loses $200, but Youssef never receives the money.

This creates inconsistent financial data.

The Solution: Database Transactions

START TRANSACTION;

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

UPDATE accounts
SET balance = balance + 200
WHERE id = 2;

COMMIT;

Both operations are now part of a single transaction.

COMMIT: Permanently saves the transaction's changes.

ROLLBACK: Cancels the uncommitted changes.

If an operation fails, the application can execute:

ROLLBACK;

Transactions in Laravel

Laravel provides a convenient transaction API:

use Illuminate\Support\Facades\DB;

DB::transaction(function () {

    Account::where('id', 1)
        ->decrement('balance', 200);

    Account::where('id', 2)
        ->increment('balance', 200);

});

Laravel automatically commits the transaction if the callback completes successfully.

If an exception occurs, Laravel rolls back the transaction.

Developer Note: Transactions provide atomicity, but financial operations also require sufficient-balance checks and protection against concurrent updates.


02. Understanding ACID Properties

ACID represents four essential properties that help databases execute reliable transactions.

Property

Meaning

Purpose

A — Atomicity

All or Nothing

Prevent partial commits

C — Consistency

Preserve database rules

Maintain valid data

I — Isolation

Control concurrent transactions

Prevent unsafe interference

D — Durability

Preserve committed changes

Survive system crashes

Let's explore each property.

A. Atomicity — All or Nothing

Atomicity ensures that a transaction commits all its required changes or none of them.

Consider a banking transaction containing three operations:

  1. Withdraw money from Ahmed.

  2. Deposit money into Youssef's account.

  3. Record the transaction history.

DB::transaction(function () {

    Account::where('id', 1)
        ->decrement('balance', 200);

    Account::where('id', 2)
        ->increment('balance', 200);

    Transfer::create([
        'sender_id' => 1,
        'receiver_id' => 2,
        'amount' => 200,
    ]);

});

If the third operation throws an exception, Laravel rolls back the database changes made by the transaction.

Key Principle:

Atomicity = All operations succeed, or none are committed.

B. Consistency — Maintaining Database Rules

Consistency ensures that transactions preserve defined database constraints and integrity rules.

For example, a banking application may require that balances never become negative.

CREATE TABLE accounts (
    id BIGINT PRIMARY KEY,
    balance DECIMAL(12,2) NOT NULL,
    CONSTRAINT positive_balance
        CHECK (balance >= 0)
);

If a transaction attempts to violate this constraint, MySQL rejects the invalid update.

Consistency can be supported by:

  • Primary keys and foreign keys.

  • Unique constraints.

  • Check constraints.

  • Application-level business validation.

Key Principle:

Consistency = Transactions preserve database rules.

C. Isolation — Managing Concurrent Transactions

Isolation controls how transactions interact when they execute concurrently.

Consider two users attempting to purchase the last available product.

Without appropriate concurrency control, both requests might read the same stock value and incorrectly approve both purchases.

Database isolation mechanisms help control this behavior.

Key Principle:

Isolation = Concurrent transactions do not interfere improperly.

The exact guarantees depend on the isolation level.

D. Durability — Preserving Committed Data

Durability ensures that committed changes survive database crashes.

START TRANSACTION;

UPDATE accounts
SET balance = 800
WHERE id = 1;

COMMIT;

After a successful commit, the database must preserve the change according to its durability guarantees.

MySQL InnoDB uses mechanisms such as redo logging to support crash recovery.

Key Principle:

Durability = Committed data survives system crashes.


03. Concurrency in Modern Applications

What Is Concurrency?

Concurrency means multiple operations make progress during overlapping periods of time.

Modern web applications frequently handle concurrent requests.

Examples

E-commerce: Multiple customers purchasing products simultaneously.

Banking: Several transactions updating the same account.

SaaS: Team members editing shared records.

Web APIs: A server processing multiple HTTP requests concurrently.

Concurrency helps applications serve multiple users efficiently.

However, shared data introduces potential conflicts.


04. Race Conditions Explained

What Is a Race Condition?

A race condition occurs when the result of an operation depends on the timing or execution order of concurrent tasks.

Race conditions can produce incorrect results when shared resources are accessed without appropriate coordination.

Example: The Last iPhone

Imagine an online store with one iPhone remaining.

Initial State:

Product

Available Stock

iPhone 16

1

Two customers attempt to purchase the product simultaneously.

Without Concurrency Control

Time

Request A

Request B

10:00:01

Reads stock = 1

—

10:00:02

—

Reads stock = 1

10:00:03

Product available

—

10:00:04

—

Product available

10:00:05

Creates order

—

10:00:06

—

Creates order

Result:

  • Available inventory: 1

  • Successful orders: 2

Problem: Overselling caused by a race condition.

Another Common Problem: Lost Updates

Consider the following Laravel code:

$product = Product::findOrFail(1);

$product->stock--;

$product->save();

Two requests can read the same initial value.

Initial Stock = 10

Request A reads 10
Request B reads 10

Request A saves 9
Request B saves 9

Final Stock = 9

Two products were purchased, but inventory decreased by only one.

This is called a Lost Update.

Important: A database transaction alone does not automatically eliminate every race condition. Correctness may require locking, appropriate isolation, atomic updates, or constraints.


05. What Is Concurrency Control?

Concurrency control is the collection of techniques used to manage simultaneous operations while maintaining data correctness.

Its purpose is to prevent conflicting operations from producing invalid results.

Common Concurrency Control Techniques

Technique

How It Works

Pessimistic Locking

Locks data before modification

Optimistic Locking

Detects conflicting updates

Atomic Operations

Executes indivisible updates

Isolation Levels

Controls transaction visibility and interference

Database Constraints

Rejects invalid database states

Each approach has different advantages and trade-offs.


06. Pessimistic Locking in Laravel

What Is Pessimistic Locking?

Pessimistic locking protects shared data by acquiring locks before modifying it.

It assumes conflicts may occur and prevents incompatible transactions from modifying locked resources simultaneously.

Laravel provides the lockForUpdate() method.

Example: Preventing Overselling

use Illuminate\Support\Facades\DB;

DB::transaction(function () {

    $product = Product::where('id', 1)
        ->lockForUpdate()
        ->firstOrFail();

    if ($product->stock <= 0) {
        throw new Exception('Out of stock');
    }

    $product->decrement('stock');

    Order::create([
        'product_id' => $product->id,
    ]);

});

How It Works

Transaction A:

  1. Starts a database transaction.

  2. Acquires a lock on the product row.

  3. Checks available inventory.

  4. Decreases stock.

  5. Creates the order.

  6. Commits the transaction.

Transaction B:

  1. Starts another transaction.

  2. Attempts to acquire a conflicting lock.

  3. Waits until Transaction A releases its lock.

  4. Reads the updated stock.

  5. Rejects the purchase if inventory is unavailable.

Advantages

  • Protects critical shared records.

  • Prevents certain conflicting updates.

  • Useful for financial and inventory operations.

Considerations

  • May increase request latency.

  • Can reduce throughput under heavy contention.

  • Requires careful transaction management.

Best Practice: Keep transactions short and acquire locks in a consistent order.


07. Optimistic Locking

What Is Optimistic Locking?

Optimistic locking detects whether a record has been modified since it was read.

Instead of acquiring a lock before reading, the application verifies that the data has not changed before saving.

This is commonly implemented using a version column.

Example: Account Updates

Initial Record:

ID

Balance

Version

1

1000

1

Two requests read the same record.

Request A

UPDATE accounts
SET balance = 800,
    version = version + 1
WHERE id = 1
AND version = 1;

The update succeeds.

Updated Record:

ID

Balance

Version

1

800

2

Request B

UPDATE accounts
SET balance = 700,
    version = version + 1
WHERE id = 1
AND version = 1;

This update affects zero rows because the record's version is now 2.

The application detects the conflict.

Pessimistic vs. Optimistic Locking

Pessimistic Locking

Optimistic Locking

Acquires locks before changes

Detects changes before saving

Can block transactions

Detects stale updates

Useful when conflicts are frequent

Useful when conflicts are uncommon

Relies on database locking

Commonly relies on version checks

Which Should You Choose?

Use pessimistic locking when conflicting updates are likely and must be coordinated.

Use optimistic locking when conflicts are relatively rare and retrying is acceptable.


08. Atomic Database Operations

What Is an Atomic Operation?

An atomic operation executes as an indivisible operation from the perspective of concurrent access.

Atomic updates are useful for modifying counters and inventory values safely.

Unsafe Example

$product = Product::findOrFail(1);

$product->stock--;

$product->save();

This read-modify-write pattern can cause lost updates.

Safer Atomic Update

$updated = Product::where('id', 1)
    ->where('stock', '>', 0)
    ->decrement('stock');

if ($updated === 0) {
    throw new Exception('Out of stock');
}

The condition and decrement are executed as one database update statement.

This helps prevent stock from being decremented below zero by competing requests.

Common Use Cases

  • Product inventory.

  • Usage counters.

  • Page view counters.

  • API rate limits.

  • Numerical state updates.

Developer Tip: Use transactions when an operation must update multiple related records together.


09. Understanding Database Deadlocks

What Is a Deadlock?

A deadlock occurs when two or more transactions wait for resources locked by one another.

Real-World Example

Imagine two transactions that need to update the same two bank accounts.

Transaction A:

Locks Account 1
       ↓
Needs Account 2
       ↓
WAITING

Transaction B:

Locks Account 2
       ↓
Needs Account 1
       ↓
WAITING

Transaction A waits for Transaction B.

Transaction B waits for Transaction A.

Neither can continue without resolving the conflict.

This is a deadlock.

How MySQL Handles Deadlocks

MySQL InnoDB can detect deadlocks and roll back one of the affected transactions.

This releases its locks and allows other transactions to continue.

Preventing Deadlocks

1. Use consistent lock ordering.

Always acquire locks in a predictable order.

2. Keep transactions short.

Avoid slow operations while holding locks.

3. Use appropriate indexes.

Efficient queries can reduce unnecessary locking.

4. Handle transaction failures.

Applications should prepare for deadlocks and lock timeouts.

5. Retry eligible transactions.

Laravel supports transaction retry attempts.

DB::transaction(function () {

    // Database operations

}, 3);

The second argument specifies the maximum number of attempts when handling deadlocks.

Be careful when retrying transactions that perform external side effects, such as sending emails or calling payment APIs.


10. Deadlock vs. Race Condition

These concepts are related to concurrency but represent different problems.

Deadlock

Race Condition

Transactions wait for one another

Results depend on operation timing

Prevents progress until resolved

Can produce incorrect results

Involves circular resource dependencies

Involves unsafe concurrent access

Often handled through rollback and retry

Addressed through concurrency control

Remember:

A deadlock is about transactions being unable to proceed.

A race condition is about the result depending on timing, potentially producing an incorrect outcome.


11. Understanding Database Isolation Levels

Isolation levels define how database transactions interact with concurrent changes.

The four standard isolation levels are:

READ UNCOMMITTED

Allows transactions to read uncommitted changes from other transactions.

Potential Problem: Dirty Reads.

READ COMMITTED

Prevents dirty reads by allowing transactions to read committed data.

However, repeated reads may return different values when other transactions commit updates.

REPEATABLE READ

Provides stable repeated consistent reads within a transaction.

MySQL InnoDB uses REPEATABLE READ as its default isolation level.

SERIALIZABLE

Provides strong isolation guarantees by ensuring transactions behave equivalently to some serial execution order.

It may introduce additional contention or waiting.

Isolation Levels Comparison

Isolation Level

Dirty Reads

Non-Repeatable Reads

READ UNCOMMITTED

Possible

Possible

READ COMMITTED

Prevented

Possible

REPEATABLE READ

Prevented

Prevented

SERIALIZABLE

Prevented

Prevented

Actual behavior can vary between database engines and types of reads.


12. Real-World Use Cases

Understanding these concepts is essential when developing production applications.

E-commerce Applications

Concurrency control helps prevent overselling when multiple customers purchase the same product.

Banking Systems

Transactions protect financial operations from partial updates.

SaaS Platforms

Multiple team members may modify shared records simultaneously.

Optimistic locking and transactional updates can protect application state.

Reservation Systems

Concurrency control prevents conflicting bookings.

CRM Applications

Transactions help coordinate lead assignment, activity creation, and updates to related records.


13. Laravel Database Best Practices

Reliable backend development requires choosing the correct database techniques for each operation.

Group database writes that must succeed or fail together.

Keep Transactions Short

Avoid unnecessary database operations and slow external requests inside transactions.

Use Appropriate Locking

Apply pessimistic locking, optimistic locking, or atomic updates based on your application's requirements.

Enforce Database Constraints

Use primary keys, foreign keys, unique indexes, and check constraints.

Handle Deadlocks

Implement safe retry logic and maintain consistent locking order.

Monitor Database Performance

Track slow queries, transaction duration, and lock contention.

Validate Business Rules

Database transactions do not automatically enforce every business requirement.

Avoid Unnecessary Complexity

Choose the simplest approach that preserves correctness.


14. Complete Technical Summary

Concept

Short Definition

Database Transaction

Groups operations into one logical unit

Atomicity

All or Nothing

Consistency

Preserves database rules

Isolation

Controls concurrent transaction interference

Durability

Preserves committed changes

Concurrency

Multiple operations overlap in time

Race Condition

Results depend on concurrent execution timing

Concurrency Control

Manages simultaneous operations safely

Pessimistic Locking

Locks data before modification

Optimistic Locking

Detects conflicting updates

Atomic Operation

Executes an indivisible operation

Deadlock

Transactions wait for each other's locks


Conclusion

Reliable backend development requires understanding how databases behave under real-world workloads.

Database transactions and ACID properties provide important guarantees for maintaining data integrity.

Concurrency control techniques help applications safely process simultaneous requests, while locking mechanisms and atomic operations protect shared resources.

Understanding race conditions and deadlocks allows developers to identify and prevent some of the most challenging problems in backend engineering.

The key takeaway:

A reliable backend is not just about processing requests quickly. It is about processing them correctly, even when many operations happen concurrently.

At PixCoders, we apply modern backend engineering principles to build reliable web applications, scalable APIs, SaaS platforms, and custom software solutions.

Explore our development services at pixcoders.dev.


Frequently Asked Questions

What are ACID properties?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties describe important guarantees provided by database transactions.

What is the difference between concurrency and a race condition?

Concurrency means multiple operations progress during overlapping periods. A race condition occurs when the result depends on the timing or execution order of those operations.

Does DB::transaction() prevent race conditions?

Not necessarily. Transactions provide ACID guarantees, but preventing specific race conditions may require locks, appropriate isolation levels, atomic updates, or database constraints.

What does lockForUpdate() do in Laravel?

It requests a locking read on selected database rows. Conflicting operations may need to wait until the transaction releases its locks.

What is the difference between pessimistic and optimistic locking?

Pessimistic locking acquires locks before modification. Optimistic locking detects conflicting modifications, commonly through version checks.

What causes a database deadlock?

A deadlock occurs when transactions wait for resources held by one another, creating a circular dependency.

How can developers prevent race conditions?

Developers can use atomic updates, locking mechanisms, database constraints, and suitable transaction isolation.

What is MySQL's default isolation level?

MySQL InnoDB uses REPEATABLE READ by default, although the isolation level can be configured.


Published by PixCoders - Software Engineering & Web Development

Laravel · PHP · MySQL · Backend Architecture · Database Engineering