Optimistic vs Pessimistic Locking in SQL
Introduction
Optimistic and pessimistic locking are two strategies for protecting shared database state when several users or transactions may modify it. Optimistic locking lets work proceed without reserving the record for the entire business operation and checks for change when saving. Pessimistic locking reserves current database resources before a critical read-and-write sequence.
The strategies solve different conflict shapes. Optimistic control works well for long user edits and low collision rates. Pessimistic control fits short, high-value operations where collisions are likely or where work must be based on the latest locked state. Neither strategy replaces database constraints, atomic SQL, or correct transaction boundaries.
Why Locking Strategy Matters
Two administrators can open the same employee record at salary 50,000. Administrator A saves 55,000, then Administrator B saves 60,000 from the older screen. A blind update silently loses A's change. Optimistic locking detects B's stale version; pessimistic locking can make B wait before entering the protected database operation.
The choice affects throughput, user experience, latency, deadlock risk, retry behavior, and implementation complexity. A strategy should follow the business invariant and expected contention rather than a framework default.
Basic Difference
| Factor | Optimistic locking | Pessimistic locking |
|---|---|---|
| Conflict timing | Detected when writing or validating | Coordinated before or during access |
| Main assumption | Conflicts are uncommon | Conflicts are likely or expensive |
| Long user edits | Suitable | Do not hold an open DB lock |
| Main failure | Stale version or validation conflict | Wait, timeout, or deadlock |
| Main cost | Discarded work and conflict handling | Blocking and reduced concurrency |
What Is Optimistic Locking?
Optimistic locking is an application-level concurrency strategy that records the state originally read and includes a concurrency token in the later update condition. If another transaction changed the row, the token no longer matches and the update affects zero rows.
The name does not mean the database performs the final update without locks. The update still uses normal write coordination. Optimistic refers to avoiding a long reservation between the initial read and the attempted save.
How Optimistic Locking Works
- User A and User B read article version 1.
- Both edit independently without an open transaction.
- A updates with
WHERE version_number = 1. - A succeeds and changes the version to 2.
- B attempts the same version-1 condition.
- B affects zero rows and receives a conflict.
The application must treat zero affected rows as a meaningful concurrency outcome, not as successful persistence.
Optimistic Locking Table
CREATE TABLE articles (
article_id INTEGER PRIMARY KEY,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
version_number INTEGER NOT NULL DEFAULT 1
);
INSERT INTO articles
(article_id, title, content, version_number)
VALUES
(101, 'SQL Basics', 'Introduction to SQL', 1);
The version belongs to the persisted aggregate being protected. It should not be accepted from an untrusted client as an arbitrary replacement value; the client sends the version it originally observed.
Version-Based Update
UPDATE articles
SET title = ?,
content = ?,
version_number = version_number + 1
WHERE article_id = ?
AND version_number = ?;
Incrementing in the statement avoids calculating the new version in application memory. Affected-row count one means success. Count zero can mean a stale version or missing record, so the application may query current state to present the correct domain response.
Two-User Timeline
| Time | User A | User B |
|---|---|---|
| T1 | Reads version 1 | Reads version 1 |
| T2 | Edits title | Edits content |
| T3 | Saves version 1, succeeds as version 2 | Still editing |
| T4 | Complete | Saves version 1, affects zero rows |
| T5 | Complete | Reloads, merges, or cancels |
Why a Dedicated Version Column Helps
An integer version is explicit, cheap to compare, and changes exactly when the application decides the protected record changed. It avoids ambiguity from comparing many nullable fields and is easier to expose as an ETag or concurrency token.
All write paths must increment it. A maintenance script, trigger, bulk update, or secondary service that changes protected state without advancing the version defeats conflict detection. Centralized persistence rules and tests are therefore important.
Using Timestamps as Tokens
A database-generated last-modified timestamp can be used as a token, but precision and generation rules matter. Two updates may receive the same coarse timestamp, application clocks may differ, and conversion between time zones or client formats can alter equality.
Prefer a database-managed token with sufficient precision and include it exactly in the update predicate. Integer versions or vendor-native row-version values are often more reliable when the timestamp has business meaning beyond concurrency.
Comparing Original Field Values
An application can include every original field in the update condition instead of using one token. This detects changes but produces large predicates, complicated NULL comparisons, and fragile behavior when new columns appear. It can be useful for selective merge semantics, but a dedicated version is simpler for aggregate-level conflict detection.
Advantages of Optimistic Locking
- No database transaction remains open during user think time.
- Readers and editors do not block one another before save.
- It scales well when collisions are rare.
- Stale updates become visible instead of silently overwriting data.
- The token can support HTTP ETag and conditional request patterns.
- Version history and audit workflows integrate naturally.
Disadvantages of Optimistic Locking
A conflict is discovered after a user or process has completed work. Merging can be difficult, especially when two changes affect the same business concept. Under heavy contention, repeated failed updates waste CPU, database work, and user time.
Optimistic row versions also do not automatically protect predicates spanning many rows. Two transactions can update different rows and violate a shared capacity rule without either token conflicting.
Conflict Resolution Experience
A good application does not display a generic database error. It can show the current saved value, the user's attempted value, and the fields that changed. Independent edits may be merged explicitly; conflicting fields should require a deliberate decision.
Automatic retry is appropriate when the operation can be safely recalculated from current state, such as incrementing a counter. Blindly reapplying an old full-record replacement defeats the purpose of detecting the conflict.
What Is Pessimistic Locking?
Pessimistic locking acquires database protection before performing a critical sequence that depends on current state. Another transaction requesting incompatible access waits, fails immediately, or skips the locked candidate depending on SQL options and engine behavior.
The strategy is pessimistic about collision, not about system quality. It is useful when concurrent conflict is likely and the transaction is short enough that waiting is cheaper than performing work that will later be rejected.
How Pessimistic Locking Works
- Begin a database transaction.
- Select the current row using a locking read.
- Validate business conditions after acquiring protection.
- Perform the required update on the same connection.
- Commit or roll back promptly.
- The database releases transaction-duration locks.
No user input or remote service call should occur while the transaction remains open.
Inventory Table
CREATE TABLE inventory (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
available_quantity INTEGER NOT NULL,
CHECK (available_quantity >= 0)
);
INSERT INTO inventory
(product_id, product_name, available_quantity)
VALUES (500, 'Mechanical Keyboard', 1);
SELECT FOR UPDATE Example
BEGIN;
SELECT available_quantity
FROM inventory
WHERE product_id = 500
FOR UPDATE;
UPDATE inventory
SET available_quantity = available_quantity - 1
WHERE product_id = 500
AND available_quantity > 0;
COMMIT;
A competing locking read or update on the same protected row commonly waits. The condition remains on the update because every write should defend its own invariant.
Two-Session Pessimistic Example
Session A begins and locks product 500. Session B begins and requests the same row, then waits. A validates stock, updates, and commits. B wakes and reads the current quantity of zero, so it rejects the purchase. The lock coordinated the decisions rather than allowing both to rely on quantity one.
NOWAIT
NOWAIT requests an immediate lock error instead of waiting. It is useful when the application can tell a user that the resource is busy or route work elsewhere. Syntax and supported lock types vary by database.
Immediate failure still requires transaction cleanup and a domain decision. Repeating instantly in a tight loop merely converts blocking into retry pressure.
SKIP LOCKED
SKIP LOCKED omits rows that another transaction currently protects. It is valuable for job queues where several workers can claim different available jobs. It intentionally returns an incomplete result, so it is inappropriate for totals, reports, financial checks, or any query that requires every matching row.
Pessimistic Locking in SQL Server
SQL Server commonly expresses locking intent through transaction isolation and table hints such as UPDLOCK or HOLDLOCK. ROWLOCK is a preference rather than an absolute guarantee. The exact resource and mode depend on the plan, indexes, isolation, escalation, and other hints.
BEGIN TRANSACTION;
SELECT available_quantity
FROM inventory WITH (UPDLOCK, HOLDLOCK)
WHERE product_id = 500;
-- Validate and update here.
COMMIT;
Hints create vendor coupling and should follow measured evidence and documented semantics, not habit.
Locking Strategies in PostgreSQL
PostgreSQL ordinary reads use MVCC snapshots and do not reserve rows against writers. SELECT ... FOR UPDATE locks selected current rows against conflicting updates and locking modes, while FOR NO KEY UPDATE, FOR SHARE, and FOR KEY SHARE express narrower intentions. NOWAIT and SKIP LOCKED modify waiting behavior for supported locking clauses.
Optimistic version columns work normally at Read Committed. A deadlock generally reports SQLSTATE 40P01, while a Serializable conflict reports 40001; both differ from a version update that simply affects zero rows. The application should preserve these distinct outcomes for diagnostics and domain handling.
Locking Strategies in MySQL InnoDB
InnoDB combines MVCC consistent reads with locking reads. Under common Repeatable Read behavior, an ordinary query can use a transaction snapshot, while SELECT ... FOR UPDATE performs a current locking read. Depending on the predicate and index, InnoDB can acquire record, gap, or next-key locks, so a range query may protect more than the existing rows returned.
A selective index is therefore part of pessimistic-lock design. Optimistic version updates remain portable, but the application must still classify InnoDB deadlocks and lock-wait timeouts separately from zero-row version conflicts. Retrying a timeout automatically should follow product and business requirements rather than assuming every wait failure is transient.
Locking Strategies in Oracle
Oracle uses multiversion read consistency for ordinary queries, so readers generally do not block writers. SELECT ... FOR UPDATE reserves selected current rows for a later change and supports wait-control variants such as NOWAIT, WAIT, and SKIP LOCKED. The transaction must remain short because those row locks persist until commit or rollback.
An application version column is still valuable for long edits that span requests. Oracle's snapshot and locking behavior does not automatically detect that a web form was based on old data in a previous transaction. Version checking provides that cross-request contract, while constraints and transaction locking protect the final database operation.
Locking Strategies in SQLite
SQLite does not provide server-style row locking or SELECT FOR UPDATE. It coordinates access at the database-file level, with different reader-writer behavior in rollback-journal and WAL modes and only one writer proceeding at a time. Pessimistic designs from client-server databases must therefore be reconsidered rather than translated literally.
Optimistic version columns work well for stale-edit detection. Short write transactions, conditional updates, an appropriate transaction mode, and a bounded busy timeout handle local write competition. Applications should distinguish a zero-row stale version from SQLITE_BUSY or SQLITE_LOCKED, because the user response and retry policy are different.
Database Behavior Comparison
| Database | Ordinary read model | Pessimistic approach |
|---|---|---|
| PostgreSQL | MVCC snapshots | FOR UPDATE family with wait options |
| MySQL InnoDB | MVCC consistent reads | Locking reads with record and range effects |
| SQL Server | Locking or configured row versioning | Isolation and documented table hints |
| Oracle | Multiversion read consistency | FOR UPDATE with wait options |
| SQLite | Journal-mode snapshots | Transaction-level file coordination |
Optimistic token checking is broadly portable, but token generation, update counts, transaction failure state, and retryable error codes still require database-specific integration tests.
Advantages of Pessimistic Locking
- Conflict is coordinated before dependent work completes.
- The transaction validates against current protected state.
- It fits scarce-resource allocation and high-contention operations.
- It can simplify short read-then-write sequences.
- NOWAIT and queue-oriented options offer explicit wait behavior.
Disadvantages of Pessimistic Locking
Locks reduce concurrency and can create long waits, timeouts, deadlocks, and latency spikes. A slow transaction can block many unrelated requests if its query scans or locks a broad range. Connection loss and incomplete cleanup increase operational risk.
It is especially unsuitable for a web form left open while a user thinks. Database locks should protect milliseconds or short seconds of database work, not a human editing session.
Detailed Comparison
| Question | Optimistic answer | Pessimistic answer |
|---|---|---|
| When is conflict handled? | At update or validation | At lock acquisition |
| Does work wait? | Usually not before save | Possibly |
| Can work be discarded? | Yes, after conflict | Usually less application work |
| Deadlock exposure | Still possible during writes | Higher with multiple locked resources |
| Best transaction duration | Short save transaction | Very short protected transaction |
| Typical example | Document editing | Inventory reservation |
Hybrid Strategies
Real systems often combine both approaches. A document uses a version for user edits, while its short publish transition locks the publication record. Inventory uses an atomic conditional update for ordinary purchases and a locked batch allocator for complex reservations. A high-conflict workflow can switch from optimistic attempts to a queue or explicit lock after measured contention.
The combination must still have one authoritative invariant. Multiple techniques should reinforce one another rather than create different definitions of success.
Atomic SQL May Be Better Than Either
When one statement can express the complete decision, use it. A conditional decrement checks and changes stock atomically:
UPDATE inventory
SET available_quantity = available_quantity - 1
WHERE product_id = 500
AND available_quantity > 0;
The database still locks internally, but the application avoids a separate read-and-lock phase and interprets the affected-row count. This is often the simplest solution for counters and state transitions.
Banking Applications
A user editing a beneficiary nickname can use optimistic control. A money transfer needs a short database transaction, consistent account order, atomic balance validation, and durable idempotency. Pessimistic row protection may be appropriate inside that transaction, but no database lock should be held while calling an external payment network.
E-Commerce Inventory
Low-contention catalog descriptions fit optimistic versions. Scarce inventory fits atomic conditional updates or short pessimistic reservation. The order record, inventory claim, and idempotency key must agree on one logical purchase so retries do not consume multiple units.
Ticket and Appointment Booking
Seats and time slots are naturally explicit scarce resources. A unique assignment constraint is the final authority. A short lock can coordinate a multi-step hold, while an optimistic version can protect less contentious booking metadata. Expiring holds should be modeled as durable state rather than a transaction left open.
Content Management
Content editing is a classic optimistic use case because users may keep a page open for minutes. Store a version, return it with the edit form, and require it on save. Show meaningful differences when stale. Publishing can use a conditional state transition or short lock to prevent two competing release operations.
Enrollment and Hospital Scheduling
Course capacity and appointment slots often have high-value collisions. Atomic slot claims, unique constraints, or short pessimistic locking fit the allocation. Profile updates and notes can use optimistic versions. A count followed by an unprotected insert is unsafe for either domain.
Job Queue Processing
Multiple workers can select small batches using a locking read with SKIP LOCKED, mark them claimed, and commit immediately. Processing occurs after the claim transaction. Leases, attempt counters, and idempotent handlers recover abandoned work without holding long database locks.
Optimistic Locking with JDBC
String sql = """
UPDATE articles
SET title = ?, content = ?, version_number = version_number + 1
WHERE article_id = ? AND version_number = ?
""";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, title);
statement.setString(2, content);
statement.setInt(3, articleId);
statement.setInt(4, originalVersion);
if (statement.executeUpdate() != 1) {
throw new OptimisticConflictException(articleId);
}
}
Commit in a short transaction and translate the conflict into a domain response. Do not treat zero rows as a successful no-op.
Pessimistic Locking with JDBC
try (Connection connection = dataSource.getConnection()) {
connection.setAutoCommit(false);
try {
lockAndReadInventory(connection, productId);
reserveInventory(connection, productId);
connection.commit();
} catch (SQLException error) {
connection.rollback();
throw error;
}
}
The lock and update must use the same connection and transaction. Configure bounded query or lock timeouts where supported, and return the connection only after rollback or commit.
ORM Versioning
Many ORMs support a version annotation or mapped concurrency token and generate the conditional update automatically. Verify the emitted SQL, especially for detached objects, bulk updates, native queries, and partial updates. Bulk operations may bypass entity-level version handling unless explicitly configured.
Pessimistic ORM lock modes also map differently across databases. Integration tests must run against the production database family rather than only an in-memory substitute.
Optimistic Locking vs MVCC
MVCC is an internal database method for maintaining and selecting row versions. Optimistic locking is an application strategy for detecting that a logical record changed between read and write. A database can use MVCC internally while the application also uses a version column.
A stable MVCC snapshot does not make a stale user edit safe, because the edit may be submitted in a later transaction. The explicit token carries the original version across that boundary.
Optimistic Locking vs Serializable
Serializable protects one database transaction against nonserializable overlap. Optimistic versioning protects a logical edit that may span several requests and cannot remain inside one transaction. They solve different time scales and can be used together.
Serializable may abort a transaction even when no versioned row reports zero affected rows. Conversely, a stale token can fail at Read Committed without requiring Serializable.
Can Optimistic Locking Deadlock?
Yes. It avoids holding a lock during the long edit, but the final save transaction still acquires write locks. If it updates several rows in an order that conflicts with another transaction, a conventional deadlock can occur. Version conflicts and deadlocks are separate outcomes requiring different diagnostics.
Can Pessimistic Locking Prevent Everything?
No. Locking the wrong row does not protect an unmodeled predicate, locks can be too broad or too narrow, deadlocks can form, and external services remain outside the transaction. Constraints, atomic statements, isolation, idempotency, and data modeling still matter.
Choosing a Strategy
- State the invariant and the conflicting concurrent schedule.
- Identify whether one row, several rows, or a predicate is involved.
- Estimate conflict frequency and work discarded after conflict.
- Measure how long the business operation lasts.
- Prefer atomic SQL and constraints where possible.
- Use optimistic control for long, low-conflict editing.
- Use pessimistic control for short, high-contention decisions.
- Define timeout, retry, idempotency, and user conflict behavior.
- Load-test with production-like data and indexes.
Conflict Frequency and Cost
Conflict rate alone is not enough. A rare conflict that discards an hour of work may justify earlier coordination, while frequent conflicts on a cheap counter may be handled by one atomic statement. Measure attempts per success, time spent waiting, rejected edits, retry success, and business impact.
Transaction Duration
Optimistic editing keeps the long human interaction outside a transaction. Pessimistic locking requires an intentionally short transaction. Prepare input before beginning, perform only required SQL, and commit before remote calls, rendering, or large computations.
Deadlocks, Timeouts, and Retries
Pessimistic operations can wait, time out, or deadlock. Optimistic saves can report stale versions and can also experience deadlocks during multi-row writes. Classify outcomes using vendor codes and SQLState, not message text.
Retry a complete transaction only when the operation can be recalculated safely. Use bounded backoff with jitter and preserve one idempotency key. A stale user edit usually requires merge or confirmation rather than invisible automatic overwrite.
Constraints and Final Authority
A unique key, foreign key, check constraint, or exclusion rule protects every write path, including scripts and services that do not follow the application locking convention. Use strategy-level coordination for workflow and constraints for enforceable final state.
Distributed and Offline Editing
A pessimistic database lock cannot sensibly remain open across a mobile device going offline or a request traversing several services. Version tokens, ETags, idempotent commands, durable workflow state, and merge rules are the appropriate tools. One service should remain authoritative for each scarce resource or invariant.
Observability
Track optimistic conflicts, lock wait duration, timeouts, deadlocks, retries, retry exhaustion, transaction age, and latency percentiles by operation. Database diagnostics should identify holders, waiters, modes, resources, and query plans. Application logs should include a safe operation ID and original token without exposing sensitive values.
A high optimistic-conflict rate can signal that the model is too coarse, while high lock waits can signal transactions that are too long or predicates that are poorly indexed.
Optimistic Locking Best Practices
- Use a dedicated database-managed token.
- Include the original token in every protected update and delete.
- Increment the token atomically in the same statement.
- Check affected-row count exactly.
- Define merge, reload, and cancel behavior.
- Audit bulk jobs and native SQL for token bypass.
- Do not silently replay stale full-record replacements.
- Monitor conflict rate and user impact.
Pessimistic Locking Best Practices
- Acquire locks only inside a short explicit transaction.
- Lock the smallest resource that protects the invariant.
- Use selective indexes and predictable resource order.
- Recheck conditions after waiting.
- Set deliberate timeout and NOWAIT behavior.
- Keep user interaction and remote calls outside the transaction.
- Always commit or roll back before returning pooled connections.
- Capture deadlock graphs and lock diagnostics.
Test Cases
- Two users save the same version; exactly one succeeds.
- A missing row is distinguished from a stale version.
- A bulk update either advances or intentionally bypasses tokens.
- A locking read makes a second session wait.
- NOWAIT returns the expected vendor signal.
- Queue workers claim different rows with SKIP LOCKED.
- Opposite resource order reproduces a deadlock.
- Timeout cleanup restores the pooled connection.
- Retries produce one external logical effect.
- High contention is measured for both strategies.
Use independent connections and deterministic barriers rather than arbitrary sleeps. Match production isolation, schema, triggers, indexes, and database family.
Common Mistakes
- Calling optimistic locking a lock-free database update.
- Ignoring zero affected rows.
- Using an unreliable client timestamp as the token.
- Forgetting version advancement in bulk or native SQL.
- Holding pessimistic locks during user think time.
- Assuming row-lock hints are guarantees.
- Using SKIP LOCKED for complete-result queries.
- Retrying stale edits until they overwrite current data.
- Ignoring constraints because locking exists.
- Testing only one session at a time.
Interview Questions and Answers
What is the main difference?
Optimistic locking detects a change at validation or write time, while pessimistic locking coordinates access before completing the protected operation.
Does optimistic locking use database locks?
The final update still uses normal database write locks or equivalent control. It simply avoids reserving the row throughout the earlier business operation.
When is pessimistic locking appropriate?
It fits short, high-contention, high-value decisions where waiting is cheaper than completing work based on stale state.
What does zero affected rows mean?
For a versioned update, it means the ID and original token no longer identify an updatable current row. The record may be stale or deleted.
Can both strategies be combined?
Yes. Systems frequently use optimistic editing and short pessimistic transitions or allocation operations, supported by constraints.
Practical Exercises
- Add a version column to an article table.
- Simulate two stale editors and verify one conflict.
- Build a field-level conflict comparison screen.
- Lock one inventory row from two sessions.
- Compare waiting with NOWAIT behavior.
- Implement a skip-locked job claim.
- Test an ORM bulk update for version handling.
- Measure optimistic rejection and pessimistic wait rates.
Quick Reference
| Need | Likely approach |
|---|---|
| Long user edit | Optimistic version |
| Single atomic decrement | Conditional UPDATE |
| Short high-contention allocation | Pessimistic lock or atomic claim |
| Multi-worker queue | Locking read with SKIP LOCKED |
| Invalid final state | Database constraint |
| Cross-request conditional update | Version or ETag token |
| Predicate-wide invariant | Serializable, guard resource, or redesign |
| Transient database victim | Bounded complete transaction retry |
Decision Workflow
- Write the invariant and stale or conflicting outcome.
- Prefer an atomic statement or constraint if possible.
- Measure operation duration and expected contention.
- Select optimistic validation for long low-conflict work.
- Select pessimistic coordination for short likely conflicts.
- Define timeouts, user messaging, merge, and retry behavior.
- Verify every write path follows the token or lock rule.
- Test with deterministic concurrent sessions.
- Load-test and monitor the selected strategy.
Key Takeaway
Optimistic locking detects stale state when saving, usually through a version token. Pessimistic locking protects current state before a short dependent operation, usually through transaction-level row or range locks. Optimistic control trades blocking for possible rejected work; pessimistic control trades early coordination for waits and deadlock risk.
Choose from the invariant, conflict frequency, operation duration, and recovery cost. Prefer atomic SQL and constraints when they express the rule directly, keep transactions short, validate every affected-row count, and make retries or user conflict resolution explicit. Many production systems correctly combine both strategies at different workflow stages.