Mastering Pessimistic Locking in PostgreSQL: A Production-Ready Guide

As systems scale and concurrency increases, maintaining data integrity without sacrificing performance becomes a core architectural challenge. Pessimistic locking is a foundational concurrency control strategy that guarantees consistency by proactively securing resources. This guide synthesizes deep-dive discussions on its mechanics, PostgreSQL-specific implementations, transactional semantics, lock compatibility, and modern ORM integration, structured for senior engineering practice.

1. Fundamentals of Pessimistic Locking

Core Concept & Mechanism

Pessimistic locking operates on the assumption that data conflicts are highly likely. Instead of hoping for the best and retrying (like optimistic locking), it proactively locks resources before any read or write operation to prevent concurrent access from causing inconsistencies.

Lifecycle:

1. A transaction requests a lock before accessing data.
2. The lock is granted; other transactions are blocked or forced to wait.
3. Once the transaction COMMITs or ROLLBACKs, the lock is automatically released.
4. Waiting transactions proceed in turn.

Common Lock Types

Pros & Cons

Typical Use Cases

• Financial transactions (banking, payments)
• Inventory/stock reservation systems
• High-contention rows/tables where conflicts are frequent
• Systems where data integrity outweighs performance scalability

Pessimistic vs Optimistic Locking

💡 Architectural Note: Modern systems often combine both: pessimistic locks for critical write paths, optimistic/version-based control for reads or low-conflict writes, leveraging MVCC (Multi-Version Concurrency Control) under the hood.

2. PostgreSQL's Implementation: Row & Table Locks

PostgreSQL implements pessimistic locking primarily through SELECT ... FOR ... clauses (row-level) and the LOCK TABLE statement (table-level). All locks are held until the end of the transaction.

🔹 Row-Level Locks (SELECT ... FOR ...)

🔧 Lock Acquisition Modifiers

• NOWAIT: Fails immediately with an error if the lock can't be acquired instead of waiting.
• SKIP LOCKED: Ignores already-locked rows and returns only unlocked ones. Ideal for job queues or task distribution. (Available since PostgreSQL 9.5)

🗄️ Table-Level Locks (LOCK TABLE)

Explicit table locks are rarely needed in application logic (PostgreSQL auto-acquires appropriate table locks during DML), but useful for maintenance or strict serialization.

Common modes:

⚠️ PostgreSQL-Specific Behaviors & Best Practices

1. Transaction Scope: All row/table locks are held until COMMIT/ROLLBACK or session end.
2. MVCC Doesn't Block Plain SELECT: Unlocked SELECT reads snapshot data and never blocks writers. Pessimistic locks only affect other locking statements or writes.
3. Deadlock Detection: PG automatically detects deadlocks (default timeout: 1 sec, configurable via deadlock_timeout). It aborts one transaction to break the cycle.
4. Index Impact: Row locks are also acquired on index entries pointing to the locked rows.
5. Avoid Long Transactions: Hold locks for the shortest time possible to prevent contention and deadlocks.
6. Prefer SKIP LOCKED for Queues: Much safer and more scalable than NOWAIT + retry loops for worker pools.

🛠️ Practical Example: End-to-End Concurrency Workflow

To see how these lock types work in tandem, consider a high-traffic e-commerce system handling inventory, order validation, status updates, and background processing.

🔹 Scenario 1: Strict Inventory Deduction (FOR UPDATE)

Prevent overselling by locking the exact product row during checkout.

BEGIN;
-- Exclusively lock the row. Other sessions trying to buy this SKU will wait.
SELECT id, stock FROM products WHERE sku = 'LAPTOP-X' FOR UPDATE;
-- Verify stock, deduct, and commit atomically
UPDATE products SET stock = stock - 1 WHERE sku = 'LAPTOP-X';
COMMIT;

🔹 Scenario 2: Order Validation Before Invoicing (FOR SHARE)

Validate pricing/taxes without blocking other readers, but prevent concurrent price changes during calculation.

BEGIN;
-- Allow concurrent reads, but block any price/total updates during validation
SELECT order_id, total, status FROM orders WHERE order_id = 8842 FOR SHARE;
-- ... run pricing engine, apply tax rules, verify discounts ...
INSERT INTO invoices (order_id, amount) VALUES (8842, ...);
COMMIT;

🔹 Scenario 3: High-Frequency Status Updates (FOR NO KEY UPDATE)

Update shipment_status or tracking_id without bottlenecking foreign key checks on child tables (e.g., order_items).

BEGIN;
-- Locks row data but skips PK/unique index locks, allowing FK validations to proceed
SELECT id FROM orders WHERE order_id = 8842 FOR NO KEY UPDATE;
UPDATE orders SET tracking_id = '1Z999AA1', status = 'shipped' WHERE order_id = 8842;
COMMIT;

🔹 Scenario 4: Distributed Worker Queue (SKIP LOCKED)

Multiple background workers pulling email_notification tasks without deadlocking or duplicating work.

BEGIN;
-- Grabs the next unlocked pending task, skipping rows already locked by other workers
SELECT id, payload FROM notifications
WHERE status = 'pending'
ORDER BY created_at ASC
LIMIT 1 FOR UPDATE SKIP LOCKED;
-- Process notification...
UPDATE notifications SET status = 'sent', processed_at = NOW() WHERE id = ...;
COMMIT;

🔹 Scenario 5: Flash Sale Seat Reservation (NOWAIT)

User clicks "Reserve". If another transaction already holds it, fail instantly rather than hanging the HTTP connection.

BEGIN;
SELECT id FROM tickets WHERE event_id = 101 AND status = 'available'
LIMIT 1 FOR UPDATE NOWAIT;
-- If successful, mark as reserved. 
-- If error (SQLSTATE 55P03), catch and return "Sold Out / Try Another" to UI.
UPDATE tickets SET status = 'reserved', reserved_until = NOW() + INTERVAL '15 min' WHERE id = ...;
COMMIT;

3. The Transaction Imperative

Why Pessimistic Locks Require Transactions

In relational databases (PostgreSQL, MySQL, Oracle, SQL Server, etc.), pessimistic locks are strictly tied to transactions. Locks need a defined lifecycle: acquire → do work → release. The transaction boundary provides exactly that. Without a transaction, the database defaults to releasing locks immediately to avoid resource leaks.

⚠️ The Auto-Commit Trap

Most database drivers run in auto-commit mode by default. Each statement is treated as its own implicit transaction.

-- ❌ USELESS in auto-commit mode
SELECT id, balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Lock is acquired... and immediately released when this statement finishes.
-- The next UPDATE runs WITHOUT protection. Race conditions are still possible.
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- ✅ CORRECT: Explicit transaction
BEGIN;
SELECT id, balance FROM accounts WHERE id = 1 FOR UPDATE; -- Lock held
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Protected
COMMIT; -- Lock released

💡 Rule of thumb: If you use FOR UPDATE, FOR SHARE, or LOCK TABLE, you must wrap the read-modify-write sequence in an explicit transaction.

Non-Transactional Locks (Exceptions)

Some systems offer lock primitives that don't require DML transactions, but they work differently:

4. Deep Dive: FOR UPDATE vs FOR NO KEY UPDATE

The Core Difference: "Key" = Primary/Unique Indexes & Foreign Keys

Both locks prevent concurrent UPDATE/DELETE on the row data itself. The distinction lies in index entries and foreign key validation:

• FOR UPDATE locks everything: row data + all index entries (PK, unique keys). This blocks FK validation on child tables.
• FOR NO KEY UPDATE locks row data only, skipping PK/unique index entries. Since FK validation only needs FOR KEY SHARE, it doesn't block.

📦 Side-by-Side Production Example

Schema Setup:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  status TEXT DEFAULT 'active',
  balance NUMERIC(12,2) DEFAULT 0
);
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id) ON DELETE RESTRICT,
  total NUMERIC(10,2),
  created_at TIMESTAMPTZ DEFAULT NOW()
);
INSERT INTO customers (id, name, status, balance) VALUES (100, 'Acme Corp', 'active', 5000.00);

Scenario A: Using FOR UPDATE → Blocks FK Insert

-- Session 1
BEGIN;
SELECT * FROM customers WHERE id = 100 FOR UPDATE; -- Holds strongest lock

-- Session 2
INSERT INTO orders (customer_id, total) VALUES (100, 50.00);
-- ⏳ BLOCKS indefinitely. Waiting for Session 1 to COMMIT/ROLLBACK.

Why? FOR UPDATE ❌ conflicts with FOR KEY SHARE (auto-acquired for FK check).

Scenario B: Using FOR NO KEY UPDATE → Allows FK Insert

-- Session 1
BEGIN;
SELECT * FROM customers WHERE id = 100 FOR NO KEY UPDATE;

-- Session 2
INSERT INTO orders (customer_id, total) VALUES (100, 50.00);
-- ✅ SUCCEEDS IMMEDIATELY. Order created without waiting.

Why? FOR NO KEY UPDATE ✅ is compatible with FOR KEY SHARE. FK validation proceeds.

✅ Corrected Lock Compatibility Matrix (PostgreSQL Official)

How to Read:

1. Pick the lock Session A already holds (left column).
2. Pick the lock Session B is trying to acquire (top row).
3. Intersection shows if Session B proceeds (✅) or blocks (❌).

🤖 How PostgreSQL Uses Them Automatically

You rarely need to type FOR NO KEY UPDATE manually. PG chooses automatically:

UPDATE users SET status = 'active' WHERE id = 1;          -- → FOR NO KEY UPDATE
UPDATE users SET email = 'new@example.com' WHERE id = 1;  -- → FOR UPDATE (if email is UNIQUE)

🧭 When to Choose Which

💡 FOR NO KEY UPDATE can improve throughput by 20-40% in FK-heavy workloads. It's a concurrency optimization, not a safety downgrade.

5. Decoding Database Concurrency: Sessions, Transactions & Queries

A common point of confusion in concurrency documentation is the hierarchy of database execution contexts.

The Hierarchy

🌐 Session (1 Database Connection)
│
├─▶ Transaction 1 (BEGIN → queries → COMMIT/ROLLBACK)
│      ├─ Query 1
│      ├─ Query 2
│      └─ Query 3
│
├─▶ Transaction 2
│      └─ Query 4
│
└─▶ ... (session stays alive until disconnected)

How Sessions Relate to Pessimistic Locks

1. Locks are held per session, but scoped to the active transaction.
2. When COMMIT/ROLLBACK executes, the lock is released, but the session/connection stays open.
3. In production, connection pools (PgBouncer, HikariCP, SQLAlchemy pool) borrow sessions per request/thread. When the transaction ends, the session returns to the pool and locks are released.

✅ Bottom Line: When docs say "Session A holds a lock, Session B tries to acquire one", they mean two separate connections running in parallel. Locks live on the session but die when the session's transaction ends.

6. Practical Decision Framework & Real-World Scenarios

🎯 Quick Reference Matrix

🧭 Decision Flow

Are multiple workers/threads competing for rows?
├─ YES → Use SKIP LOCKED (queues) or NOWAIT (user-facing)
└─ NO → Need to prevent concurrent writes?
    ├─ YES → Use FOR UPDATE (strict) or FOR NO KEY UPDATE (frequent updates)
    └─ NO → Rely on MVCC (plain SELECT). Pessimistic lock not needed.

⚠️ Real-World Gotchas

1. Lock escalation: Locking 10,000 rows with FOR UPDATE can exhaust lock memory. Batch or paginate.
2. HTTP timeouts: NOWAIT + retry logic > long waits. Never hold locks across network calls.
3. ORM pitfalls: Django select_for_update(), SQLAlchemy with_for_update(), Hibernate PESSIMISTIC_WRITE all default to FOR UPDATE. Know what your ORM emits.
4. Deadlocks: Order locks consistently (e.g., always lock sender_id before receiver_id). PG detects & aborts after deadlock_timeout (default 1s).
5. MVCC vs Locks: Plain SELECT never blocks writers. Only use pessimistic locks when application logic requires write exclusion.

7. Implementing in TypeORM (Node.js/TypeScript)

TypeORM fully abstracts PostgreSQL's pessimistic locking into a unified API. Below are production-ready patterns.

📖 TypeORM Lock Mode → PostgreSQL Mapping

⚠️ pessimistic_partial_write falls back to FOR UPDATE on MySQL/SQLite. It's PostgreSQL-specific.

🛠️ Usage Patterns

Basic Lock (Repository / EntityManager)

// Requires explicit transaction!
await dataSource.transaction(async (manager) => {
  const account = await manager.findOne(Account, {
    where: { id: 1001 },
    lock: { mode: "pessimistic_write" } // → FOR UPDATE
  });
  if (!account) throw new Error("Not found");
  account.balance -= 100;
  await manager.save(account);
});

Advanced: NOWAIT & SKIP LOCKED (QueryBuilder only)

Requires TypeORM 0.3.0+

await dataSource.transaction(async (manager) => {
  const job = await manager
    .createQueryBuilder(Job, "job")
    .where("job.status = :status", { status: "pending" })
    .orderBy("job.priority", "DESC")
    .setLock("pessimistic_write", { skipLocked: true }) // → FOR UPDATE SKIP LOCKED
    .getOne();
    
  if (!job) return null;
  job.status = "processing";
  job.workerId = workerId;
  await manager.save(job);
});

// For NOWAIT:
// .setLock("pessimistic_write", { nowait: true })

📦 Real-World Example: Inventory Deduction

import { DataSource } from "typeorm";

async function reserveInventory(dataSource: DataSource, sku: string, qty: number) {
  return dataSource.transaction(async (manager) => {
    // 1. Lock row FOR NO KEY UPDATE (optimizes FK contention)
    const product = await manager
      .createQueryBuilder(Product, "p")
      .where("p.sku = :sku", { sku })
      .setLock("pessimistic_partial_write")
      .getOne();
      
    if (!product || product.stock < qty) {
      throw new Error("Insufficient stock");
    }
    
    // 2. Atomic update
    product.stock -= qty;
    await manager.save(product);
    
    // 3. Create order (FK check won't block due to NO KEY UPDATE)
    const order = manager.create(Order, { productId: product.id, qty });
    return manager.save(order);
  });
}

⚠️ Critical TypeORM Pitfalls

✅ Best Practice Wrapper Pattern

async function withPessimisticLock<T>(
  dataSource: DataSource,
  entityClass: EntityTarget<T>,
  id: any,
  lockMode: LockMode,
  callback: (entity: T, manager: EntityManager) => Promise<void>
) {
  return dataSource.transaction(async (manager) => {
    const entity = await manager.findOneOrFail(entityClass, {
      where: { id },
      lock: { mode: lockMode }
    });
    await callback(entity, manager);
    // lock auto-releases on COMMIT
  });
}

8. Verification & Monitoring

🔍 Verify Generated SQL

Enable query logging temporarily to confirm TypeORM emits the correct clauses:

const dataSource = new DataSource({ /* ... */, logging: ["query"] });

Expected output:

SELECT "Product"."id", "Product"."sku", "Product"."stock"
FROM "product" "Product"
WHERE "Product"."sku" = $1
FOR NO KEY UPDATE

🛠️ Real-Time Lock Monitoring

Run this in a third session while transactions are active:

SELECT pid, mode, granted, query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE relation = 'your_table'::regclass;

Conclusion

Pessimistic locking is not a silver bullet, but it is an indispensable tool for high-contention, high-integrity workloads. By understanding PostgreSQL's nuanced lock modes (FOR UPDATE vs FOR NO KEY UPDATE), respecting strict transactional boundaries, clarifying session vs transaction scopes, and leveraging modern ORM patterns correctly, you can prevent race conditions without crippling throughput.

Architectural Takeaway: Always align your locking strategy with your access patterns. Use FOR NO KEY UPDATE for high-frequency non-PK mutations, SKIP LOCKED for distributed queues, NOWAIT for user-facing timeouts, and reserve strict FOR UPDATE for critical financial or inventory paths. Monitor pg_locks and pg_stat_activity proactively, keep transactions short, and never hold database locks across external service boundaries.