Preventing Double-Crediting in Paystack Webhooks: Postgres Advisory Locks vs. Redis Redlock

It is Friday at 7:45 PM. Paystack experiences a transient upstream timeout while delivering payment notifications for a merchant processing weekend transfers. Within 300 milliseconds, two duplicate charge.success HTTP POST requests hit your application cluster behind an Application Load Balancer. Node A receives Webhook #1; Node B receives Webhook #2. Both carry the exact same transaction reference: tx_ref_8f93a102b4.

Both application nodes initiate database queries simultaneously. Both check the wallets table, read the balance as ₦10,000, verify that the transaction reference is not yet present in payment_events, calculate the updated balance (₦10,000 + ₦50,000 = ₦60,000), insert a ledger row, and issue a SQL commit.

The customer gets credited ₦100,000 instead of ₦50,000. By Saturday morning, your ledger reconciliation worker flags a ₦4.5 million balance divergence across 312 user accounts. This scenario is not theoretical; it is the default failure mode of naively designed payment webhooks under high concurrency.

To handle provider retry surges on providers like Paystack Dedicated Virtual Accounts vs. Monnify vs. Squadco: Evaluating NUBAN Infrastructure for High-Volume Nigerian Fintechs, engineers typically rely on Redis locks or database unique constraints. However, both approaches exhibit subtle, catastrophic failure modes in production.

Here is an evaluation of why traditional deduplication fails, followed by a concrete, runnable implementation of PostgreSQL transactional advisory locks in TypeScript.

Why Standard Idempotency Patterns Fail Under Race Conditions

Failure Mode 1: Unique Constraints on Event Tables

Many engineering teams rely on an events table with a UNIQUE index on the provider reference string:

CREATE TABLE payment_events (
    id BIGSERIAL PRIMARY KEY,
    reference VARCHAR(64) NOT NULL UNIQUE,
    amount_kobo BIGINT NOT NULL,
    processed_at TIMESTAMPTZ DEFAULT NOW()
);

If your service logic attempts to write to the ledger first and then insert into payment_events, Node A and Node B both complete the ledger update before either hits the UNIQUE index check. One node fails its event insertion and throws an unhandled exception, but the money has already been moved in the wallet table.

If you reverse the order—inserting into payment_events before updating the ledger—you solve the double-credit, but you introduce partial failure deadlocks. If Node A inserts the event record, but crashes due to a database connection reset right before updating the wallet, Webhook #2 (Node B) will inspect payment_events, see the reference, assume it was successfully processed, and drop the webhook. The customer is never credited.

Failure Mode 2: SELECT FOR UPDATE on Missing Rows

Executing SELECT * FROM payment_events WHERE reference = 'tx_ref_8f93a102b4' FOR UPDATE only locks rows that already exist in the database. When the first pair of duplicate webhooks hit your API, neither query finds an existing row. SELECT FOR UPDATE returns 0 rows, acquires zero locks, and lets both execution paths proceed to modify the wallet.

Failure Mode 3: Redis Distributed Locks (Redlock)

Distributed locks built on Redis (or Redlock) seem ideal until you account for real-world Node.js runtime anomalies:

1. Node A acquires a Redis lock on key lock:tx_ref_8f93a102b4 with a 3-second Time-To-Live (TTL).
2. Node A experiences a 3.5-second Stop-The-World V8 Garbage Collection pause or connection pool queuing delay.
3. The Redis lock TTL expires.
4. Node B acquires the lock on lock:tx_ref_8f93a102b4 and begins updating the wallet.
5. Node A resumes execution, believing it still owns the lock, and updates the wallet a second time.

Redis operates outside the database transaction boundary. If your application process dies mid-transaction, Redis remains locked until the TTL expires, or worse, releases early while a long-running Postgres write is still pending.

The Architecture: PostgreSQL Transactional Advisory Locks

PostgreSQL Advisory Locks allow applications to lock arbitrary integer keys. Crucially, Postgres supports transaction-level advisory locks (pg_advisory_xact_lock), which tie the lock lifetime directly to the active SQL transaction.

Key properties of pg_advisory_xact_lock:

• Automatic Release: The lock releases automatically when the transaction commits or rolls back, whether cleanly or due to a dropped socket.
• Zero TTL Management: You do not guess timeout durations. The lock remains active exactly as long as the ACID transaction lives.
• Application-Defined Keys: You lock on a hash of your business key (e.g., Paystack reference string), not a database row ID.
• Blocking Semantics: If Node B requests a lock held by Node A, Node B blocks cleanly in Postgres until Node A completes its transaction.

| Lock Mechanism | Transaction Scope | Garbage Collection Resilient | No Phantom Lock Leaks | Works on Non-Existent Records | | :--- | :--- | :--- | :--- | :--- | | SELECT FOR UPDATE | Yes | Yes | Yes | No | | Unique Index Constraint | Yes | Yes | Yes | No | | Redis (Redlock) | No | No | No | Yes | | pg_advisory_xact_lock | Yes | Yes | Yes | Yes |

Step-by-Step Implementation in TypeScript

Postgres advisory locks accept either a single 64-bit signed integer or two 32-bit signed integers. Because payment provider references are alphanumeric strings (e.g., tx_ref_8f93a102b4), we hash the reference string using SHA-256 and convert the first 8 bytes into a 64-bit BigInt.

Here is a complete, production-ready implementation using the Node.js pg driver.

import crypto from 'crypto';
import { PoolClient } from 'pg';

/**
 * Hashes an arbitrary string reference into a 64-bit signed integer string
 * suitable for Postgres advisory lock functions.
 */
export function hashReferenceToBigInt(reference: string): string {
  const hash = crypto.createHash('sha256').update(reference).digest();
  // Read the first 8 bytes as a signed 64-bit big-endian integer
  const bigIntValue = hash.readBigInt64BE(0);
  return bigIntValue.toString();
}

interface ProcessWebhookInput {
  reference: string;
  walletId: string;
  amountKobo: bigint;
}

interface ProcessWebhookResult {
  status: 'PROCESSED' | 'ALREADY_EXISTS';
  newBalanceKobo?: bigint;
}

export async function processPaymentWebhook(
  client: PoolClient,
  input: ProcessWebhookInput
): Promise<ProcessWebhookResult> {
  const lockKey = hashReferenceToBigInt(input.reference);

  try {
    await client.query('BEGIN');

    // Acquire an exclusive transaction-level advisory lock.
    // If another transaction holds this key, this call blocks until the
    // holding transaction commits or rolls back.
    await client.query('SELECT pg_advisory_xact_lock($1::bigint)', [lockKey]);

    // Step 1: Check if the transaction reference has already been committed
    const existingEvent = await client.query(
      'SELECT id FROM transaction_events WHERE reference = $1 LIMIT 1',
      [input.reference]
    );

    if (existingEvent.rowCount && existingEvent.rowCount > 0) {
      await client.query('COMMIT');
      return { status: 'ALREADY_EXISTS' };
    }

    // Step 2: Fetch current balance using row lock for safe arithmetic
    const walletResult = await client.query(
      'SELECT balance_kobo FROM wallets WHERE id = $1 FOR UPDATE',
      [input.walletId]
    );

    if (walletResult.rowCount === 0) {
      throw new Error(`Target wallet not found: ${input.walletId}`);
    }

    const currentBalance = BigInt(walletResult.rows[0].balance_kobo);
    const nextBalance = currentBalance + input.amountKobo;

    // Step 3: Update the wallet balance
    await client.query(
      'UPDATE wallets SET balance_kobo = $1, updated_at = NOW() WHERE id = $2',
      [nextBalance.toString(), input.walletId]
    );

    // Step 4: Record the transaction event
    await client.query(
      `INSERT INTO transaction_events 
        (reference, wallet_id, amount_kobo, status, created_at) 
       VALUES ($1, $2, $3, 'SUCCESS', NOW())`,
      [input.reference, input.walletId, input.amountKobo.toString()]
    );

    await client.query('COMMIT');
    return { status: 'PROCESSED', newBalanceKobo: nextBalance };
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  }
}

Execution Flow

1. Webhook #1 (Node A) and Webhook #2 (Node B) enter the function simultaneously.
2. Both compute identical lock keys from hashReferenceToBigInt('tx_ref_8f93a102b4').
3. Node A executes pg_advisory_xact_lock first. Postgres grants the lock to Node A.
4. Node B executes pg_advisory_xact_lock milliseconds later. Postgres pauses Node B's query execution, waiting for Node A's transaction to end.
5. Node A completes Step 1 through Step 4, writes the transaction event row, and executes COMMIT.
6. Postgres completes Node A's transaction, releases the advisory lock automatically, and unblocks Node B.
7. Node B's pg_advisory_xact_lock call returns. Node B proceeds to Step 1 (SELECT FROM transaction_events).
8. Node B finds the record inserted by Node A, skips wallet modifications, commits cleanly, and returns { status: 'ALREADY_EXISTS' }.

Common Pitfalls and Edge Cases

1. Hash Collisions with 32-bit hashtext

PostgreSQL includes a built-in string hashing function, hashtext('string'). Do not use hashtext() for advisory locks in production payment systems.

hashtext() returns a 32-bit signed integer. By the Birthday Paradox, a 32-bit hash space yields a 50% probability of a collision after approximately 77,000 unique keys. If two unrelated live transactions hash to the same 32-bit integer, Webhook B will block waiting for unrelated Webhook A to complete, causing artificial query queuing under high load.

Using SHA-256 chopped down to a 64-bit BigInt reduces the 50% collision probability threshold to 5.1 billion concurrent keys, eliminating false lock contention.

2. Using pg_advisory_lock Instead of pg_advisory_xact_lock

Postgres exposes two advisory lock variants:

• pg_advisory_lock(key): Session-level. Stays locked until explicitly released via pg_advisory_unlock(key) or until the database client disconnects.
• pg_advisory_xact_lock(key): Transaction-level. Automatically released at the end of the current SQL transaction.

If you use pg_advisory_lock and your process crashes, or your connection pool manager catches an exception and reuses the underlying connection without calling pg_advisory_unlock, that reference key remains locked indefinitely. Always use the xact variant for financial transactions.

3. Connection Pool Exhaustion

Because blocked transactions hold open database connections while waiting for lock releases, high-concurrency spikes can exhaust your application's database pool.

To prevent worker pool starvation:

1. Set explicit statement timeouts on your PostgreSQL session (SET statement_timeout = '3000ms').
2. Configure client connection pool sizes dynamically based on CPU core count rather than arbitrarily large connection limits.
3. Implement proper HTTP timeouts at your reverse proxy layer to gracefully respond to Paystack when processing backpressure surges, as detailed in our guide on Go HTTP Client Socket Leaks Under NIP Transfer Spikes: Diagnosing and Tuning High-Concurrency Switches.

Frequently Asked Questions

What if two different references hash to the same BigInt?

If a 64-bit hash collision occurs, the system will not corrupt financial data or credit incorrectly. It will simply force the second, unrelated transaction to wait a few milliseconds until the first transaction completes. Once unlocked, the second transaction checks its own reference string against transaction_events, finds no matching record, and executes normally.

Can I use try_pg_advisory_xact_lock instead of blocking?

Yes. PostgreSQL provides pg_try_advisory_xact_lock($1), which returns false immediately if the lock is held rather than blocking. If it returns false, your webhook worker can return an HTTP 429 or HTTP 503 response to Paystack. Paystack's automated engine will back off and retry the notification later according to the Paystack Webhooks Documentation. However, blocking for a few milliseconds with pg_advisory_xact_lock generally yields lower overall latency than relying on external webhook HTTP retries.

Does this scale if my database uses PgBouncer?

Yes, provided PgBouncer operates in Transaction Pooling mode or Session Pooling mode. Transaction-level advisory locks (pg_advisory_xact_lock) are bound to the underlying SQL transaction, not the TCP connection session. Statement-level pooling in PgBouncer is incompatible with multi-statement explicit BEGIN...COMMIT blocks altogether, so payment engines should always run on transaction-pooled connection endpoints.

How does this affect PostgreSQL CPU and memory consumption?

PostgreSQL maintains advisory locks in memory within its shared memory pool (max_locks_per_transaction). Because pg_advisory_xact_lock instances are ephemeral and vanish instantly upon transaction commit, memory utilization remains negligible even at sustained rates of several thousand webhooks per second.