Database Concurrency Control: Pessimistic Lock vs. Optimistic Lock in Practice

What happens when two concurrent requests withdraw money from the same account at the exact same millisecond, or two shoppers click "Buy" on the very last item in stock? Without explicit concurrency control, both transactions read the same initial balance or stock count, compute their updates independently, and overwrite each other—leaving your database with corrupted balances or oversold inventory. This article compares the two primary patterns for preventing lost updates in relational databases—Pessimistic Locking (SELECT ... FOR UPDATE) and Optimistic Locking (version column)—along with when a single atomic `UPDATE` statement is all you actually need.

1. Intuitive Analogy

• Pessimistic Locking is like booking a physical meeting room: Because you assume someone else might try to barge in, you lock the door as soon as you enter (`SELECT ... FOR UPDATE`), and anyone else who wants the room waits outside until you finish and unlock the door (`COMMIT`).
• Optimistic Locking is like editing a shared wiki page with version checks: Anyone can open and edit the page without locking it upfront, but when you click Save, the server checks whether the document is still at `version = 5` (the version you originally read). If someone else already bumped it to `version = 6`, your save is rejected and you must refresh and retry.

2. The Classic Bug: Lost Update (Read-Modify-Write Race)

1. An account starts with a balance of $100. Request A (withdraw `$30`) and Request B (withdraw `$50`) arrive simultaneously.
2. Both Request A and Request B execute `SELECT balance FROM account WHERE id = 1` and read $100 into application memory.
3. Request A calculates `100 - 30 = 70` and runs `UPDATE account SET balance = 70 WHERE id = 1`.
4. A millisecond later, Request B calculates `100 - 50 = 50` and runs `UPDATE account SET balance = 50 WHERE id = 1`.
5. Although `$80` was withdrawn in total (so the final balance should be `$20`), Request A's deduction was completely overwritten, leaving `$50` in the account!

Whenever application code splits state changes into a Read → Compute in Memory → Write sequence under standard `READ COMMITTED` isolation, concurrent transactions interleave and cause a Lost Update.

3. First Check: Can You Solve It with a Single Atomic `UPDATE`?

Before reaching for application-level pessimistic or optimistic locks, check whether the invariant can be expressed inside a single SQL `UPDATE` statement:

UPDATE account
   SET balance = balance - 3000
 WHERE id = 1 AND balance >= 3000;

In PostgreSQL and MySQL InnoDB, an `UPDATE` statement automatically acquires an exclusive row-level lock on matching rows and evaluates `balance - 3000` against the latest committed row version. If the affected row count is `0`, the balance was insufficient. You only need explicit `SELECT ... FOR UPDATE` or optimistic `version` locking when your business logic must inspect multiple rows or perform complex domain branching in application code between reading and writing.

4. Pessimistic Locking: `SELECT ... FOR UPDATE`

Reading a row with SELECT ... FOR UPDATE inside an explicit database transaction places an exclusive row lock on the selected records until the transaction commits or rolls back. Any other transaction attempting to `UPDATE`, `DELETE`, or `SELECT ... FOR UPDATE` that same row blocks and waits in queue.

Clarifying a common myth: In both PostgreSQL and MySQL InnoDB (MVCC engines), SELECT ... FOR UPDATE does NOT block plain, non-locking `SELECT` queries! Regular `SELECT` statements continue reading the consistent MVCC snapshot without waiting at all; only concurrent writers and locking readers are blocked.

// Go + GORM
err := db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
	var acc Account
	if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
		First(&acc, id).Error; err != nil {
		return err
	}
	if acc.Balance < amount {
		return ErrInsufficientBalance
	}
	return tx.Model(&acc).Update("balance", acc.Balance-amount).Error
})

// Kotlin + Spring Data JPA
interface AccountRepository : JpaRepository<Account, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("select a from Account a where a.id = :id")
    fun findForUpdate(@Param("id") id: Long): Account?
}

Preventing Infinite Lock Wait Queues

The biggest operational hazard with pessimistic locks is thread-pool and connection-pool exhaustion when many requests pile up waiting on a single hot row. Always bound lock wait durations:

• FOR UPDATE NOWAIT: Fails immediately with an error if the row is already locked by another transaction (supported in PostgreSQL and MySQL 8.0+).
• FOR UPDATE SKIP LOCKED: Skips locked rows and returns only unlocked rows—ideal when multiple worker processes pull unclaimed tasks from a database queue table, though unsuitable when querying a single specific account ID.
• Explicit Lock Timeouts: Configure PostgreSQL `lock_timeout` (e.g., `SET LOCAL lock_timeout = '3s'`) or MySQL `innodb_lock_wait_timeout` so blocked queries fail fast instead of hanging for 50 seconds.

5. Optimistic Locking: The `version` Column Pattern (CAS)

Optimistic locking avoids database lock wait queues by adding an integer `version` column to the table. When updating the row, the `WHERE` clause asserts that `version` still matches the value read at the start of the use case, and increments `version + 1` atomically (Compare-And-Swap):

// NestJS + Prisma: updateMany returns the number of matched rows (count)
const acc = await this.prisma.account.findUniqueOrThrow({ where: { id } });
const { count } = await this.prisma.account.updateMany({
  where: { id, version: acc.version },
  data: { balance: acc.balance - amount, version: { increment: 1 } },
});
if (count === 0) {
  throw new ConflictException('Concurrent modification detected; please retry');
}

// Kotlin + Spring Data JPA
@Entity
class Account(
    @Id val id: Long = 0,
    var balance: Long = 0,
    @Version var version: Long = 0,
)

In Spring Data JPA / Hibernate, annotating a field with `@Version` automatically appends `WHERE id = ? AND version = ?` to `UPDATE` statements and throws `ObjectOptimisticLockingFailureException` when zero rows match.

Crucial rule when retrying optimistic lock failures: If you implement automatic retries (typically 2–3 attempts with short jittered backoff), the retry loop must sit outside the `@Transactional` boundary and must re-read the fresh row from the database before re-running the calculation.

6. Side-by-Side Comparison

Dimension	Pessimistic Lock (FOR UPDATE)	Optimistic Lock (version Column)
When Conflict Is Detected	At read time (acquires exclusive row lock upfront)	At write/commit time (`WHERE version = ?`)
Behavior on Contention	Competing transactions wait in queue until commit	Competing write fails immediately (`count === 0`)
Best Suited For	High contention (flash sale inventory, wallet balances)	Low contention (profile edits, CMS document saves)
Primary Risk	Connection pool exhaustion & deadlocks	Retry storms when contention is high

7. Five Common Mistakes in Production

• Executing `SELECT ... FOR UPDATE` outside a transaction: In auto-commit mode, the statement commits immediately after returning the row, releasing the lock before your `UPDATE` ever runs!
• Calling slow external HTTP APIs while holding a `FOR UPDATE` row lock: Waiting 2 seconds for an external payment gateway inside the transaction freezes that row (and holds a scarce DB connection) for 2 full seconds. Keep transactions strictly confined to fast local DB operations.
• Locking multiple rows in inconsistent order (Deadlocks): If Transaction A locks Account 1 then Account 2, while Transaction B locks Account 2 then Account 1, both deadlock. Always sort row IDs in ascending order (`ORDER BY id ASC`) before locking multiple rows.
• Using `FOR UPDATE` on read-only dashboard queries: Never attach `FOR UPDATE` unless the current transaction actually intends to mutate the locked rows.

If you need to coordinate mutual exclusion across servers for operations that don't map to an existing database row—such as scheduled cron jobs or external API calls—see our guides on Database-Backed Distributed Locks (`pg_advisory_lock` / `GET_LOCK`) and Redis Distributed Locks.

8. Summary

References

• PostgreSQL Documentation - Explicit Locking
• MySQL 8.4 Reference Manual - InnoDB Locking Reads
• Spring Data JPA Reference - Locking
• GORM Documentation - Locking