Introduction

The concept of idempotency is a common challenge faced by developers regardless of the programming language used. This article presents practical strategies for handling idempotent operations, many of which have been successfully implemented in real-world projects. These approaches can serve as a reference for those who encounter similar issues.

Have you ever experienced these situations?

1. When submitting a form, accidentally clicking the save button twice results in duplicate records being created, differing only by an auto-incremented ID.
2. To handle timeout issues, retry mechanisms are often introduced. If an initial request times out, it may be retried multiple times, leading to duplicated data if the first attempt actually succeeded.
3. In message queue consumers, duplicate messages can sometimes be processed, potentially causing data duplication if not handled correctly.

These scenarios all illustrate idempotency problems.

API idempotency means that making one or multiple identical requests should produce the same result, without side effects from repeated actions.

Such issues commonly occur during:

• Insert operations: Multiple requests may create duplicate entries.
• Update operations: Simple updates like UPDATE user SET status=1 WHERE id=1 are safe. However, operations involving calculations, such as UPDATE user SET status=status+1 WHERE id=1, might cause incorrect data when executed multiple times.

How do we ensure API idempotency? Here's how.

1. Check Before Insert

To prevent duplicates during data insertion, a typical approach is to query existing data before inserting. If the record exists, perform an update; otherwise, proceed with an insert.

This method is widely used but isn't suitable alone in concurrent environments. It must be combined with other techniques to avoid race conditions. This note is to warn against potential pitfalls.

2. Use Pessimistic Locking

In payment scenarios, suppose user A has a balance of 150 yuan and wants to transfer 100 yuan. The standard SQL would be:

UPDATE user SET amount = amount - 100 WHERE id = 123;

Repeated requests could lead to negative balances, which is a critical system error.

To mitigate this, pessimistic locking can be applied to lock the row until the transaction completes:

SELECT * FROM user WHERE id = 123 FOR UPDATE;

Steps involved:

1. Multiple requests query user info by ID.
2. Check if the balance is sufficient. Return error if not.
3. If sufficient, re-query with FOR UPDATE to acquire the lock.
4. Only the first request acquires the lock and proceeds with the update.
5. Other requests wait for the lock.

Important: Requires InnoDB storage engine for transaction support. The ID column must be a primary key or unique index to avoid locking entire tables.

Pessimistic locking can cause performance bottlenecks due to long-running transactions.

While useful for preventing duplicates, it’s less ideal for true idempotency because response consistency isn't guaranteed.

Note: Duplicate prevention aims to stop duplicate data, whereas idempotency ensures consistent responses across repeated calls.

3. Apply Optimistic Locking

Given performance concerns with pessimistic locking, optimistic locking offers a better alternative. Add a version or timestamp field to your table.

Before updating:

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

Assuming version is 1, update with version check:

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

After updating, increment the version. Then check affected rows:

• If > 0, update was successful.
• If == 0, it's a duplicate request.

Subsequent requests will fail to update since version no longer matches.

Steps:

1. Query user data including version.
2. Update with version condition.
3. Check number of affected rows.
   • 1: Proceed with further logic.
   • 0: Return success (duplicate).

4. Implement Unique Constraints

Adding unique constraints in the database is a simple yet effective way to prevent duplicates.

Example:

ALTER TABLE `order` ADD UNIQUE KEY `un_code` (`code`);

On the first insert, it succeeds. Subsequent attempts throw a duplicate entry exception.

In Java applications, catch exceptions like DuplicateKeyException or MySQLIntegrityConstraintViolationException.

Steps:

1. Submit data via API.
2. Attempt insert into DB.
3. If successful, continue processing.
4. If failed due to constraint violation, return success.

5. Create a Deduplication Table

When not all cases require uniqueness, creating a separate deduplication table is helpful.

The table only needs two fields: id and a composite unique identifier (e.g., name_code).

Steps:

1. User submits request.
2. Insert into deduplication table.
3. If successful, proceed with business logic.
4. If failed due to conflict, return success.

Note: The deduplication table and main table must reside in the same database and operate within the same transaction.

6. Leverage State Machines

When working with stateful entities like orders (e.g., 1-pending, 2-paid, 3-completed), use state transitions to enforce idempotency.

Example:

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

First request updates status from 2 to 3, affecting 1 row. Second request finds no matching rows (status is now 3), affecting 0 rows.

Return success even when no actual change occurs.

Steps:

1. Submit request.
2. Update based on current state.
3. Check affected rows.
   • 1: Continue processing.
   • 0: Return success (duplicate).

Applies only when the target entity has a state field undergoing sequential changes.

7. Utilize Distributed Locks

Databases inherently provide distributed locking through unique constraints or dedicated tables. For improved performance, consider alternatives like Redis or Zookeeper.

Redis-based locking typically uses commands such as SETNX, SET, or frameworks like Redission.

Steps:

1. Generate a unique order code.
2. Store it in Redis with TTL.
3. If store succeeds, proceed with operation.
4. If not, it's a duplicate — return success.

Ensure appropriate TTL settings to avoid stale locks.

8. Token-Based Approach

Another strategy involves tokans requiring two-step interaction:

1. Request a token.
2. Use the token to complete the action.

Steps:

1. Browser requests token upon page load.
2. Server generates and stores token in Redis, returning it.
3. On subsequent requests, validate token presence in Redis.
   • Not found: First time — proceed.
   • Found: Duplicate — return success.
4. Token automatically expires after TTL.

Tokens must be globally unique.