0Pricing
SQL Academy · Lesson

Optimistic vs Pessimistic Locking

Compare SELECT ... FOR UPDATE (pessimistic) and version-column / WHERE updated_at = ? (optimistic) patterns.

Optimistic vs Pessimistic Locking is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Two Concurrency Strategies

  • Pessimistic — lock the row when you read it; nobody else can change it
  • Optimistic — don't lock; on update, verify the row hasn't changed

Pessimistic: SELECT ... FOR UPDATE

Lock now, write later:

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- other transactions cannot lock or update this row
-- compute new balance...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

FOR SHARE

Read-lock — others can read but not write:

SELECT * FROM orders WHERE id = 1 FOR SHARE;
-- others can SELECT FOR SHARE but cannot UPDATE

Pessimistic Pros and Cons

Pros: simple to reason about, no retry needed.
Cons: reduces concurrency, can cause lock waits and deadlocks.

Optimistic: Version Column

Read with version, write with WHERE version = expected:

BEGIN;
SELECT id, balance, version FROM accounts WHERE id = 1;
-- compute new balance...
UPDATE accounts
   SET balance = ?, version = version + 1
WHERE id = 1 AND version = ?;
-- check rows affected: 0 means someone else updated, retry

Optimistic with updated_at

Same idea, using updated_at instead of an explicit version column:

UPDATE accounts
   SET balance = ?, updated_at = NOW()
WHERE id = ? AND updated_at = ?;

-- If updated_at has changed in the meantime, 0 rows affected — retry.

Optimistic Pros and Cons

Pros: high concurrency, no waiting.
Cons: writes can fail and require retry logic; the conflict only surfaces at UPDATE time.

When to Pick Pessimistic

For:

  • Short transactions with high contention on hot rows
  • Money transfers — you don't want partial work
  • Long-running operations where conflict is likely

When to Pick Optimistic

For:

  • Read-mostly workloads with rare conflicts
  • Stateless APIs where the client holds the row between requests
  • Mobile / offline edit followed by sync

Hybrid: FOR UPDATE NOWAIT

Try to lock; if locked, immediately fail and let the user retry:

SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR if someone else holds it — user sees a friendly retry message

Advisory Locks

App-level locks not tied to any row:

SELECT pg_try_advisory_xact_lock(hashtext('order:42'));
-- True if you got the lock, false otherwise — useful for cross-row coordination.

Don't Forget to Index Lock Targets

FOR UPDATE without an index on the WHERE column may lock more rows than expected (locks scanned rows, not just matching).

Lock Timeouts

Set lock_timeout to avoid waiting forever:

SET lock_timeout = '5s';
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- ERROR if lock not acquired in 5 seconds

Recap

Pessimistic locks the row; optimistic checks at write time.

  • Pessimistic: FOR UPDATE — simple but reduces concurrency
  • Optimistic: version column — more concurrent, requires retry
  • Pick per workload; combine when needed

Quick Check

An e-commerce stock decrement is highly contended. Which locking strategy is generally safer?

Frequently asked questions

Is the “Optimistic vs Pessimistic Locking” lesson free?

Yes — the full text of “Optimistic vs Pessimistic Locking” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Optimistic vs Pessimistic Locking”?

Compare SELECT ... FOR UPDATE (pessimistic) and version-column / WHERE updated_at = ? (optimistic) patterns. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Optimistic vs Pessimistic Locking” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. ACID Properties and Anomalies
  2. Isolation Levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE
  3. Deadlocks: Detection and Avoidance
  4. Optimistic vs Pessimistic Locking
← Back to SQL Academy