Using Atomic Transactions to Power an Idempotent API

Aligning HTTP Requests with Database Transactions

Most production web services are CRUD applications backed by ACID-compliant relational databases, yet the relationship between an HTTP request and a database transaction is rarely examined closely. In practice, a request is itself a unit of work: it has a clear start, an end, and a result the client expects to be atomic. Failures—client disconnects, application bugs, timeouts—are inevitable at scale, and databases offer the right tools to keep data consistent when they occur.

The cleanest model for an idempotent API is a 1:1 mapping: every request performs all of its operations inside a single transaction that either commits entirely or aborts entirely.

Idempotency sounds restrictive, but with careful endpoint design—and by pushing non-idempotent work like network calls into background jobs—most operations can be made idempotent. Genuinely non-idempotent requests need extra handling, which we'll return to below.

A Worked Example: User Creation

Consider a minimal service with one endpoint: a client sends an email parameter, and the service creates a user. The endpoint is idempotent, returning 201 Created on first creation and 200 OK on subsequent calls with the same parameter.

PUT /[email protected]

The backend performs three steps:

1. Check whether the user already exists; if so, do nothing.
2. Insert a new user record.
3. Insert a "user action" audit record containing the user's ID, an action name, and a timestamp.

The example uses Postgres with Ruby and an ActiveRecord- or Sequel-style ORM, but the approach transfers to any stack.

Schema

The schema has two tables: users and user_actions, with length checks, NOT NULL, and foreign key constraints for hygiene.

CREATE TABLE users (
    id    BIGSERIAL PRIMARY KEY,
    email TEXT      NOT NULL CHECK (char_length(email) <= 255)
);

-- our "user action" audit log
CREATE TABLE user_actions (
    id          BIGSERIAL   PRIMARY KEY,
    user_id     BIGINT      NOT NULL REFERENCES users (id),
    action      TEXT        NOT NULL CHECK (char_length(action) < 100),
    occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Implementation

The route checks for an existing user, returning early if found; otherwise it inserts the user and the action record. Either way the transaction commits.

put "/users/:email" do |email|
  DB.transaction(isolation: :serializable) do
    user = User.find(email)
    halt(200, 'User exists') unless user.nil?

    # create the user
    user = User.create(email: email)

    # create the user action
    UserAction.create(user_id: user.id, action: 'created')

    # pass back a successful response
    [201, 'User created']
  end
end

The generated SQL for a successful insert looks roughly like this:

START TRANSACTION
    ISOLATION LEVEL SERIALIZABLE;

SELECT * FROM users
    WHERE email = '[email protected]';

INSERT INTO users (email)
    VALUES ('[email protected]');

INSERT INTO user_actions (user_id, action)
    VALUES (1, 'created');

COMMIT;

Protecting Against Concurrency

The users table has no UNIQUE constraint on email, which leaves a window for a race: two concurrent transactions could both run their SELECT, find nothing, and both proceed to insert a duplicate.

This example deliberately avoids UNIQUE to demonstrate a stronger mechanism. The transaction runs at the SERIALIZABLE isolation level via DB.transaction(isolation: :serializable), which emulates serial execution as though each transaction ran one after the other. If a race would have allowed one transaction to affect another's results, one of the two fails to commit with an error like this:

ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

We won't dig into how SERIALIZABLE detects races; suffice it to say it catches several classes of data races and aborts the offending transaction at commit time.

Retrying a Serialization Abort

These aborts are rare but should not surface as a 500 to the client. Wrapping the request's core operations in a retry loop handles the contention cleanly:

MAX_ATTEMPTS = 2

put "/users/:email" do |email|
  MAX_ATTEMPTS.times do
    begin
      DB.transaction(isolation: :serializable) do
        ...
      end

      # Success! Leave the loop.
      break

    rescue Sequel::SerializationFailure
      log.error "Failed to commit serially: #{$!}"
      # Failure: fall through to the next loop.
    end
  end
end

In the worst case there may be more than one transaction attempt mapped to a single HTTP request:

The loop costs more than a single attempt, but it only matters under contention. Libraries like Sequel can automate this; the following behaves like the loop above:

DB.transaction(isolation: :serializable,
    retry_on: [Sequel::SerializationFailure]) do
  ...
end

Defense in Depth

Even though SERIALIZABLE prevents duplicate inserts, a real deployment should still add a UNIQUE constraint on email. It protects against misconfigured transactions or buggy code invoking the transaction at a weaker isolation level. Defense in layers is worth the extra constraint.

Background Jobs and Transaction Staging

Services commonly enqueue background jobs during a request so the client doesn't block on expensive work. Suppose user creation also notifies an external support service about the new account via a queued job.

put "/users/:email" do |email|
  DB.transaction(isolation: :serializable) do
    ...

    # enqueue a job to tell an external support service
    # that a new user's been created
    enqueue(:create_user_in_support_service, email: email)

    ...
  end
end

With a typical queue like Sidekiq, a transaction rollback—from a serialization abort, for instance—can leave an invalid job behind: it references data that no longer exists, so retries never succeed.

The fix is to stage jobs in the database before they reach the queue. Jobs are inserted into a staging table within the request's transaction, and a separate enqueuer process pulls them out in batches and forwards them to the job queue.

CREATE TABLE staged_jobs (
    id       BIGSERIAL PRIMARY KEY,
    job_name TEXT      NOT NULL,
    job_args JSONB     NOT NULL
);

The enqueuer selects jobs, enqueues them, and deletes them from the staging table. A rough implementation:

loop do
  DB.transaction do
    # pull jobs in large batches
    job_batch = StagedJobs.order('id').limit(1000)

    if job_batch.count > 0
      # insert each one into the real job queue
      job_batch.each do |job|
        Sidekiq.enqueue(job.job_name, *job.job_args)
      end

      # and in the same transaction remove these records
      StagedJobs.where('id <= ?', job_batch.last).delete
    end
  end
end

Because staged jobs are written inside the transaction, the isolation property of ACID guarantees they are invisible to other transactions until commit. A rolled-back job is never seen by the enqueuer and never reaches the queue. This pattern is called a transactionally-staged job drain.

An alternative is to put the job queue directly in the database with a library like Que, but bloat can become dangerous in Postgres, so that approach is less advisable.

Handling Non-Idempotent Requests

The 1:1 transaction model suits idempotent endpoints, which covers a well-designed API's majority. Some endpoints, however, cannot be made idempotent: charging a credit card through an external gateway, provisioning a server, or making synchronous network calls. These need a more sophisticated mechanism, such as idempotency keys, built on multi-stage transactions. That's the subject of a follow-up article.

Note that the enqueuer system described above guarantees "at least once" delivery, not "exactly once," so the jobs themselves must be idempotent.