Practical Techniques for Ensuring API Idempotency

Introduction

API idempotency means that multiple identical requests produce the same result without side effects. Common situations that break idempotency include rapid double‑clicks on a form, retry mechanisms after timeouts, and duplicate messages consumed from a message queue.

1. Pre‑select before Insert

Before executing an INSERT, query the table by a unique field (e.g., name or code). If the record exists, perform an UPDATE; otherwise, proceed with the INSERT. This method works well in single‑threaded scenarios but may fail under high concurrency.

2. Pessimistic Lock

Use a row‑level lock to ensure only one request updates the data at a time. Example SQL for locking a user row: SELECT * FROM user WHERE id=123 FOR UPDATE; The workflow is: multiple requests query the user, check balance, acquire the lock with FOR UPDATE, the first request updates the balance, others wait, and duplicate requests return success after the lock is released.

MySQL must use the InnoDB engine and the locked column should be a primary key or unique index.

3. Optimistic Lock

Add a version (or timestamp) column. When updating, include the current version in the WHERE clause and increment the version atomically:

SELECT id, amount, version FROM user WHERE id=123;

UPDATE user SET amount=amount+100, version=version+1 WHERE id=123 AND version=1;

If the affected rows count is 0, the request is a duplicate and can return success.

4. Unique Index

Create a unique index on a business‑critical column (e.g., code). The first insert succeeds; subsequent identical inserts raise a Duplicate entry error, which should be caught (e.g., DuplicateKeyException in Java) and treated as a successful idempotent operation.

5. Anti‑Duplicate Table

Maintain a lightweight table that stores only the unique key (e.g., id and code). Insert the key first; if a unique‑key conflict occurs, treat the request as a duplicate and return success.

The anti‑duplicate table and the business table must reside in the same database and be part of the same transaction.

6. State‑Machine Check

When the business table has a status field, update only if the current status matches the expected previous state. Example:

UPDATE `order` SET status=3 WHERE id=123 AND status=2;

If the update affects 0 rows, the request is a duplicate.

This method only applies when the table has a well‑defined status progression.

7. Distributed Lock

Use Redis (or Zookeeper) to implement a distributed lock. Common patterns include SETNX, the SET command with expiration, or libraries such as Redisson. The lock key is usually a business‑unique identifier (e.g., order code). If the lock acquisition fails, the request is considered duplicate.

Set a reasonable expiration time to avoid long‑lasting locks that waste Redis memory.

8. Token‑Based Approach

Generate a one‑time token (stored in Redis) on the first request. The second request must present the token to complete the operation. If the token is missing or already used, treat the request as duplicate.

Tokens must be globally unique and have an expiration.

Conclusion

All the above techniques aim to guarantee API idempotency; some focus on preventing duplicate data (anti‑duplicate design), while others also require consistent response values (idempotent design). Choose the appropriate method based on performance, concurrency, and business requirements.