Pessimistic Locking in Rails — Preventing Race Conditions in Production

Introduction

In the previous article, we learned why databases need locking and how PostgreSQL uses MVCC (Multi-Version Concurrency Control) to allow multiple transactions to work simultaneously.

However, MVCC alone cannot prevent every concurrency issue.

Imagine an e-commerce application where there is only one product left in stock.

Two customers click “Buy Now” at almost the same time.

Without proper locking, both requests may purchase the same product.

This is where Pessimistic Locking becomes essential.

In this article, we’ll understand how pessimistic locking works in PostgreSQL and how Rails makes it easy to use in production applications.

What is Pessimistic Locking?

Definition

Pessimistic locking is a strategy where a transaction locks a row before modifying it , preventing other transactions from changing that row until the lock is released.

In simple words:

“I’m assuming someone else might modify this row, so I’m locking it first.”

Unlike optimistic locking, pessimistic locking assumes conflicts are likely and prevents them before they happen.

Why Do We Need Pessimistic Locking?

Consider a banking application.

Current balance:

$100

Two requests arrive simultaneously.

Transaction A:

account.balance -= 80
account.save!

Transaction B:

account.balance -= 50
account.save!

Without locking:

Balance = $100

↓

Transaction A reads $100

↓

Transaction B reads $100

↓

A saves $20

↓

B saves $50

↓

Final Balance = $50 ❌

Expected:

$100 - $80 - $50 = -$30

Actual:

$50

This is called a Lost Update.

How PostgreSQL Solves This

PostgreSQL provides

SELECT ... FOR UPDATE;

When a transaction executes:

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

that row becomes locked.

Any other transaction trying to update the same row must wait until the first transaction commits or rolls back.

Rails Pessimistic Locking

Rails exposes PostgreSQL row locking through several APIs.

1. lock

Example:

Account.transaction do
  account = Account.lock.find(params[:id])

  account.balance -= 80

  account.save!
end

Generated SQL:

BEGIN;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

UPDATE accounts
SET balance = 20
WHERE id = 1;

COMMIT;

Notice:

FOR UPDATE

This is the important part.

Timeline

Without locking

Transaction A Transaction B

Read balance=100

                      Read balance=100

Update balance=20

                      Update balance=50

Final = 50 ❌

With locking

Transaction A Transaction B

Lock row

Read balance=100

Update balance=20

Commit

                      Wait...

                      Lock acquired

                      Read balance=20

                      Update balance=-30

Commit
Final = -30 ✅

2. lock!

If you already have the record:

account = Account.find(1)

account.lock!

account.balance -= 50

account.save!

Instead of querying again, Rails locks the existing record.

3. with_lock

This is the cleanest API.

account = Account.find(1)

account.with_lock do
  account.balance -= 50

  account.save!
end

Rails automatically:

• starts a transaction
• locks the row
• executes the block
• commits
• releases the lock

Equivalent SQL:

BEGIN;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

UPDATE accounts...

COMMIT;

Why with_lock is Preferred

Instead of:

Account.transaction do
  account = Account.lock.find(id)
end

You simply write:

account.with_lock do
  ...
end

Cleaner.

Safer.

More readable.

Locking Multiple Rows

Example:

Product.transaction do
  products = Product.lock.where(category_id: 1)
  products.each do |product|
      product.update!(price: product.price * 1.1)
   end
end

All selected rows remain locked until the transaction completes.

NOWAIT

Sometimes you don’t want to wait.

Instead:

Product.lock("FOR UPDATE NOWAIT")

Generated SQL:

FOR UPDATE NOWAIT

If another transaction already owns the lock:

Instead of waiting,

PostgreSQL immediately raises an error.

Useful for:

• payment processing
• reservation systems
• inventory

SKIP LOCKED

Another useful option.

Job.lock("FOR UPDATE SKIP LOCKED")

Instead of waiting,

locked rows are skipped.

Example:

Job 1 Locked

Job 2 Free

Job 3 Locked

Job 4 Free

Worker gets:

Job 2

Job 4

Very common in:

• Sidekiq alternatives
• queue systems
• job schedulers

Real Production Example 1

Inventory Reservation

Product

Inventory = 1

Safe implementation

Product.transaction do
  product = Product.lock.find(id)
raise "Out of stock" if product.inventory.zero?
  product.inventory -= 1
  product.save!
end

No two users can purchase the last item simultaneously.

Real Production Example 2

Hotel Booking

Room:

Available

Without locking:

Two guests reserve the same room.

With locking:

Guest A locks the room.

Guest B waits.

Guest A confirms booking.

Guest B now sees:

Room unavailable

Real Production Example 3

Wallet Transfer

Wallet.transaction do
  wallet = Wallet.lock.find(id)
  wallet.balance -= amount
  wallet.save!
end

Prevents inconsistent balances.

Lock Duration

A common misconception:

People think locks remain forever.

Reality:

BEGIN

↓

Lock row

↓

Update

↓

COMMIT

↓

Lock released

Locks exist only during the transaction.

Things to Avoid

Long Transactions

Bad

User.transaction do
  user.lock!
  sleep 30
  user.update!
end

The row remains locked for 30 seconds.

Other users wait.

External API Calls Inside Transactions

Avoid:

User.transaction do
  user.lock!
  Stripe.charge(...)
  user.update!
end

Network calls increase lock duration.

Instead:

Stripe.charge(...)

User.transaction do
  user.lock!

  user.update!
end

Keep transactions as short as possible.

Advantages

✅ Prevents race conditions

✅ Prevents lost updates

✅ Guarantees data consistency

✅ Great for financial applications

✅ Works well for inventory systems

Disadvantages

❌ Transactions may block each other

❌ Can reduce throughput

❌ Long-running transactions hurt performance

❌ Deadlocks become possible

When Should You Use Pessimistic Locking?

Use it when:

• Payment processing
• Wallet balance
• Inventory reservation
• Ticket booking
• Hotel reservation
• Airline seats
• Banking
• Subscription renewals
• Coupon redemption

Avoid it for:

• Simple profile updates
• Blog posts
• User preferences

Interview Questions

Difference between lock, lock!, and with_lock

lock

Used while querying.

Account.lock.find(1)

lock!

Locks an already loaded record.

account.lock!

with_lock

Starts a transaction, locks the record, executes the block, and commits automatically.

account.with_lock do
  ...
end

What SQL does Rails generate?

SELECT *
FROM accounts
FOR UPDATE;

What happens if another transaction tries to update the same row?

It waits until the first transaction commits or rolls back.

When would you use SKIP LOCKED?

When building:

• Job queues
• Background workers
• Task schedulers

where workers should continue processing available rows instead of waiting.

Key Takeaways

• Pessimistic locking prevents concurrent modifications by locking rows before updates.
• Rails supports it through lock, lock!, and with_lock.
• PostgreSQL implements it using SELECT ... FOR UPDATE.
• Keep transactions short to minimize lock contention.
• Use features like NOWAIT and SKIP LOCKED when appropriate for advanced concurrency scenarios.
• Pessimistic locking is ideal when data correctness is more important than maximizing concurrency.