Rails Pessimistic Locking: SELECT FOR UPDATE, with_lock, and Preventing Race Conditions

Two checkout requests hit the same wallet at the same millisecond. Each reads the balance, sees €100, subtracts €80, and writes back €20. The customer paid twice and the wallet shows one charge. The finance team finds it three days later when the reconciliation report does not balance. Engineering opens the database and stares at the row for a long time before someone says the words “I think we have a race condition.”

After nineteen years of Rails I can tell you that this exact bug — read, compute, write, with two requests interleaving — is the single most common production data corruption I have seen. It is not subtle. It is not rare. It happens any time you have concurrent writers touching the same row and you reach for the obvious code instead of Rails pessimistic locking. This post is about when to use lock!, with_lock, and SELECT FOR UPDATE, how each one actually behaves under Postgres, and the failure modes that make people abandon them and reinvent something worse.

What Rails Pessimistic Locking Actually Does

Rails pessimistic locking tells the database to acquire a row-level lock the moment you read a record, and to hold that lock until your transaction commits or rolls back. Any other transaction that tries to read the same row with a lock, or to update it, blocks and waits. The optimistic alternative, lock_version, lets everybody read freely and only detects conflicts at write time by raising ActiveRecord::StaleObjectError. Pessimistic locking trades a little throughput for a hard guarantee: while you hold the lock, nobody else can touch this row.

Under the hood, when you call wallet.lock! or Wallet.lock.find(id) inside a transaction, ActiveRecord issues SELECT ... FOR UPDATE to Postgres. That tells Postgres to mark the row as locked for the duration of the transaction. Other transactions that issue SELECT FOR UPDATE, UPDATE, or DELETE on the same row will block until your transaction ends. Plain SELECT without a lock still works — it reads the pre-locked snapshot via MVCC and returns immediately. That last detail is important and surprises people: pessimistic locking does not block all readers, only writers and other lockers.

The mental model that matters: a row lock is a queue with one ticket window. Whoever gets the lock first does their work and commits. Everybody else waits in line. As long as you do the smallest possible amount of work while holding the lock, the queue moves fast and your app stays responsive.

The Race Condition That Started This Post

Here is the broken code, written the way most Rails developers write it on the first try.

class WalletsController < ApplicationController
  def charge
    wallet = Wallet.find(params[:id])
    amount = params[:amount].to_i

    if wallet.balance >= amount
      wallet.update!(balance: wallet.balance - amount)
      head :ok
    else
      head :payment_required
    end
  end
end

This works perfectly in a unit test. It works in staging. It works the first ten thousand times in production. Then two requests arrive within a millisecond of each other and Postgres serves them both. Both transactions read balance = 100. Both check 100 >= 80. Both write balance = 20. The wallet has been charged twice and the second charge was free. There is no error in the logs. There is no failed query. The database did exactly what each transaction asked, in isolation. The problem is that they were not in isolation — they were interleaved.

Fixing It With Rails Pessimistic Locking

The fix is three lines of code. Wrap the read in a transaction, use lock! to acquire a row lock, then do the check and the write while holding the lock.

class WalletsController < ApplicationController
  def charge
    amount = params[:amount].to_i

    Wallet.transaction do
      wallet = Wallet.lock.find(params[:id])

      if wallet.balance >= amount
        wallet.update!(balance: wallet.balance - amount)
        head :ok
      else
        head :payment_required
      end
    end
  end
end

Wallet.lock.find(params[:id]) issues SELECT * FROM wallets WHERE id = ? FOR UPDATE. The first request to arrive gets the lock immediately. The second request blocks inside find until the first transaction commits. When the second one finally gets the lock, it reads the post-commit value (balance = 20), checks 20 >= 80, sees false, and returns payment_required. No double charge. No silent corruption. The behavior matches the intent.

The same code can be written with with_lock, which is a small wrapper that opens a transaction, reloads the record with a lock, and yields:

wallet = Wallet.find(params[:id])

wallet.with_lock do
  if wallet.balance >= amount
    wallet.update!(balance: wallet.balance - amount)
    head :ok
  else
    head :payment_required
  end
end

with_lock is what I reach for ninety percent of the time. It makes the lock scope visible in the code and you cannot accidentally forget the transaction. The first find outside the block is the “look up which row” lookup; the lock-and-reload inside with_lock is what actually matters.

SELECT FOR UPDATE Variants You Should Know About

Postgres offers four flavors of row lock and Rails exposes all of them through the lock scope. Knowing which one to use is the difference between code that scales and code that deadlocks on Black Friday.

Wallet.lock.find(id)                   # SELECT ... FOR UPDATE
Wallet.lock("FOR UPDATE NOWAIT").find(id)
Wallet.lock("FOR UPDATE SKIP LOCKED").find(id)
Wallet.lock("FOR SHARE").find(id)

FOR UPDATE is the default and the one you want for “I am about to modify this row.” It blocks other writers until you commit.

FOR UPDATE NOWAIT is identical except that if the lock is held by somebody else it raises immediately instead of waiting. Use this when you would rather fail fast and let the user retry than have the request sit in a queue. It is the right choice for low-latency APIs where a long wait is worse than a clean error. Catch ActiveRecord::LockWaitTimeout (or PG::LockNotAvailable on older Rails) and return 409 Conflict.

FOR UPDATE SKIP LOCKED is the magic one for job queues. It skips rows somebody else has locked and returns the next available row. This is how Solid Queue, GoodJob, and Que all implement “give me the next unclaimed job” without serializing the entire worker fleet. If you ever build your own queue table, this is the line of SQL that makes it scale.

Job.lock("FOR UPDATE SKIP LOCKED").where(state: "ready").limit(1).first

FOR SHARE is a read lock. Other FOR SHARE readers can also acquire it, but writers block. Use it when you want to guarantee that nobody changes the row while you are reading it but you do not yourself intend to write. In practice I almost never reach for this — if I am locking, it is because I intend to modify.

The Two Failure Modes Everyone Hits

Rails pessimistic locking is mechanical and well-defined, but two failure modes catch every team eventually. Knowing them in advance is the difference between fixing the bug in five minutes and fixing it after a four-hour outage.

Deadlocks

A deadlock happens when transaction A holds a lock on row X and is waiting for row Y, while transaction B holds Y and is waiting for X. Neither will ever finish. Postgres detects this after a short timeout and kills one of the transactions with deadlock detected. Rails surfaces it as ActiveRecord::Deadlocked.

The two-row transfer is the canonical case:

def transfer(from_id, to_id, amount)
  Wallet.transaction do
    from = Wallet.lock.find(from_id)
    to   = Wallet.lock.find(to_id)
    from.update!(balance: from.balance - amount)
    to.update!(balance: to.balance + amount)
  end
end

Two parallel calls of transfer(1, 2, 10) and transfer(2, 1, 10) will deadlock immediately. The fix is to lock the rows in a deterministic order — always the lower id first.

def transfer(from_id, to_id, amount)
  ordered = [from_id, to_id].sort
  Wallet.transaction do
    wallets = Wallet.lock.where(id: ordered).index_by(&:id)
    from = wallets.fetch(from_id)
    to   = wallets.fetch(to_id)
    from.update!(balance: from.balance - amount)
    to.update!(balance: to.balance + amount)
  end
end

Sorting by id is the cheapest, most reliable deadlock prevention I know. Any time your transaction touches more than one row of the same table, sort.

Long-held locks under slow callbacks

The other failure mode is more insidious. A junior dev adds a callback to Wallet that calls an external API.

class Wallet < ApplicationRecord
  after_update :notify_fraud_service

  private

  def notify_fraud_service
    FraudClient.post(id: id, balance: balance)
  end
end

The fraud service is slow. The HTTP call takes two seconds. The lock is held the entire time. Now charge requests serialize at two seconds per wallet instead of two milliseconds. Throughput drops by three orders of magnitude. The wallet table becomes the bottleneck for the whole site.

The rule is simple and absolute: do not perform any I/O while holding a row lock. No HTTP. No external API. No email. No third-party SDK. The transaction should contain only Postgres queries and pure Ruby. Push the side effects to a background job using after_commit, and let the job re-fetch the record without a lock.

class Wallet < ApplicationRecord
  after_commit :enqueue_fraud_notification, on: :update

  private

  def enqueue_fraud_notification
    NotifyFraudJob.perform_later(id)
  end
end

after_commit runs after the transaction has been released, so the side effect is fired without holding the row lock. If the fraud service is down, the job retries and the wallet table keeps moving.

Setting Lock Timeouts So You Fail Fast

Postgres will wait forever for a row lock by default. That is almost never what you want in a web request — a request that hangs for two minutes is worse than a request that fails at one second and lets the user retry. Set a lock_timeout on the connection.

# config/initializers/lock_timeout.rb
ActiveSupport.on_load(:active_record) do
  ActiveRecord::Base.connection.execute("SET lock_timeout = '2s'")
end

For Rails 7.1+ multi-database setups, set it per role in database.yml:

production:
  primary:
    <<: *default
    variables:
      lock_timeout: 2000           # 2 seconds, in milliseconds
      statement_timeout: 15000
      idle_in_transaction_session_timeout: 30000

idle_in_transaction_session_timeout is the unsung hero. It kills transactions that have been sitting idle (not running a query) for too long. If a Ruby exception in the middle of your transaction prevents the rollback from firing — say, because the app server crashed — Postgres will eventually clean it up instead of leaving locks held forever.

I touched on related Postgres tuning in a previous post on PgBouncer and on slow query analysis. Lock timeouts belong in the same pile of “things you should configure before going to production.”

When To Reach For Optimistic Locking Instead

Pessimistic locking is not free. It serializes access to a row, and for high-contention hot rows that serialization shows up in your latency p99. If your data shape is “most updates do not conflict, but occasional conflicts must be detected,” optimistic locking with lock_version is often the better fit.

class AddLockVersionToOrders < ActiveRecord::Migration[8.0]
  def change
    add_column :orders, :lock_version, :integer, default: 0, null: false
  end
end

order = Order.find(id)
order.status = "shipped"
order.save!  # raises ActiveRecord::StaleObjectError if another writer beat us

The rule I use in practice: pessimistic locking for financial state and inventory counts; optimistic locking for editable user content. The wallet balance must never go negative — lock. The user’s profile page might lose an edit in a tab they left open for a week — that is acceptable and StaleObjectError lets you tell them.

For background-job claim semantics, neither of these — use FOR UPDATE SKIP LOCKED and a state column. Combined with Solid Queue’s recurring jobs that pattern scales to thousands of workers without contention.

Testing Pessimistic Locking

The trick to testing a lock is that you need two concurrent transactions. RSpec with threads and two database connections does the job, though the test reads strangely the first time.

require "rails_helper"

RSpec.describe "Wallet charging", :truncation do
  it "prevents double-charging under concurrency" do
    wallet = Wallet.create!(balance: 100)

    barrier = Concurrent::CyclicBarrier.new(2)
    results = Concurrent::Array.new

    threads = 2.times.map do
      Thread.new do
        ActiveRecord::Base.connection_pool.with_connection do
          barrier.wait
          begin
            Wallet.transaction do
              w = Wallet.lock.find(wallet.id)
              if w.balance >= 80
                w.update!(balance: w.balance - 80)
                results << :charged
              else
                results << :rejected
              end
            end
          end
        end
      end
    end

    threads.each(&:join)

    expect(results.sort).to eq([:charged, :rejected])
    expect(wallet.reload.balance).to eq(20)
  end
end

Use :truncation strategy, not transactional fixtures — transactional fixtures run the whole test in a single transaction, which serializes everything and makes the lock invisible. The Concurrent::CyclicBarrier makes both threads start the transaction at the same moment, which forces the race. Without it the test passes by accident.

FAQ

What is the difference between lock! and with_lock in Rails?

lock! acquires a row-level lock on an already-loaded record and reloads it. It must be called inside a transaction or it has no effect when the method returns. with_lock opens a transaction, calls lock!, and yields a block — so it is safer and self-contained. Use with_lock unless you already have an open transaction.

Does Rails pessimistic locking work with SQLite?

Partially. SQLite serializes all writes at the database level, so lock! issues are silently no-ops on most adapters — the lock comes from SQLite’s own write serialization. For Rails 8 apps running SQLite in production, race conditions on the same row are much harder to hit because of that serialization, but the pattern is still worth writing for portability and clarity. See my SQLite production post for the trade-offs.

When should I use FOR UPDATE SKIP LOCKED instead of FOR UPDATE?

Use SKIP LOCKED when you have multiple workers competing for “the next available item” — job queues, outbox processors, email sending. It lets each worker grab a different row instead of all of them serializing on the first one. Use plain FOR UPDATE when you need to lock a specific row by id and the wait is intentional.

How do I avoid deadlocks with Rails pessimistic locking?

Always acquire locks in a deterministic order. If a transaction touches multiple rows of the same table, sort the ids and lock in sorted order. If it touches multiple tables, lock them in a documented, app-wide order — for example, always users before orders. Catch ActiveRecord::Deadlocked at the controller boundary and retry once with jitter.

Need help untangling race conditions, deadlocks, or production data corruption in a Rails app? TTB Software has been doing this for nineteen years — pessimistic locking, optimistic locking, and the queue patterns in between. We have written the post-mortems so you do not have to.