<commit_before>---
title: How to Map an Atomic Transaction onto an Idempotent
  HTTP Request
published_at: 2017-09-05T20:29:07Z
location: San Francisco
hook: TODO
---

A lot of us are building applications that server requests
over the lingua franca of today's internet -- HTTP. Many of
those same applications are backed by a relational database
like Postgres, and requests perform a series of operations
against it to complete.

There's a surprising symmetry between an HTTP request and a
database's transaction. Just like the transaction, an HTTP
request is a transactional piece of work -- it's got a
clear beginning, end, and result. The client generally
expects one to execute atomically (although whether it
actually does may vary based on implementation).

The sharp edges encountered in the real world can lead to
all kinds of unexpected cases during the execution of an
HTTP request -- client disconnects, application bugs that
fail a request midway through, and various sorts of
timeouts will occur regularly given enough request volume.
In these cases a relational DB's transaction can be a
powerful tool for making sure that data stays correct even
given these sorts of adverse conditions.

I'm going to suggest that for a common idempotent HTTP
request, requests map to backend transactions at 1:1. At
first glance requiring idempotency may sound like a
sizeable caveat, but in a well-designed API, many
operations can be tweaked so that they're idempotent.

!fig src="/assets/http-transactions/http-transactions.svg" caption="Transactions (tx1, tx2, tx3) mapped to HTTP requests at a 1:1 ratio."

## Let's create a user (#create-user)

Lets come up with a simple test scenario where we're
building an idempotent "create user" endpoint. A client
hits it with a single `email` parameter, and the endpoint
responds with status `201` if the user already existed, and
status `200` otherwise.

```
PUT /users?email=<EMAIL>
```

On the backend, we want to insert a record for a new user,
and we also want to insert a "user action", a record that
will serve as an audit log and which comes with a reference
to a user's ID, an action name, and a timestamp.

In SQL [1]:

``` sql
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()
);
```

The server route checks to see if the user exists. If so,
it returns immediately. If not, it creates the user and
user action, and returns. In both cases the transaction
commits successfully:

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

    user = User.create(email: email)
    UserAction.create(user_id: user.id, action: 'created')
    [201, 'User created']
  end
end
```

The first time we invoke the endpoint we get a `200 OK`:

``` sh
$ curl -i http://localhost/users/<EMAIL>
HTTP/1.1 200 OK
...

User created
```

And the next time, a `201 Created`:

``` sh
$ curl -i http://localhost/users/<EMAIL>
HTTP/1.1 201 Created
...

User exists
```

The SQL that's generated on the successful insertions looks
roughly like:

``` sql
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM users WHERE email = '<EMAIL>';
INSERT INTO users (email) VALUES ('<EMAIL>');
INSERT INTO user_actions (user_id, action) VALUES (1, 'created');
COMMIT;
```

## Concurrency protection (#concurrency-protection)

Readers with sharp eyes may have noticed a potential
problem: our user's `email` column doesn't have a `UNIQUE`
constraint. The lack of one could potentially allow two
interleaved transactions to run their `SELECT` phase one
concurrently and get empty results. They'd both follow up
with an `INSERT`, thus leaving a duplicated row.

!fig src="/assets/http-transactions/concurrent-race.svg" caption="A data race causing two concurrent HTTP requests to insert the same row."

Luckily, the magic of the `SERIALIZABLE` isolation level
protects us from harm here (see where we invoke
`DB.transaction(isolation: :serializable)`). It emulates
serial transaction execution as if each outstanding
transaction had been executed one after the other, rather
than concurrently. In cases like the above where a race
condition would have caused one transaction to taint the
results of another, one of the two will fail to commit with
a message like this one:

```
# COMMIT;
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.
Time: 0.291 ms
```

Even though this race should be relatively rare, we'd
prefer to handle it correctly in our application code. This
is possible by wrapping the request's core operations in a
loop:

``` ruby
MAX_ATTEMPTS = 2

put "/users/:email" do |email|
  MAX_ATTEMPTS.times do
    begin
      DB.transaction(isolation: :serializable) do
        ...
      end
      break
    rescue Sequel::SerializationFailure
      log.error "Failed to commit serially: #{$!}"
      # fall through to the next loop
    end
  end
end
```

In this case, we might have more than one of the same
transaction mapped to the HTTP request like so:

!fig src="/assets/http-transactions/transaction-retry.svg" caption="An aborted transaction being retried within the same request."

These loops will be more expensive than usual, but keep in
mind that we're only protecting against an unusual
concurrency access race. In practice, unless callers are
particularly contentious, it should rarely occur.

Gems like Sequel can handle this for you automatically:

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

And just as short addendum, although I've taken this
opportunity to demonstrate the power of a serializable
transaction, in real life you'd want to put in a `UNIQUE`<commit_msg>More comments<commit_after>---
title: How to Map an Atomic Transaction onto an Idempotent
  HTTP Request
published_at: 2017-09-05T20:29:07Z
location: San Francisco
hook: TODO
---

A lot of us are building applications that server requests
over the lingua franca of today's internet -- HTTP. Many of
those same applications are backed by a relational database
like Postgres, and requests perform a series of operations
against it to complete.

There's a surprising symmetry between an HTTP request and a
database's transaction. Just like the transaction, an HTTP
request is a transactional piece of work -- it's got a
clear beginning, end, and result. The client generally
expects one to execute atomically (although whether it
actually does may vary based on implementation).

The sharp edges encountered in the real world can lead to
all kinds of unexpected cases during the execution of an
HTTP request -- client disconnects, application bugs that
fail a request midway through, and various sorts of
timeouts will occur regularly given enough request volume.
In these cases a relational DB's transaction can be a
powerful tool for making sure that data stays correct even
given these sorts of adverse conditions.

I'm going to suggest that for a common idempotent HTTP
request, requests map to backend transactions at 1:1. At
first glance requiring idempotency may sound like a
sizeable caveat, but in a well-designed API, many
operations can be tweaked so that they're idempotent.

!fig src="/assets/http-transactions/http-transactions.svg" caption="Transactions (tx1, tx2, tx3) mapped to HTTP requests at a 1:1 ratio."

## Let's create a user (#create-user)

Lets come up with a simple test scenario where we're
building an idempotent "create user" endpoint. A client
hits it with a single `email` parameter, and the endpoint
responds with status `201` if the user already existed, and
status `200` otherwise.

```
PUT /users?email=<EMAIL>
```

On the backend, we want to insert a record for a new user,
and we also want to insert a "user action", a record that
will serve as an audit log and which comes with a reference
to a user's ID, an action name, and a timestamp.

In SQL [1]:

``` sql
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()
);
```

The server route checks to see if the user exists. If so,
it returns immediately. If not, it creates the user and
user action, and returns. In both cases the transaction
commits successfully:

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

    user = User.create(email: email)
    UserAction.create(user_id: user.id, action: 'created')
    [201, 'User created']
  end
end
```

The first time we invoke the endpoint we get a `200 OK`:

``` sh
$ curl -i http://localhost/users/<EMAIL>@<EMAIL>.<EMAIL>
HTTP/1.1 200 OK
...

User created
```

And the next time, a `201 Created`:

``` sh
$ curl -i http://localhost/users/jane@foo.<EMAIL>
HTTP/1.1 201 Created
...

User exists
```

The SQL that's generated on the successful insertions looks
roughly like:

``` sql
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT * FROM users WHERE email = '<EMAIL>';
INSERT INTO users (email) VALUES ('<EMAIL>');
INSERT INTO user_actions (user_id, action) VALUES (1, 'created');
COMMIT;
```

## Concurrency protection (#concurrency-protection)

Readers with sharp eyes may have noticed a potential
problem: our user's `email` column doesn't have a `UNIQUE`
constraint. The lack of one could potentially allow two
interleaved transactions to run their `SELECT` phase one
concurrently and get empty results. They'd both follow up
with an `INSERT`, thus leaving a duplicated row.

!fig src="/assets/http-transactions/concurrent-race.svg" caption="A data race causing two concurrent HTTP requests to insert the same row."

Luckily, the magic of the `SERIALIZABLE` isolation level
protects us from harm here (see where we invoke
`DB.transaction(isolation: :serializable)`). It emulates
serial transaction execution as if each outstanding
transaction had been executed one after the other, rather
than concurrently. In cases like the above where a race
condition would have caused one transaction to taint the
results of another, one of the two will fail to commit with
a message like this one:

```
# COMMIT;
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.
Time: 0.291 ms
```

Even though this race should be relatively rare, we'd
prefer to handle it correctly in our application code. This
is possible by wrapping the request's core operations in a
loop:

``` ruby
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 this case, we might have more than one of the same
transaction mapped to the HTTP request like so:

!fig src="/assets/http-transactions/transaction-retry.svg" caption="An aborted transaction being retried within the same request."

These loops will be more expensive than usual, but keep in
mind that we're only protecting against an unusual
concurrency access race. In practice, unless callers are
particularly contentious, it should rarely occur.

Gems like Sequel can handle this for you automatically:

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

And just as short addendum, although I've taken this
opportunity to demonstrate the power of a serializable
transaction, in real life you'd want to put in a `UNIQUE`
{'fraction_non_alphanumeric': 0.07280186630198697, 'fraction_numerical': 0.00997506234413965, 'mean_word_length': 4.270029673590504, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '229681', 'n_tokens_mistral': 3586, 'n_tokens_neox': 3346, 'n_words': 1739}