Optimistic and Pessimistic Concurrency Patterns

Learning outcomes

Concurrency control strategies differ mainly in when conflicts are handled. Pessimistic control prevents conflicting work by locking early. Optimistic control allows concurrent work and detects conflicts when updating or committing.

Pessimistic concurrency

Pessimistic control assumes conflicts are likely or costly and acquires locks before making the decision.

BEGIN;SELECT *FROM work_orderWHERE work_order_id = ?FOR UPDATE;-- validate and modifyCOMMIT;

What the row lock accomplishes

Other transactions attempting conflicting locks on the same row wait until the first transaction commits or rolls back.

Pessimistic use case

Closing a WorkOrder may lock the WorkOrder row while checking assignments so competing commands coordinate around the same parent.

Lock scope matters

Lock too little and the invariant remains exposed. Lock too much and throughput collapses. Choose a stable row/entity that represents the business contention boundary.

Deadlocks

Deadlock example:

Transaction A locks WorkOrder 1Transaction B locks WorkOrder 2A requests WorkOrder 2B requests WorkOrder 1

The DBMS detects the cycle and aborts one transaction.

Reduce deadlocks with consistent lock order

If multi-row operations always lock rows by ascending key, many cyclic lock patterns disappear.

Optimistic concurrency

Optimistic control assumes most concurrent operations do not conflict. Each row carries a version:

work_order_idstatus_codeversion

Versioned update

UPDATE work_orderSET status_code = 'closed',    version = version + 1WHERE work_order_id = 100  AND version = 7;

If the affected-row count is zero, someone else changed the row after it was read.

What to do after optimistic conflict

Options include:

• reload and ask the user to resolve;
• retry automatically if the operation is safe;
• merge non-conflicting field changes;
• reject with a conflict response.

Optimistic control works well when

• conflicts are relatively rare;
• reads are frequent;
• locks would remain open too long;
• user edits occur outside database transactions.

Pessimistic control works well when

• conflicts are common;
• the operation is short;
• rework after conflict is expensive;
• the invariant naturally centers on a lockable row.

Atomic SQL beats both when possible

For inventory:

UPDATE part_inventorySET available_qty = available_qty - :qtyWHERE part_id = :part_id  AND available_qty >= :qty;

This can avoid an explicit read-before-write race.

Unique constraints as concurrency control

A unique constraint can serialize the business rule at the database level:

UNIQUE (work_order_id, technician_id, started_at)

or a partial uniqueness rule for one active primary assignment.

Compare strategies

WorkshopHub decision examples

• Editing WorkOrder notes: optimistic version may be enough.
• Closing WorkOrder: parent lock or serializable transaction may be appropriate.
• Inventory decrement: atomic update is preferable.
• One active primary assignment: unique partial constraint when supported.

Practice: choose a strategy

Summary and next lesson

Pessimistic control blocks conflicting work early; optimistic control detects conflicts later. Declarative constraints and atomic updates are often even better because they make invariants directly enforceable. The final lesson addresses what happens when operations are retried, duplicated, or interrupted: idempotency and safe state transitions.

References

• PostgreSQL documentation on explicit locking and MVCC.
• Martin Kleppmann, Designing Data-Intensive Applications.
• Jim Gray and Andreas Reuter, Transaction Processing: Concepts and Techniques.