Idempotency keys in databases: Preventing duplicate transactions

I remember the first time a retry storm hit our payment processor. A frontend client lost its connection mid-request, automatically retried the POST, and suddenly, a single customer was charged three times in under 300ms. It was a mess to reconcile, and it taught me that relying on application-level logic for state consistency in distributed systems is a dangerous game.

If you’re building services that move money or update critical state, you need idempotency at the storage layer. Relying on your backend code to "check if a record exists" before inserting is a recipe for disaster when concurrent requests arrive at the same time.

Why Application-Level Checks Fail

Most engineers start by querying the database: "If record doesn't exist, create it." In a single-threaded environment, this works perfectly. But in a distributed system, you're dealing with multiple application nodes and network latency.

Consider this sequence:

1. Node A receives Request 1, queries the database, and sees no record for idempotency_key_123.
2. Node B receives Request 2 (a retry of the same operation) and queries the database before Node A has finished its insert.
3. Node B also sees no record.
4. Both nodes proceed to insert.

You end up with duplicate rows. Even if you use Laravel Distributed Locks: Preventing Race Conditions with Redis to serialize access, network partitions can cause your locks to expire or drift. You need a source of truth that is strictly enforced: the database schema itself.

Implementing Idempotency Keys with Database Constraints

The most robust way to ensure distributed transactions don't result in duplicates is to lean on your database's unique constraint engine. When you define your schema, you should treat the idempotency key as a first-class citizen.

Instead of a simple check-and-insert, define a UNIQUE index on your idempotency key column.

SQL
CREATE TABLE transaction_log (
    id BIGSERIAL PRIMARY KEY,
    idempotency_key VARCHAR(255) UNIQUE NOT NULL,
    amount DECIMAL(19, 4),
    status VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

By adding that UNIQUE constraint, the database now handles the concurrency logic for you. If a second request arrives with the same key, the database will throw a constraint violation error. You catch this error in your code and return a 200 OK (or a 409 Conflict, depending on your API design) instead of creating a second record.

Handling Race Conditions and Retries

Once the constraint is in place, your application flow changes. You no longer "check and insert." You "try to insert and handle the conflict."

When you use Idempotency keys: Making Retries Safe in Distributed Systems, the logic becomes atomic. If the database rejects the second insert, you can query for the existing record that matches that key and return the original result. This creates a deterministic outcome regardless of how many times the client retries.

However, be careful with your schema design. If your keys are high-cardinality strings, ensure you're using an efficient index type. While Database Schema Design: Choosing Primary Keys for Performance often focuses on primary keys, the same performance considerations apply to your idempotency key indexes. A B-tree index is usually sufficient, but keep an eye on index bloat if you're processing millions of transactions.

The Trade-off: Performance vs. Safety

Is this perfect? Not quite. Every UNIQUE constraint adds a small overhead to your write operations because the database must check the index before committing the transaction. In my experience, this usually adds about 2-5ms to write latency—a price well worth paying to avoid the nightmare of duplicate financial transactions.

Also, consider what happens if your system crashes after the database insert but before the application sends the response. Your client will retry, but the database will now block the second attempt because the key exists. Your code must be prepared to handle these "already processed" scenarios gracefully. You aren't just preventing duplicates; you're implementing a state machine.

FAQ

What if I need to update the record later? The idempotency key should represent the intent of the operation. If you need to update state, use a separate status flag or a version column. Don't try to reuse the same idempotency key for different lifecycle stages of a single transaction.

How long should I keep these keys? I typically keep them for 24 to 48 hours. After that, the likelihood of a legitimate retry drops to near zero. You can use a TTL index (like in MongoDB) or a simple background job to prune the table and keep index size manageable.

What if my database doesn't support unique constraints on large text fields? If you're using a system where you can't index long strings, hash the idempotency key to a fixed-length string (like a SHA-256 hash) and store that in an indexed column. Just remember to handle potential hash collisions if your volume is massive.

I’m still experimenting with how to best handle "partial" failures—where the record exists but the initial transaction was in a 'pending' state. For now, I stick to a strict status column and force the client to query the status if they hit a conflict. It’s not elegant, but it’s consistent. Distributed systems are never truly solved; they’re just managed better over time.