--- title: 'Database and Distributed Transactions: Isolation, Locks, and Consensus Patterns' description: 'A comprehensive deep dive into database transactions, race conditions, optimistic vs pessimistic locking, Redis-based distributed locks, 2PC, Saga orchestration/choreography, and the Transactional Outbox pattern with message brokers.' faq: - { question: 'What is the difference between optimistic and pessimistic locking?', answer: 'Pessimistic locking prevents concurrency issues by locking database rows (using SELECT FOR UPDATE), blocking other transactions until the lock is released. Optimistic locking assumes conflicts are rare; it allows concurrent operations but checks a version or timestamp column during updates. If another transaction updated the row in the meantime, the update fails, and the application must retry.' } - { question: 'Why is Two-Phase Commit (2PC) rarely used in modern microservices?', answer: 'Two-Phase Commit is a synchronous, blocking protocol. It requires all participating services to lock resources during both phases. If one service slows down or goes offline, all other services remain blocked, creating a single point of failure and severely limiting scalability and system availability.' } - { question: 'How does the Transactional Outbox pattern guarantee at-least-once delivery?', answer: "Instead of publishing a message directly to a message broker during a database transaction (which could fail if the broker is offline), the service writes both the business data and the message payload to an 'outbox' table within the same local transaction. A separate publisher process polls this table asynchronously, sends the messages to the broker, and marks them as processed, ensuring no message is lost." } published: '2026-06-14' --- # Database and Distributed Transactions: Isolation, Locks, and Consensus Patterns Imagine a user trying to book the last available seat on a flight. Two requests arrive at the application servers at the exact same millisecond. If the system is not designed to handle concurrency, both requests read the seat status as "available", deduct the payment, and issue two tickets for the same seat. This classic double-booking problem is a nightmare for developers and businesses alike. In a monolithic system with a single database, SQL transactions can easily prevent this. But in a distributed system, where the seat booking service, the payment gateway, and the notification system are isolated microservices, a simple database transaction is no longer enough. Building robust transaction systems requires matching the locking granularity to the architectural scale, transitioning from simple SQL locks for local state to distributed consensus patterns for multi-service consistency. ## Table of Contents * [Local Database Transactions: ACID and Isolation Levels](#acid-isolation) * [Race Conditions: Pessimistic vs. Optimistic Locking](#locking-strategies) * [Distributed Locking at Scale: Redis-Based Locks](#redis-locks) * [Distributed Transactions: The Fall of Two-Phase Commit](#distributed-2pc) * [Eventual Consistency: Saga and Transactional Outbox](#saga-outbox) * [Practical Code Demonstration in PHP & Laravel](#code-demo) * [Limitations and Trade-offs](#limitations) * [Practical Takeaways](#takeaways) --- ## Local Database Transactions: ACID and Isolation Levels At the core of data integrity is the ACID model: Atomicity, Consistency, Isolation, and Durability. While Atomicity (all-or-nothing) and Durability (permanent storage) are straightforward, Isolation is where performance and correctness clash. Databases offer four standard isolation levels to manage concurrent access, each preventing different anomalies: 1. **Read Uncommitted**: The lowest level. A transaction can read uncommitted changes from another transaction (Dirty Reads). 2. **Read Committed**: Prevents dirty reads. A query only reads data committed before the query began. However, if it runs the query again, it might see changes committed by other transactions in the meantime (Non-repeatable Reads). 3. **Repeatable Read**: Prevents non-repeatable reads. The data read during the transaction is locked or version-consistent. In some databases, this does not prevent new rows from being inserted by other transactions (Phantom Reads). 4. **Serializable**: The highest level. Transactions are executed in a way that yields the same result as if they ran serially, eliminating all concurrency anomalies at the cost of severe performance limits. Most relational databases (like PostgreSQL and MySQL) default to **Read Committed** or **Repeatable Read**. To achieve total safety without sacrificing performance, developers must explicitly control locking rather than relying solely on the global database isolation level. --- ## Race Conditions: Pessimistic vs. Optimistic Locking When multiple clients attempt to modify the same database row concurrently, we must choose a locking strategy. ### Pessimistic Locking * **Point**: Assume conflicts are highly likely and block access proactively. * **Why it matters**: It prevents race conditions at the database level by using the database's internal locking mechanisms. * **Example**: Running `SELECT ... FOR UPDATE` locks the selected rows. Any other transaction trying to read those rows with `FOR UPDATE` or modify them will block until the first transaction commits or rolls back. * **Consequence**: High safety but poor concurrency. If transactions take a long time (e.g., waiting for external network requests), other database threads quickly exhaust the connection pool, bringing down the application. ### Optimistic Locking * **Point**: Assume conflicts are rare, allowing concurrent reads but validating during writes. * **Why it matters**: It avoids database locks entirely, maximizing read throughput and concurrency. * **Example**: Every row has a `version` (integer) or `updated_at` (timestamp) column. When updating, the SQL statement is structured as: ```sql UPDATE inventory SET quantity = quantity - 1, version = version + 1 WHERE id = 1 AND version = 3; ``` * **Consequence**: If another transaction updated the row first, the version would be 4, and this query would update 0 rows. The application detects this and decides whether to retry or fail. However, under high write contention, the frequent retries can degrade performance. --- ## Distributed Locking at Scale: Redis-Based Locks In high-concurrency applications, locking at the database level can overload the database CPU. Furthermore, if you need to lock a resource that isn't mapped to a single database row, or lock operations across different systems, database locks are insufficient. * **Point**: Use a fast, in-memory store like Redis to manage locks outside the relational database. * **Why it matters**: Redis operations are single-threaded and execution is atomic, making it extremely fast (sub-millisecond) and freeing database connections. * **Example**: A process acquires a lock using Redis `SET lock_key unique_token NX PX 5000` (set if Not eXists, expire after 5000ms). When releasing, a Lua script verifies that the current process holds the unique token before deleting the key. * **Consequence**: Highly scalable. However, if Redis goes down, or in a clustered environment where a master fails before replicating the lock to a replica (split-brain), duplicate locks could be issued. The Redlock algorithm solves this by acquiring locks across multiple independent Redis nodes. --- ## Distributed Transactions: The Fall of Two-Phase Commit When transitioning to a microservices architecture, data is split across service boundaries. A business transaction might span a Customer Service, an Inventory Service, and a Payment Service. Historically, the standard solution for distributed transactions was the **Two-Phase Commit (2PC)**: 1. **Prepare Phase**: An orchestrator queries all participating services if they are ready to commit. The services lock their local resources and reply "yes". 2. **Commit Phase**: If all services replied "yes", the orchestrator commands them to commit. If any replied "no", it commands all to rollback. While conceptually simple, 2PC is a **synchronous, blocking protocol**. If the network fails or a participating service goes offline during the commit phase, resources remain locked indefinitely. This severely degrades throughput and violates the CAP theorem's availability principle. Modern distributed systems trade atomic consistency for eventual consistency. --- ## Eventual Consistency: Saga and Transactional Outbox To achieve reliability across multiple microservices without blocking resources, we use alternative patterns. ### Saga Pattern A Saga is a sequence of local transactions. Each transaction updates the database within a single service and publishes an event. Other services listen to this event and execute their local transactions. If one transaction fails, the Saga runs **compensating transactions** backward to undo the changes. * **Choreography**: Services publish and subscribe to events without a central coordinator. Fast and decoupled, but hard to trace under complex workflows. * **Orchestration**: A central service orchestrates the workflow, calling services and managing compensating logic. Easier to model, but introduces a single point of orchestration. ### Transactional Outbox Pattern A common anti-pattern is publishing an event to a message broker (like RabbitMQ) inside a database transaction. If the broker is offline, the transaction rolls back. If the broker succeeds but the database fails to commit, you have ghost events. The **Transactional Outbox** solves this: 1. Write both the domain model changes and the event payload (to an `outbox` table) inside the same database transaction. 2. A separate background process (message relay) polls the `outbox` table, publishes the messages to the broker, and marks them as sent. 3. This guarantees **at-least-once delivery**. The receiver must implement **idempotency** (processing the same message twice yields the same result) to handle duplicate messages safely. --- ## Practical Code Demonstration in PHP & Laravel Here is how you can implement these patterns in a Laravel application. ### 1. Database Transaction with Pessimistic Locking ```php // app/Services/BookingService.php namespace App\Services; use Illuminate\Support\Facades\DB; use App\Models\FlightSeat; class BookingService { public function bookSeat(int $seatId, int $userId): bool { return DB::transaction(function () use ($seatId, $userId) { // Lock the row for update. Other threads calling this block here. $seat = FlightSeat::where('id', $seatId) ->lockForUpdate() ->first(); if (!$seat || $seat->is_booked) { return false; } $seat->update([ 'is_booked' => true, 'user_id' => $userId, ]); return true; }); } } ``` ### 2. Redis Distributed Locking ```php // app/Services/InventoryService.php namespace App\Services; use Illuminate\Support\Facades\Cache; class InventoryService { public function decreaseStock(int $productId, int $quantity): bool { $lockKey = "lock:product:{$productId}"; // Try to acquire the lock for 10 seconds, block up to 3 seconds waiting. $lock = Cache::lock($lockKey, 10); if ($lock->get()) { try { // Perform fast, non-blocking check and update // ... database updates without heavy locking return true; } finally { $lock->release(); } } return false; } } ``` ### 3. Transactional Outbox Insertion ```php // app/Services/OrderService.php namespace App\Services; use Illuminate\Support\Facades\DB; use App\Models\Order; use App\Models\OutboxMessage; class OrderService { public function createOrder(array $data): Order { return DB::transaction(function () use ($data) { $order = Order::create($data); // Record event inside the same transaction OutboxMessage::create([ 'event_type' => 'order.created', 'payload' => json_encode([ 'order_id' => $order->id, 'user_id' => $order->user_id, 'total' => $order->total_amount, ]), 'processed' => false, ]); return $order; }); } } ``` --- ## Limitations and Trade-offs * **Optimistic Locking**: Under extremely high concurrent write loads, optimistic updates will frequently fail, causing client requests to error out or forcing heavy CPU usage during retries. * **Redis Locks**: A lock is only as safe as its timeout value. If a process takes longer than the lock duration (e.g., due to a garbage collection pause), the lock expires, another process acquires it, and you lose safety. * **Sagas**: Compensating transactions cannot "rollback" side-effects like sending an email or charging a credit card. Instead, you must write pivot logic (e.g. reserving money instead of capturing, sending email drafts instead of final emails). --- ## Practical Takeaways 1. Use **Optimistic Locking** when read volume is high and conflicts are rare (e.g., editing articles, profile updates). 2. Use **Pessimistic Locking** (`SELECT ... FOR UPDATE`) for high-value local mutations with moderate contention where conflicts must be handled inside the DB (e.g., banking ledger updates). 3. Use **Redis Locks** to guard application operations and prevent multiple workers from running identical jobs simultaneously. 4. For microservices, avoid synchronous distributed transactions (like 2PC). Implement the **Saga Pattern** for orchestration and the **Transactional Outbox Pattern** to guarantee reliable asynchronous messaging.