Optimistic vs Pessimistic Locking

When multiple users try to update the same database record at the same time, we need a way to prevent incorrect updates.

Two important approaches are Optimistic Locking and Pessimistic Locking.

The Problem

Imagine an e-commerce product has:

ProductId = 100
Stock = 10

Two users read the same product at almost the same time.

sequenceDiagram
    participant A as User A
    participant DB as Database
    participant B as User B

    A->>DB: Read Stock = 10
    B->>DB: Read Stock = 10

    A->>DB: Update Stock = 9
    B->>DB: Update Stock = 9

    Note over DB: One update overwrites the other

This is a classic lost update problem.

Optimistic Locking

Optimistic locking assumes that conflicts are relatively uncommon.

Instead of locking the row while reading it, we read a version number and verify that the version has not changed before updating.

sequenceDiagram
    participant A as User A
    participant DB as Database
    participant B as User B

    A->>DB: Read Stock=10, Version=5
    B->>DB: Read Stock=10, Version=5

    A->>DB: Update WHERE Version=5
    DB-->>A: Success, Version=6

    B->>DB: Update WHERE Version=5
    DB-->>B: 0 rows updated

    Note over B: Conflict detected

Complete Optimistic Locking Example

Suppose the table contains:

ProductId | Stock | Version
----------|-------|--------
100       | 10    | 5

User A reads:

Stock = 10
Version = 5

User B also reads:

Stock = 10
Version = 5

User A updates first:

UPDATE Products
SET Stock = 9,
    Version = Version + 1
WHERE ProductId = 100
  AND Version = 5;

The update succeeds and the database becomes:

Stock = 9
Version = 6

User B now tries the same operation:

UPDATE Products
SET Stock = 9,
    Version = Version + 1
WHERE ProductId = 100
  AND Version = 5;

No row is updated because the current version is 6, not 5.

flowchart TD
    A[Read Product] --> B[Version = 5]
    B --> C[Modify Product]
    C --> D{Version still 5?}
    D -->|Yes| E[Update + Version = 6]
    D -->|No| F[Conflict]
    F --> G[Reload / Retry / Inform User]

Optimistic Locking with EF Core

In .NET applications, a version/concurrency token can be used to detect concurrent modifications.

public class Product
{
    public int Id { get; set; }

    public int Stock { get; set; }

    public byte[] RowVersion { get; set; }
}

The concurrency token allows EF Core to detect that another transaction changed the row before the current update.

When a concurrency conflict occurs, the application can catch the exception and decide whether to retry, reload the data, or return a conflict response.

Pessimistic Locking

Pessimistic locking assumes that conflicts are likely.

The transaction locks the required row/resource so that another transaction cannot modify it until the first transaction finishes.

sequenceDiagram
    participant A as User A
    participant DB as Database
    participant B as User B

    A->>DB: Begin Transaction
    A->>DB: Lock Product Row
    DB-->>A: Stock = 10

    B->>DB: Request same row
    DB-->>B: Waiting

    A->>DB: Update Stock = 9
    A->>DB: Commit

    DB-->>B: Lock available
    B->>DB: Read latest Stock = 9

SQL Server Example

For scenarios where explicit locking is appropriate, SQL Server supports locking hints such as UPDLOCK.

BEGIN TRANSACTION;

SELECT Stock
FROM Products WITH (UPDLOCK)
WHERE ProductId = 100;

UPDATE Products
SET Stock = Stock - 1
WHERE ProductId = 100;

COMMIT;

The exact locking behavior depends on the database, isolation level, indexes, query plan, and transaction.

Optimistic vs Pessimistic

Real-World Example — Hotel Room Booking

Suppose only one room remains available.

RoomId = 501
Available = true

Optimistic Approach

Both users can read the room. The first successful update changes the version.

UPDATE Rooms
SET Available = 0,
    Version = Version + 1
WHERE RoomId = 501
  AND Available = 1
  AND Version = 10;

If the affected rows are 1, booking succeeds. If the affected rows are 0, another user already booked or changed the room.

Pessimistic Approach

The first transaction locks the room while checking and reserving it.

BEGIN TRANSACTION;

SELECT Available
FROM Rooms WITH (UPDLOCK)
WHERE RoomId = 501;

-- Reserve room

UPDATE Rooms
SET Available = 0
WHERE RoomId = 501;

COMMIT;

Which One Should You Use?

There is no universal answer. Choose based on the workload and business requirement.

flowchart TD
    A[Concurrent Update Required] --> B{How Frequent Are Conflicts?}

    B -->|Low| C[Optimistic Locking]
    B -->|High| D[Pessimistic Locking]

    C --> E[Version / Timestamp]
    C --> F[Handle Conflict / Retry]

    D --> G[Acquire Lock]
    D --> H[Keep Transaction Short]
    D --> I[Control Blocking]

Important System Design Point

Locking is not a replacement for good database design.

For high-throughput systems, consider:

• Atomic conditional updates
• Proper indexes
• Short transactions
• Appropriate isolation levels
• Idempotency
• Bounded retries with backoff
• Queue-based processing where appropriate

Interview Scenario

Interviewer: Two users edit the same customer profile. User A saves first and User B saves later. How do you prevent User B from silently overwriting User A's changes?

Answer: Use optimistic concurrency. Store a version or row-version value with the record. When updating, include the version in the WHERE condition. If zero rows are updated, another user has modified the record. Return a conflict, reload the latest version, or merge the changes according to the business requirement.

Common Interview Questions

• What is optimistic locking?
• What is pessimistic locking?
• Optimistic vs pessimistic locking?
• What is a lost update?
• How does a version column work?
• How do you implement optimistic concurrency in EF Core?
• When would you choose pessimistic locking?
• How can locking affect database performance?
• How would you prevent two users from booking the same room?

Key Takeaways

• Optimistic locking → assume conflicts are uncommon and detect them during update.
• Pessimistic locking → assume conflicts are likely and lock resources early.
• Optimistic locking commonly uses a version/timestamp/concurrency token.
• Pessimistic locking can increase blocking and contention.
• For high-concurrency operations, atomic conditional updates can often be simpler and faster than holding long-lived locks.
• Always keep transactions short and make retry behavior safe and bounded.

Conclusion

The core difference is simple:

Optimistic locking detects conflicts.
Pessimistic locking prevents conflicts by locking resources.

In a system design interview, explain the choice using conflict frequency, concurrency, transaction duration, business consistency, and performance.