7 Rails Database Locking Patterns That Stop Race Conditions Before They Strike

Imagine two people editing the same blog post. Both open their browsers, both see the same text, both make changes. The first one clicks Save. A moment later, the second one clicks Save. In too many Rails apps, the second write simply overwrites the first. The first person loses minutes of work. Nobody sees an error. That is a race condition.

I have fixed enough of those bugs to know one thing: race conditions almost always show up at the database boundary. Two requests read the same record. Both decide what to write. Both write. The last writer wins. Or two background workers read the same unfinished job. Both think they claimed it. Both process it. The customer gets two emails, two charges, or two refunds.

The solution is not to add more Ruby code. The solution is to make the database the referee. Rails gives us several locking patterns that push coordination into the database itself. Each pattern solves a different class of race. I will walk through all seven in plain language, with code that you can copy and adapt.

Let’s start with the easiest pattern.

1. Optimistic locking with a version column

Optimistic locking does not hold any database lock. Instead, it uses a version number to make sure that the row you are updating is the same row you originally read. You add an integer column named lock_version to the table. Rails recognizes that column name automatically.

Here is the migration.

class AddLockVersionToArticles < ActiveRecord::Migration[7.0]
  def change
    add_column :articles, :lock_version, :integer, default: 0, null: false
  end
end

Now imagine that two requests load the same article. Both requests read lock_version = 2. The first request saves its changes. Rails updates that row and increases lock_version to 3. The second request tries to save using its stale lock_version = 2. Rails sends an UPDATE statement that says: change this article only if the current lock_version is still 2. But the current version is now 3. The database changes no rows. Rails raises ActiveRecord::StaleObjectError.

A controller might look like this.

class ArticlesController < ApplicationController
  def update
    article = Article.find(params[:id])
    article.assign_attributes(article_params)
    article.save!

    render json: article
  rescue ActiveRecord::StaleObjectError
    render json: {
      error: "Someone else saved this article first. Refresh and try again."
    }, status: :conflict
  end

  private

  def article_params
    params.require(:article).permit(:title, :body, :lock_version)
  end
end

Your form needs a hidden field. When the user opens an article, include the current lock_version in the form. When that article is saved, send it back. If another user saved the article in the meantime, the conflict is caught.

Optimistic locking works best when write conflicts are rare. If two users rarely edit the same record at the same time, this pattern keeps the database clean and the application code simple. The downside is that the second user must deal with an error. There is no waiting. There is no retry inside the database. The user is told to go back, reload, and copy their changes into the fresh version.

I think of optimistic locking as a hotel room key. The first person to come back to the front desk can change things. The second person who tries to use the old key gets a polite bell sound: that key no longer works. Go get a new key.

2. Pessimistic row locking with SELECT ... FOR UPDATE

Some operations need more than a version check. You might need to read a row, make a decision based on that read, and then write. If two requests run that sequence at the same time, a version error is not enough. You need to stop the second request before it reads stale data.

Rails gives you the lock method. When you call lock inside a transaction, Rails appends FOR UPDATE to the SQL query. The database locks that row. Other transactions that try to lock the same row wait until your transaction finishes.

Here is an example of moving money out of an account.

amount_cents = params[:amount_cents].to_i

Account.transaction do
  account = Account.lock.find(params[:id])
  new_balance = account.balance_cents - amount_cents

  raise "Insufficient funds" if new_balance.negative?

  account.update!(balance_cents: new_balance)
end

Let’s follow two requests. Request A and Request B both want to withdraw from the same account. Request A locks the row. Request B runs the same query and waits. Request A reads the current balance, checks that the account has enough money, updates the balance, and commits the transaction. Now the row is unlocked. Request B proceeds and reads the new balance. It makes its decision based on the updated data. That is exactly what we want.

Pessimistic locks are powerful, but they come with responsibilities. The lock is held until the transaction commits or rolls back. If you keep that transaction open for a long time, other requests pile up. If you call an external HTTP service while holding the lock, the database connection may sit there waiting for a slow website to respond. In a small Rails app, that can drain the connection pool quickly.

Keep locked transactions short. Do not send emails inside the lock. Do not call external APIs inside the lock. Lock the row, update it, commit, and move on.

3. Locking a parent record to protect its children

The most common race condition I see in e-commerce apps is an order total. Two users add items to the same order at almost the same time. Both requests read the order. Both requests read the current total. Each request adds its own item price to the old total. Each request writes its own new total. One of those writes disappears. The order total ends up too low.

The cleanest fix is to lock the parent order row before changing the children. The order row is a single point that every child update must pass through. When one request locks the order, the other request has to wait. After the first request commits, the second request sees the new order total.

Order.transaction do
  order = Order.lock.find_by!(public_id: params[:order_public_id])
  product = Product.find(params[:product_id])
  quantity = params[:quantity].to_i

  item = order.line_items.create!(
    product: product,
    quantity: quantity,
    unit_price_cents: product.price_cents
  )

  order.update!(
    total_cents: order.total_cents + item.unit_price_cents * item.quantity
  )
end

Follow what happens with two overlapping requests. Request A locks the order. Request B tries to lock the same order and waits. Request A creates a line item, adjusts the total, commits. Request B enters and reads the updated order total. It creates its own line item. It adds its own price to the current total. Both changes survive.

This pattern is not just for orders. Use it whenever you have a parent record and a group of children that must stay consistent. An account with transactions, an order with line items, a user with balance movements, a product with stock counts. Lock the one parent row that every child operation touches. The parent row becomes a mutex.

One warning: this only works if every code path that changes the parent uses the same lock. If one background job uses Order.lock and another background job uses a raw SQL update that ignores the lock, you still have a race. Use one consistent method for all changes to that aggregate.

4. Advisory locks for non-row resources

Sometimes the resource you need to protect is not a row in a table. Maybe you need to make sure only one process can run a refund for the same request, or only one process can handle a billing period, or only one process can export data for a particular customer. The data involved may be spread across many tables. There is no single parent row to lock.

PostgreSQL gives you advisory locks. These locks are not attached to a table row. They are named by a key. You decide what the key means. As long as all processes use the same key, only one can hold the lock at a time.

Here is a refund example.

ActiveRecord::Base.transaction do
  connection = ActiveRecord::Base.connection
  connection.execute(
    "SELECT pg_advisory_xact_lock(hashtext('refund:#{refund_request.id}'))"
  )

  # Only one process can run this block for the same refund request.
  RefundService.new(refund_request).run
end

The pg_advisory_xact_lock lock is released automatically when the transaction ends. That is a nice property. If your code raises an exception, the transaction rolls back and the lock is released. You do not need to call an unlock method by hand.

If you do not need to wait for the lock, you can use pg_try_advisory_xact_lock. It returns true if you got the lock and false if someone else has it. That is useful when you want to run a non-critical task only if no other process is already running it.

If raw SQL frightens you, the with_advisory_lock gem gives you the same thing in a friendlier wrapper.

RefundRequest.with_advisory_lock("refund:#{refund_request.id}") do
  RefundService.new(refund_request).run
end

Advisory locks are not restricted to one table. That makes them perfect for logical resources. The key string should be descriptive and include an ID. That way, locks for refunds, exports, and billing periods do not block each other.

5. SKIP LOCKED for competing workers

Many small Rails apps use a database table as a background job queue. A table called pending_jobs holds rows that need processing. Several Sidekiq workers or plain Ruby processes constantly ask for the next job. The danger is simple: two workers could pull the same row at the same time.

A normal FOR UPDATE lock is not enough. It only makes the second worker wait. The second worker reads the same job after the first worker commits, sees the job is already running, and may try to process it anyway.

PostgreSQL has a better clause for this problem: FOR UPDATE SKIP LOCKED. When a query uses SKIP LOCKED, it skips rows that are locked by other transactions and returns the next unlocked row. Each worker gets a different job.

class ClaimNextPendingJob
  def self.call
    PendingJob.transaction do
      job = PendingJob
              .lock("FOR UPDATE SKIP LOCKED")
              .where(state: :pending)
              .order(:created_at)
              .first

      job&.update!(state: :running, claimed_at: Time.current)
    end
  end
end

Your worker process calls ClaimNextPendingJob.call. If a job is available, the worker receives it and changes its state to running. The transaction commits and releases the lock on that row. No other worker can claim the same job because the state is no longer pending and the row was locked during the claim.

This pattern is simple. It turns a plain table into a safe work queue without Redis and without a separate job scheduler. It does have limitations. The table can grow large. A long-running job keeps a database transaction open only while it claims the row, not while it does the actual work, as long as you commit immediately after claiming. But if you process the job inside the same transaction, you hold a database connection for the whole job. Do not do that.

Claim the job, commit, then process the job outside the transaction.

job = ClaimNextPendingJob.call

if job
  begin
    ProcessTheJob.new(job).run
    job.update!(state: :done)
  rescue StandardError => e
    job.update!(state: :failed, error_message: e.message)
  end
end

SKIP LOCKED works well in PostgreSQL. MySQL also has a similar clause, but the exact behavior can differ. If you use PostgreSQL, this is one of the most pleasant pattern fixes I know. It saved me from a whole class of duplicate job problems.

6. Unique constraints as insert guards

A race condition is not always about updating an existing row. It can also happen when two requests try to insert the same record. The classic example is user signup. Two people request the same email at the same time. Each request checks whether the email exists. Each sees no existing user. Each tries to insert the same email. Both succeed if the only guard is a Rails validation. Now you have two user accounts with the same email.

Model validations cannot stop this. A validation and an insert are separate operations. Between the validation and the insert, another request can do the same thing.

The only authority that can stop duplicate rows is a database unique index.

class AddUniqueIndexToUsersEmail < ActiveRecord::Migration[7.0]
  def change
    add_index :users, :email, unique: true
  end
end

With that unique index in place, one of the two insert statements will succeed. The other will raise ActiveRecord::RecordNotUnique. Your application can rescue that error and read the existing record.

begin
  User.create!(email: email, name: name)
rescue ActiveRecord::RecordNotUnique
  user = User.find_by!(email: email)
end

The database decides the winner. The loser gets an exception. This is not a sad situation. The loser can use the existing record. The signup request can log the user in. The coupon redemption can show that the coupon was already used. The reservation code can show the existing reservation.

When using this trick, be careful if the table has more than one unique index. The exception might come from a different unique constraint. If you only care about email, make sure the unique index on email is the one that caused the error. You can check the error message or the constraint name in the exception.

Rails also offers upsert, which is an atomic insert-or-update operation. With PostgreSQL and MySQL 8.0+, you can avoid the rescue entirely.

User.upsert(
  { email: email, name: name },
  unique_by: :email
)

user = User.find_by!(email: email)

The database inserts the row if the email is new. If the email already exists, the database updates the row. Then you reload the row from the database. No exception is raised.

Unique constraints are your last line of defense. Add them for every column or combination of columns that should appear only once. A unique constraint cannot fix every race, but it can fix the ones where two requests both insert a row that should be globally unique.

7. Distributed locks with Redis

Sometimes your Rails application runs on more than one server. Multiple processes can be running in different data centers. They still need to coordinate access to one shared resource. A database row lock can work, but sometimes the resource is not in your database. Sometimes the resource is a third-party API action, a cache rebuild, or a rate-limited endpoint.

A distributed lock uses Redis as a central place where all processes agree. The idea is simple. A process tries to create a key in Redis. If the key already exists, someone else holds the lock. If the key does not exist, the process creates it and proceeds. When the work is done, the process removes the key.

The redlock gem is a common way to do this in Rails.

# Gemfile
gem "redlock"

Here is a small wrapper class.

require "redlock"

class RedisLock
  def initialize
    @redis = Redis.new(url: ENV.fetch("REDIS_URL", "redis://localhost:6379/0"))
    @manager = Redlock::Client.new([@redis])
  end

  def run(key, ttl_ms: 5000)
    lock_info = @manager.lock(key, ttl_ms)
    raise "Could not acquire lock #{key}" unless lock_info

    begin
      yield
    ensure
      @manager.unlock(lock_info)
    end
  end
end

You can then use it anywhere.

lock = RedisLock.new

lock.run("customer:#{customer.id}") do
  customer.refresh_credit_score!
  customer.send_summary_email
end

All processes that use the same key will share the same mutex. If one process dies before the ensure block runs, the Redis key has a time-to-live. The lock automatically expires after that number of milliseconds. Choose a TTL that is longer than your slowest operation. If your work normally takes one second, a TTL of five seconds gives you a comfortable margin. If the TTL is too short, the lock can expire while the job is still running, and a second process can enter the block. That can create a race all over again.

Distributed locks are not perfect. If Redis has a network problem or two processes have clock differences, distributed locks can misbehave. For money movements or order totals that live inside one PostgreSQL database, I prefer row locks and advisory locks. Redis locks are best for application-level mutexes where an occasional failure during an outage is acceptable.

I once used a Redis lock to prevent duplicate welcome emails. If the same customer signed up from two browser tabs, both requests reached the same key. Only one of them could send the email. The other request waited, saw the key was gone, and moved on. It worked well.

How to choose

When I see a race condition, I ask myself a small set of questions.

Is the race about two users editing the same record? Is conflict rare? Then I use optimistic locking. It adds one column, a hidden field, and a clear error message.

Is the race about reading a balance and then writing a new balance? Then I use a pessimistic row lock.

Is the race about children under a parent? Then I lock the parent row.

Is the race about a logical resource that does not live in one row? Then I use a PostgreSQL advisory lock.

Do I have many workers claiming jobs from the same table? Then I use SKIP LOCKED.

Do I need to stop duplicate inserts? Then I add a unique index.

Do I need a lock across many servers and many processes, and the resource is not stored in PostgreSQL? Then I use a Redis distributed lock.

The database is the only place where multiple Rails processes truly agree. Application-level checks look nice in tests, but they do not survive real traffic. Locking patterns are not complicated once you see them as simple rules for coordinating who goes first. The code examples above are not theoretical. They are the exact tools I use when a race condition wakes me up at night.

Start with the simplest pattern that matches your problem. Add the version column or the row lock. Add the unique index before you need it. Let the database be the referee, and let your Rails code stay calm.