class CreateInventoryReservations < ActiveRecord::Migration[7.1]
def change
create_table :inventory_items do |t|
t.string :sku, null: false
t.integer :quantity_on_hand, null: false, default: 0
t.integer :quantity_reserved, null: false, default: 0
t.timestamps
end
add_index :inventory_items, :sku, unique: true
add_check_constraint :inventory_items,
"quantity_reserved >= 0 AND quantity_reserved <= quantity_on_hand",
name: "reserved_within_on_hand"
create_table :reservations do |t|
t.references :inventory_item, null: false, foreign_key: true
t.string :dedupe_key, null: false
t.integer :quantity, null: false
t.string :status, null: false, default: "held"
t.timestamps
end
add_index :reservations, :dedupe_key, unique: true
end
end
class InventoryItem < ApplicationRecord
class InsufficientStock < StandardError; end
has_many :reservations, dependent: :restrict_with_exception
validates :sku, presence: true, uniqueness: true
def available
quantity_on_hand - quantity_reserved
end
def reserve!(quantity:, dedupe_key:)
raise ArgumentError, "quantity must be positive" unless quantity.positive?
transaction do
lock! # SELECT ... FOR UPDATE, reloads the row inside the tx
if available < quantity
raise InsufficientStock, "only #{available} of #{sku} available"
end
increment!(:quantity_reserved, quantity)
reservations.create!(quantity: quantity, dedupe_key: dedupe_key, status: "held")
end
end
end
class ReservationsController < ApplicationController
rescue_from InventoryItem::InsufficientStock, with: :render_insufficient
rescue_from ActiveRecord::RecordNotUnique, with: :render_conflict
def create
if (existing = Reservation.find_by(dedupe_key: params[:dedupe_key]))
return render json: existing, status: :ok
end
reservation = ActiveRecord::Base.transaction do
item = InventoryItem.find_by!(sku: params[:sku])
item.reserve!(quantity: params[:quantity].to_i, dedupe_key: params[:dedupe_key])
end
render json: reservation, status: :created
end
private
def render_insufficient(error)
render json: { error: error.message }, status: :unprocessable_entity
end
def render_conflict(_error)
render json: { error: "reservation already exists" }, status: :conflict
end
end
This snippet shows the classic problem of reserving stock under concurrent checkout traffic: two requests read the same available quantity, both decide there's enough, and both commit — an oversell. The fix is pessimistic row locking with SELECT ... FOR UPDATE, serializing the read-modify-write against a single inventory row for the duration of a transaction.
The create_inventory_reservations migration defines the two tables involved. inventory_items holds quantity_on_hand and quantity_reserved as non-null integers, and a check constraint (quantity_reserved <= quantity_on_hand) acts as a last line of defense at the database level — even if application logic is buggy, Postgres refuses to persist an oversell. The reservations table carries a dedupe_key with a unique index so the same logical checkout can be retried safely without double-reserving.
In InventoryItem model, reserve! is the core routine. It opens a transaction and calls lock!, which issues SELECT ... FOR UPDATE and reloads the row; any other transaction touching the same item now blocks until this one commits or rolls back. Only after acquiring the lock does it compute available and either raise InsufficientStock or increment quantity_reserved. Because the read and write happen inside the same locked window, the decision can't be invalidated by a competing writer. The available helper is a plain in-memory subtraction, correct only because callers hold the lock.
ReservationsController ties it together. It first guards on dedupe_key by returning any existing Reservation, giving idempotent retries. The find_by! and the reserve! call run inside one ActiveRecord::Base.transaction, so the reservation row and the counter update commit atomically. A rescue of InsufficientStock maps cleanly to 422, while the unique index converts a racing duplicate into RecordNotUnique, handled as a 409.
The main trade-off is throughput: FOR UPDATE serializes access per item, so a single hot SKU becomes a contention point. It's the right tool when correctness matters more than raw concurrency on that row. Keeping transactions short — lock, decide, write, commit — minimizes the blocking window, and lock ordering matters if a request ever reserves multiple items to avoid deadlocks.
Related snips
class CommentsController < ApplicationController
before_action :set_post
def create
@comment = @post.comments.build(comment_params)System test: asserting Turbo Stream responses
class Post < ApplicationRecord
belongs_to :author, class_name: 'User'
has_many :comments, dependent: :destroy
scope :published, -> { where.not(published_at: nil).where('published_at <= ?', Time.current) }
scope :draft, -> { where(published_at: nil) }ActiveRecord scopes for reusable query logic
module Api
module V1
class UsersController < BaseController
def show
user = User.includes(:profile).find(params[:id])ETags for conditional requests and caching
class PostsController < ApplicationController
def index
@posts = Post.includes(:author)
.order(created_at: :desc)
.page(params[:page])
.per(10)Turbo Frames: infinite scroll with lazy-loading frame
package dbutil
import (
"context"
"github.com/jackc/pgconn"Retry Postgres serialization failures with bounded attempts
require "csv"
class PeopleCsvStream
include Enumerable
HEADERS = %w[id full_name email signed_up_at plan].freezeResilient CSV Export as a Streamed Response
Share this code
Here's the card — post it anywhere.
