Mitigating Transaction Race Conditions in High-Volume KYC Systems

In financial applications like CoinsPe, multiple services might write to the same account record simultaneously (e.g., automated KYC updates, manual admin overrides, and webhook events). Without database isolation, you face state corruption.

The Challenge

If an admin approves an account while the automatic background check is executing a write, they might overwrite each other's changes.

The Solution: Optimistic Locking with Postgres

We implemented optimistic locking using a version indicator in the table schema:

CREATE TABLE user_kyc (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    status VARCHAR(50),
    version INT DEFAULT 1
);

When executing updates:

const result = await db.query(
  "UPDATE user_kyc SET status = $1, version = version + 1 WHERE id = $2 AND version = $3",
  [newStatus, kycId, currentVersion]
);

if (result.rowCount === 0) {
  throw new Error("Concurrency Conflict: Record was modified by another request.");
}

When to use Pessimistic Locks

For crypto asset balances where deductions must be immediate and atomic, we select for updates explicitly:

BEGIN;
SELECT balance FROM user_wallets WHERE id = 12 FOR UPDATE;
-- Execute transaction logic...
UPDATE user_wallets SET balance = balance - 100 WHERE id = 12;
COMMIT;

This locks the row from reads/writes of other transactions until the commit is resolved.