Skip to content

Pessimistic Locking

In one line

Pessimistic locking holds locks on rows — blocks other transactions from modifying them until commit/rollback.

In simple words

Pessimistic locking assumes conflicts will happen. It acquires locks on rows before reading or modifying them, preventing other transactions from accessing those rows until the lock is released (commit or rollback).

Types:

  1. Shared lock (read lock) — multiple transactions can read, none can write
  2. Exclusive lock (write lock) — only one transaction can access (read or write)

SQL: SELECT ... FOR UPDATE (exclusive), SELECT ... FOR SHARE (shared)

Benefits:

  1. Strong consistency — no conflicts possible while locked
  2. Predictable — once you have the lock, you're guaranteed exclusive access

Drawbacks:

  1. Reduced concurrency — other transactions wait
  2. Deadlock risk — multiple locks can deadlock
  3. Long waits — if a transaction holds a lock for a long time

Best when conflicts are common or data consistency is critical (banking, inventory).

Example

sql
-- Pessimistic locking with FOR UPDATE
BEGIN;
-- Lock the row for this transaction
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Other transactions trying to FOR UPDATE on this row will WAIT

-- Safely update (no one else can modify this row)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT; -- Lock released, other transactions can proceed

-- Shared lock (multiple readers, no writers)
SELECT * FROM products WHERE category = 'electronics' FOR SHARE;
-- Other transactions can also FOR SHARE, but not FOR UPDATE

FOR UPDATE locks the row exclusively. Other transactions wait. This prevents conflicts but reduces concurrency. Use when conflicts are likely.

Common confusions

  • Confused with: optimistic locking

    The difference: Pessimistic locking holds locks during the transaction, blocking others. Optimistic locking doesn't lock — it checks versions at update time. Pessimistic is better when conflicts are common; optimistic is better when conflicts are rare.

Related terms

Related Resources

Related Lessons

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to glossary