The Ack problem — Part 7

Common patterns in an API server

Introduction

It took us 6 posts to cover the “Ack problem” in-depth, and so far there seems to be no easy solution in sight. This post as well as the next one will (finally!) cover the typical approaches and solutions that can be used.

While the solutions depicted below have some limitations and won’t work in all possible cases, they have shown to offer good results in most “real-world” scenarios.

So without any further due, let’s explore how to address this “Ack problem”!

Core principle

Making an API idempotent is much facilitated when each downstream call can be made idempotent individually.

One key aspect is that the idempotency tokens that will be used for downstream calls must be one way or another derived from the idempotency token it received from upstream. Indeed, if the token is merely generated as a random value, then further retries will cause downstream APIs to receive a different idempotency token, making the downstream calls non-idempotent with regards to the original caller.

In other words, the only place where a new idempotency token should be generated is at the upmost level of the API call-chain, as close as possible to the business that triggered the transaction in the first place. It may be generated by the user agent, such as a Web brower. This token will then be the source of idempotency for all downstream calls through the API or messaging call chain.

If you do not forward the idempotency token to downstream calls, then your API is probably not idempotent.

Idempotency with a database

Unconditionally inserting a new record upon request is not idempotent: maybe the record was already created from a previous call, and this existing record should be returned instead. Let’s look at approaches with both PostgreSQL and MongoDB.

Creating a new record with PostgreSQL

The database should contain enough information to check the idempotency condition: typically the idempotency key such as the transaction reference should be stored in the record itself. This way, the existing record can be identified and returned upon creation request to avoid duplicate records.

If the record cannot be found, then it can be created. This logic can be made either with or without locks.

With locking

Simply checking for a record’s existence before creating a new one is race-condition prone as two calls for the same idempotency token may be running concurrently.

If there can be a “parent” row that can act as natural “container” for the new record (for instance, there is a customer table and the new record is owned by a customer, like an order or something), then this “parent” row can be locked to enforce call serialization.

createOrder:
  BEGIN TRANSACTION;
  -- We lock the customer before creating a new customer order
  SELECT id FROM customer WHERE id = order.customer_id FOR UPDATE;
  -- We check for existing order existence
  SELECT * FROM customer_order WHERE reference = order.reference;
  -- If the previous call returned an order, then return this order and do not proceed further
  (...)
  -- Otherwise, create the new order
  INSERT INTO customer_order VALUES(order.reference, order.customer_id, order.product_name, order.product_count, ...);
  -- Commit the transaction, releasing the customer lock
  COMMIT;

The downside of this approach is that only one call can be executed concurrently for any given customer. This may be an acceptable limitation depending on your needs.

Locking can lead to deadlocks, even in simple cases like the above example. Make sure you understand the RDBMS locking patterns (such as AB-BA deadlocks and lock ordering) before using a lock-based approach

It is not always possible to find a “parent” entity to act as a container. For instance, the above sample assumes that the customer record already exists in the database, but does not discuss how this customer record could itself be created in an idempotent way. Eventually either table-wide locking is necessary or lock-free implementation must be used instead.

Lock-free

By storing the idempotency key (such as a transaction reference) either as the record’s primary key or as a unique constraint, then PostgreSQL ON CONFLICT feature can be used:

createRecord:
  -- The record may or may not exist.
  INSERT INTO customer_order VALUES(order) ON CONFLICT DO NOTHING;
  -- Here, we know for use the record exists, let's read it back.
  SELECT * FROM customer_order WHERE reference = order.reference;

This pattern can be further refined, such as checking the returned record to see, based on some other fields (maybe an internal ID, maybe a timestamp, or anything else) whether the record was already existing prior, or whether it got created just now. This can sometimes be useful, for instance to further check that the rest of the record is consistent with the reference (for instance, to protect against a retry call using the same reference but using different parameters).

This example is only a very simple case on how to use the ON CONFLICT feature of PostgreSQL. The feature can be used to perform UPSERT logics with DO UPDATE instead of DO NOTHING statement. This post is not meant to be a comprehensive PostgreSQL training.

You need to be aware of race conditions with this lock-free implementation. For instance, if hard-deletes are used, then the record could be deleted between the INSERT and the SELECT statement, so the SELECT could return no record.

Additionally, the record could be updated between the two calls, so the record you get back from the SELECT may be different than either the previously existing record or the new request.

As usual, lock-free logic requires some additional care and thoughts compared to lock-based implementations.

Creating a new record with MongoDB

One way to achieve idempotent record creation with MongoDB is to use an atomic conditional insert:

orders.update(
  {
    reference: order.reference
  },
  {
    $setOnInsert: order
  },
  { upsert: true }
)

The returned UpsertedCount value can be used to check whether a new record was created or if an existing record already existed with this reference and therefore not updated. A subsequent find operation can be used to retrieve the actual value.

This example is only a very simple case on how to use the upsert feature of MongoDB. This post is not meant to be a comprehensive MongoDB training.

You need to be aware of race conditions with this implementation. For instance, if hard-deletes are used, then the record could be deleted immediately after the update call and before the latest value is read, so this subsequent read could fail.

Additionally, the record could be updated between the two calls, so the record you get back from the read may be different than either the previously existing record or the new request.

As usual, lock-free logic requires some additional care and thoughts compared to lock-based implementations.

Key takeaway with regards to idempotency and database

The database must contain enough information to verify the idempotency condition. In practice, this means that if your API accepts an idempotency token such as a transaction reference, this information must make its way to the database.

This is consistent with the initial observation that downstream calls must forward the upstream token one way or another.

In other words, if you don’t store the idempotency token in the database, your API is unlikely to be idempotent.

Idempotency with a message broker

Some message broker implementations allow you to specify a unique message id that will be used as an idempotency token. Most implementations, however, don’t, and those that support it come with limitations (typically, on how long will this message id will be stored, either in term of time or number of messages).

Specific consumer patterns will be discussed in the next post.

Idempotency with other APIs

This is probably the simplest of all. If you received an idempotency token from upstream, make sure that any downstream call you perform use idempotency tokens that are either the same or derived from the upstream data.