How to Ensure API Idempotency with Locks: 5 Proven Strategies

In real‑world development, scenarios such as retry mechanisms after interface timeouts or duplicate consumption in message queues can cause API idempotency issues; this article presents several lock‑based solutions.

1. Database Unique Primary Key

The unique primary key leverages the database's uniqueness constraint, suitable for idempotent insert operations. In distributed environments, a globally unique ID (e.g., a distributed ID) is used as the primary key.

2. Database Pessimistic Lock

After retrieving an order (e.g., order A), you can lock the row using SELECT ... FOR UPDATE . The lock holds until the transaction commits, preventing concurrent updates.

<code>select * from order where order_id = 123 for update</code>

The lock blocks other threads (e.g., T2) from accessing the same row until T1 finishes, which can affect performance if the transaction is long. It requires InnoDB and a primary key or unique index on the locked column.

3. Database Optimistic Lock

An extra version column is added to the table. Each update checks the version value, ensuring only one successful update.

<code>UPDATE order SET status=2, version=version+1 WHERE id=123 AND version=0;</code>

If the row has already been updated (version changed), the condition fails, making the operation idempotent.

4. State Machine Mechanism

When business tables have defined states (e.g., order status), the current state can be used as a condition to ensure idempotent transitions.

<code>update order set status = 2 where order_id = 123 and status = 1</code>

If the order is already in status 2, the condition fails and no rows are affected, achieving idempotency similar to optimistic locking.

5. Distributed Lock

Because database locks may impact performance, middleware such as Redis or Zookeeper can provide distributed locks. Using Redis, the unique request identifier (e.g., order number) is stored as a key with an expiration time.

If SETNX succeeds, the thread proceeds with business logic; otherwise, the request is recognized as duplicate and a success response is returned. The lock’s TTL must be balanced to avoid premature expiration or excessive memory usage.

In summary, each idempotency solution has its own advantages and drawbacks; developers should choose the most suitable approach based on the specific business scenario.