Rails Locking, Transaction, and Command Replay

Working case

Reviewer 47 approves case 62 while reviewer 81 loses assignment to it. Two requests arrive close together; one comes from a browser retry after a dropped response. If the controller calls update!(status: 'approved') directly, it can create duplicate audit rows and ignore the assignment change. A callback sends an email before commit, then a later validation raises and the database rolls back, leaving a false notice. The repaired service checks the existing operation key first, locks the current case row, verifies revision and assignment again, and commits one case transition, operation result, and outbox row. A repeated exact request reads the stored outcome; a different command under that key is rejected.

Implementation boundary

require "digest"
require "json"

class ApprovePermit
  def self.call(reviewer:, case_id:, operation_id:, expected_revision:, reason:)
    fingerprint = Digest::SHA256.hexdigest(JSON.generate([case_id, expected_revision, reason]))
    PermitCase.transaction do
      permit = PermitCase.lock.find(case_id)
      prior = ApprovalOperation.find_by(case_id: case_id, operation_id: operation_id)
      if prior
        raise OperationConflict unless prior.reviewer_id == reviewer.id && prior.command_digest == fingerprint
        prior
      else
        raise NotAuthorized unless permit.reviewer_assignments.exists?(reviewer_id: reviewer.id)
        raise StaleRevision unless permit.revision == expected_revision

        permit.update!(status: "approved", revision: permit.revision + 1)
        operation = ApprovalOperation.create!(
          operation_id: operation_id, case_id: case_id, reviewer_id: reviewer.id,
          command_digest: fingerprint, reason: reason, result_status: permit.status
        )
        ApprovalOutbox.create!(approval_operation_id: operation.id, state: "pending")
        operation
      end
    end
  end
end

Define an operation ID namespace and enforce its uniqueness in the database, not a process-local cache. Parse the revision and reason strictly at the controller boundary. Inside a short transaction, lock the case where the chosen database supports row locks, then check reviewer assignment, legal transition, and expected revision against current state. Insert the operation record and outbox event in the same transaction as the status update. If a duplicate key race occurs, reload the winner's operation and compare its command digest before returning its saved public result. Do not swallow serialization or deadlock errors with a partial response; retry the whole transaction only for the database's explicitly retryable errors, with a bounded attempt budget and no external side effects inside the closure.

Cost and boundaries

A row lock serializes conflicting approvals on case 62 and may increase wait time for a hot queue. Keeping the transaction short reduces lock duration. A unique index on operation identity adds write and storage cost but arbitrates across all application processes. Retaining operation results for the promised retry horizon uses space and needs a deletion policy that will not make late legitimate retries execute twice. The outbox adds one write and later scan work. A whole-transaction retry can multiply database work under contention, so record lock wait, retry count, duplicate-key incidence, and p95 command latency. Returning a stored replay outcome can be cheaper than repeating mutation work, but only after the payload identity is checked.

Failure trace

Post a stale revision, an invalid reason, a case with no current assignment, and an already-approved case; each must have a distinct deliberate result with no extra audit event. Race two exact requests under one operation ID from separate connections, then assert one status transition and one outbox row. Drop the successful HTTP response and replay the exact command. Reuse the ID with a different reason and require a conflict. Raise after case update but before outbox insertion and confirm rollback removes every change. Simulate a database deadlock and verify any retry reruns the entire transaction, not only the last statement. Record whether SQLite test behavior differs from the production database's row-lock semantics.

Verification

• Permission and revision are checked against locked current state.
• Case, operation, and outbox rows share one transaction.
• A uniqueness constraint resolves concurrent replay across processes.

Practice drill

Implement ApprovePermit as a service object with reviewer ID, case ID, operation ID, expected revision, and reason. Seed case 62 in review at revision 5. Add a unique operation identity, command digest, stored public outcome, and notification outbox table. Use a short transaction and current-state lock; write the case, operation, and event together. Exercise simultaneous same-key calls, two different keys competing for the same revision, a lost response, changed-payload replay, and a rollback after status update. Keep the controller responsible for request parsing and response mapping. Measure lock waits and inspect the final database rows rather than judging correctness from HTTP status alone.

Decision note

Make current-state permission and one logical command outcome database facts, then let retries recover them.

Common Mistakes

• Passing status from strong params directly into update!.
• Using only a controller-side existence check for replay.
• Sending email from a retried transaction or save callback.

Related lessons

Rails Request, Record, and Job Boundaries; Rails Controller Parameters and Object Permission; Rails Active Record Scope, Eager Load, and Page Cost; Rails Active Job After-Commit and Outbox Recovery; API Mutation and Failure Contracts; Django Forms, CSRF, Atomic Approval, and On-Commit Work; Laravel Form Requests, Transactions, and Approval Replay.

Apply and check

Build Project: Rails permit review workflow and review Web Development: Rails request, record, and job quiz.