Reading Settings

Font Size

18px

Line Height

1.5

Letter Spacing

0.01em

Font Family

Table of contents

Safe Concurrent Updates in Rails with Locks and Atomic SQL

Software Engineer

Ruby on Rails

2025-08-08 10:21:06 +0700 · 4 mins read

Some applications require handling concurrent updates, like buying concert tickets or transferring money. In these cases, data consistency and atomicity are critical. Any mistake can lead to overbooking, double-spending, or even lost money. This post walks through how to handle concurrent updates safely in Rails using atomic SQL and pessimistic locking

1. Setting the Scene

Imagine you're building a simple inventory system. You have one product, and only 10 units in stock. During a flash sale, thousands of users try to buy it at the same time.

// language: ruby
class Item < ApplicationRecord
  # columns: id:integer, name:string, stock:integer
end

Now you want to decrement stock when someone places an order:

// language: ruby
item = Item.find(1)
if item.stock > 0
  item.stock -= 1
  item.save!
else
  raise "Out of stock"
end

This works locally. But in production, under real concurrency, it can lead to overselling. Why? Because multiple requests can read the same stock before any write completes.

2. Safe & Atomic with SQL

Here’s the simplest atomic way to decrement stock:

// language: ruby
updated = Item.where("id = ? AND stock > 0", 1)
              .update_all("stock = stock - 1")
raise "Out of stock" if updated == 0

Generated SQL:

// language: sql
UPDATE items SET stock = stock - 1 WHERE id = 1 AND stock > 0;

This is a single atomic operation handled entirely by the database. It uses the database’s built-in row-level locking mechanism — so when multiple transactions try to update the same row, the database ensures they are processed one at a time, even under extreme concurrency.

If 10,000 users hit this endpoint at once:

The DB will serialize access to that row.
Only 10 requests will succeed.
The rest will either fail or retry depending on your app logic.

3. What About Pessimistic Locking?

What if you still want to use standard ActiveRecord logic but avoid races?

// language: ruby
Item.transaction do
  item = Item.lock.find(1)
  raise "Out of stock" if item.stock <= 0

  item.stock -= 1
  item.save!
end

Generated SQL:

// language: sql
SELECT * FROM items WHERE id = 1 FOR UPDATE;

This locks the row until the transaction completes. No one else can read or write it with a lock until then.

Approach         | Scope                  | Pros                   | Cons
UPDATE ... WHERE | Single record          | Fast and simple        | Only works for simple logic
lock.find        | Single or multi-record | Flexible, more control | Slower, risk of deadlocks

4. Can We Use Atomic SQL for Two Records?

No — atomic SQL is great for single-row updates, but doesn't work when two rows must be modified together (e.g., transferring money between two accounts). In that case, use pessimistic locking:

// language: ruby
Account.transaction do
  from, to = [a_id, b_id].sort
  sender = Account.lock.find(from)
  receiver = Account.lock.find(to)
  sender, receiver = sender.id == a_id ? [sender, receiver] : [receiver, sender]

  raise "Insufficient funds" if sender.balance < amount

  sender.balance -= amount
  receiver.balance += amount

  sender.save!
  receiver.save!
end

Generated SQL:

SELECT * FROM accounts WHERE id = ? FOR UPDATE;
SELECT * FROM accounts WHERE id = ? FOR UPDATE;

Be careful of deadlocks!

If two users submit concurrent transfers:

Transaction A locks Account A, then tries to lock Account B.
Transaction B locks Account B, then tries to lock Account A.
Both transactions are waiting for each other — causing a deadlock.

Most databases will detect this and roll back one of them. But the retry adds latency and complexity.

To avoid deadlocks: always lock rows in a consistent order — such as sorting account IDs before locking. This guarantees that every transaction tries to acquire locks in the same sequence, eliminating the cycle.

5. When This Becomes a Bottleneck

Even with atomic updates or row locks, your app can get overwhelmed:

Thousands of users hitting the same item
Slow DB queries
Long transactions holding locks

When that happens, you can offload pressure by using Redis as a pre-check layer before touching the database. Redis is an in-memory, single-threaded key-value store. Its atomic operations (like DECR) are extremely fast, usually completing in microseconds and do not involve disk or complex transactions.

Using Redis for a quick counter check lets you:

Reject excess requests early
Avoid hitting the DB unnecessarily
Reduce contention and improve response times

Example Redis-based limiter:

// language: ruby
if redis.decr("item:1:stock") >= 0
  # proceed with DB write
else
  raise "Out of stock"
end

Related blogs

I just made a serious mistake with Rails destroy_all

Rails Active Record is convenient and human-readable for interacting with SQL models. But not understanding the generated SQL behind these elegant methods can cause serious data loss that might go unnoticed until it's too late.1. Setting the SceneThi...

Software Engineer

Ruby on Rails

2025-06-23 18:47:22 +0700 · 4 mins read

Modern JavaScript OOP Features: True Private Fields, Static Methods, and More

When I first started learning JavaScript, its object-oriented features felt like an afterthought. The language had objects and prototypal inheritance, but it was missing key features like true private fields and methods. That’s why many developers tu...

Software Engineer

2025-06-28 21:18:52 +0700 · 5 mins read