A customer clicks "Pay" twice, a mobile app retries after a timeout, or a queue job runs again after a worker restart, and suddenly the ERP shows two identical invoices, two stock deductions or two supplier payments. If you need to prevent duplicate transactions, the fix is not a disabled button or a quick check in the controller. It is a combination of database idempotency, unique constraints and correct transaction handling, so the same business operation can arrive many times but is only applied once. This guide walks through the problem, symptoms, root cause and a step-by-step fix for PostgreSQL and Laravel based ERP systems.
Problem: The Same Operation Is Applied More Than Once
Every ERP has operations that must happen exactly once from a business point of view: posting a payment, issuing goods from a warehouse, creating a journal entry, approving a payroll run. The network and the infrastructure around them, however, deliver requests at least once. Browsers resend forms, load balancers retry upstreams, HTTP clients retry on timeouts, and queue systems redeliver jobs that did not acknowledge in time.
When the application treats each delivery as a new operation, you get duplicate rows that look valid. They pass validation, they have different primary keys, and they quietly break transaction integrity in your ledgers and stock balances.
Symptoms of Duplicate Transactions in an ERP
- Two payments or receipts with the same amount, customer and reference, created a few hundred milliseconds apart.
- Stock on hand is lower than the physical count because the same goods issue was posted twice.
- Bank reconciliation shows one bank entry matched against two ERP entries.
- Logs show the same request ID or job payload processed twice, often after a
504 Gateway Time-outor a worker restart. - Queue workers log messages like
Job has been attempted too many timesorMaxAttemptsExceededException, while earlier attempts actually succeeded. - Adding a unique index later fails with
ERROR: could not create unique index ... Key (...) is duplicated.because duplicates already exist.
Root Cause: At-Least-Once Delivery Without Idempotency
Duplicate transactions almost always come from one of these root causes:
- Client and proxy retries. The server finished the work, but the response was lost or slow. The client retried, and the server did the work again.
- Check-then-insert race conditions. Code runs
SELECTto see whether a payment exists, finds nothing, then runsINSERT. Two concurrent requests both pass the check. This is a classic race condition in programming. - Queue redelivery. A job commits to the database but crashes or times out before acknowledging, so the broker delivers it again.
- No natural business key. The table only has an auto-increment
id, so the database has no way to know that two rows represent the same real-world event. - Side effects outside the transaction. An external payment gateway is charged before the local transaction commits, and a retry charges it again.
The common thread: correctness depends on application code being perfectly timed, instead of on guarantees the database can enforce.

Step-by-Step Solution to Prevent Duplicate Transactions
Step 1: Find and Clean Existing Duplicates
Before adding constraints, measure the damage. Group by the fields that define "the same transaction" in your business, for example customer, amount, reference and a short time window.
-- Find likely duplicate payments
SELECT customer_id, amount, reference, COUNT(*) AS copies,
MIN(created_at) AS first_seen, MAX(created_at) AS last_seen
FROM payments
GROUP BY customer_id, amount, reference
HAVING COUNT(*) > 1
ORDER BY copies DESC;Do not bulk delete duplicates in a financial module. Reverse them through your normal credit note or reversal process so the audit trail stays complete, then add constraints.
Step 2: Add a Unique Constraint on the Business Key
The database is the only place that can reliably stop two concurrent inserts. Give each transaction table a natural key, such as a gateway transaction ID, a bank reference or a document number per company, and enforce it with a unique index.
-- Build the index without blocking writes on a busy table
CREATE UNIQUE INDEX CONCURRENTLY payments_gateway_txn_uq
ON payments (company_id, gateway_txn_id)
WHERE gateway_txn_id IS NOT NULL;Now a second insert with the same key fails with ERROR: duplicate key value violates unique constraint "payments_gateway_txn_uq" (SQLSTATE 23505) instead of silently creating a copy.
Step 3: Use Idempotency Keys for API Requests
Database idempotency for APIs means the client sends a unique key with every operation, and the server stores the key with the result. A repeated request with the same key returns the stored result rather than running the operation again. This is the same pattern payment providers use with an Idempotency-Key header.
CREATE TABLE idempotency_keys (
key uuid PRIMARY KEY,
user_id bigint NOT NULL,
request_hash text NOT NULL,
status text NOT NULL DEFAULT 'processing',
response_code int,
response_body jsonb,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idempotency_keys_created_idx ON idempotency_keys (created_at);The client generates the key once per user action, for example when the payment form is opened, and reuses it for every retry. Combined with sensible exponential backoff for API retries, clients can retry safely.
Step 4: Claim the Key and Do the Work in One Transaction
Insert the key first with ON CONFLICT DO NOTHING. If the insert succeeds, this request owns the operation. If it does not, another request already handled it or is handling it. Here is a Laravel version:
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
public function store(Request $request)
{
$key = $request->header('Idempotency-Key');
abort_unless($key && Str::isUuid($key), 400, 'Missing Idempotency-Key');
$hash = hash('sha256', json_encode($request->all()));
return DB::transaction(function () use ($request, $key, $hash) {
$claimed = DB::table('idempotency_keys')->insertOrIgnore([
'key' => $key, 'user_id' => $request->user()->id,
'request_hash' => $hash, 'created_at' => now(),
]);
if ($claimed === 0) {
$row = DB::table('idempotency_keys')->where('key', $key)->lockForUpdate()->first();
abort_if($row->request_hash !== $hash, 422, 'Key reused with different payload');
abort_if($row->status === 'processing', 409, 'Request still processing');
return response()->json(json_decode($row->response_body), $row->response_code);
}
$payment = Payment::create($request->validated());
$body = ['id' => $payment->id, 'status' => 'posted'];
DB::table('idempotency_keys')->where('key', $key)->update([
'status' => 'done', 'response_code' => 201, 'response_body' => json_encode($body),
]);
return response()->json($body, 201);
});
}Because the key row and the payment are written in the same database transaction, either both commit or neither does. A retry after a crash finds no key and runs cleanly. For browser forms, combine this with the techniques in how to prevent duplicate form submissions in Laravel.
Step 5: Make Queue Jobs Idempotent
Assume every job can run twice. Give each job a stable business key and let the database decide whether the work is new.
// Inside the job's handle() method
DB::transaction(function () {
$inserted = DB::table('stock_movements')->insertOrIgnore([
'source_type' => 'goods_issue',
'source_id' => $this->goodsIssueId,
'item_id' => $this->itemId,
'qty' => -$this->qty,
'created_at' => now(),
]);
if ($inserted === 0) {
return; // already processed, safe to acknowledge
}
DB::table('stock_balances')
->where('item_id', $this->itemId)
->decrement('qty', $this->qty);
});-- Matching constraint so one goods issue line moves stock once
ALTER TABLE stock_movements
ADD CONSTRAINT stock_movements_source_uq UNIQUE (source_type, source_id, item_id);Laravel's ShouldBeUnique interface and WithoutOverlapping middleware help reduce concurrent runs, but they rely on cache locks that can expire. Treat them as an optimisation and keep the database constraint as the real guarantee.
Step 6: Handle Unique Violations Gracefully
When two requests race, one will hit the unique constraint. Catch that specific error and return the existing record instead of a 500 error.
use Illuminate\Database\UniqueConstraintViolationException;
try {
$payment = Payment::create($data);
} catch (UniqueConstraintViolationException $e) {
$payment = Payment::where('company_id', $data['company_id'])
->where('gateway_txn_id', $data['gateway_txn_id'])
->firstOrFail();
}UniqueConstraintViolationException is available in Laravel 10.20 and later. On older versions, catch QueryException and check that $e->getCode() equals '23505'.
Step 7: Protect External Side Effects
For calls to payment gateways or bank APIs, pass your own idempotency key or order reference to the provider if it supports one, and record the outgoing request before calling it. A transactional outbox table, processed by a worker after commit, keeps the local transaction and the external call from drifting apart.
Verification: Prove Duplicates Can No Longer Happen
- Send the same request twice in parallel with the same key and confirm only one row is created and both responses carry the same ID.
- Insert a duplicate business key directly in
psqland confirm you get SQLSTATE23505. - Kill a queue worker in the middle of a job, let the job retry, and confirm stock moved only once.
- Rerun the duplicate-finding query from Step 1 after a busy day and confirm it returns no new rows.
# Fire two identical requests at the same time
KEY=$(uuidgen)
for i in 1 2; do
curl -s -X POST https://erp.example.com/api/payments \
-H "Authorization: Bearer $TOKEN" \
-H "Idempotency-Key: $KEY" \
-H "Content-Type: application/json" \
-d '{"customer_id":42,"amount":1500,"gateway_txn_id":"TXN-9001"}' &
done; wait
psql -d erp -c "SELECT COUNT(*) FROM payments WHERE gateway_txn_id = 'TXN-9001';"The count should be 1. One response may be 201 and the other 409 or a replayed 201, depending on timing; both are correct.
Prevention: Keep Transaction Integrity by Design
- Define a business key for every new transaction table and add the unique constraint in the same migration.
- Require an
Idempotency-Keyheader on every API endpoint that creates money, stock or accounting entries. - Never use the check-then-insert pattern without a constraint behind it; prefer
INSERT ... ON CONFLICT. - Keep database writes and the idempotency record in one transaction, and move external calls to an outbox.
- Expire old idempotency keys with a scheduled command, for example after 24 to 72 hours, depending on how long clients may retry.
- Record who created each transaction and from which request in your audit log database design, so any duplicate that slips through can be traced quickly.
FAQs
What is database idempotency?
Database idempotency means an operation can be applied many times with the same result as applying it once. In practice you achieve it with unique business keys, ON CONFLICT inserts and stored idempotency keys that map a request to its first result.
Is disabling the submit button enough to prevent duplicate transactions?
No. It helps with double clicks, but it does nothing against network retries, proxy retries, API clients or queue redelivery. Server-side constraints are required.
Should I use Redis locks or database constraints?
Use database constraints as the guarantee. Redis locks are useful for reducing duplicate work, but a lock can expire while the work is still running, and it does not protect rows written by other code paths.
How long should idempotency keys be stored?
Keep them at least as long as any client might retry, plus a margin. Many teams use 24 hours for web and mobile clients and longer for batch integrations.
How do I prevent duplicate payments with a payment gateway?
Store the gateway transaction ID with a unique constraint, send your own order reference or idempotency key to the gateway when supported, and process webhooks idempotently because gateways may deliver the same webhook more than once.
Does SERIALIZABLE isolation solve duplicate transactions?
It can detect some conflicting concurrent transactions, but it adds retries and does not help when the same request arrives seconds apart in separate transactions. A unique constraint is simpler and stronger for this problem.




Comments
Join the discussion. Replies load on demand, up to 5 levels.
0 threads
No comments yet
Be the first to start the conversation.