High-Concurrency Wallet Design: Hot Accounts, Idempotent Deductions, and Sharding Practices

Why Wallet Deduction Is Harder Than Inventory

Many developers mistakenly treat a wallet like an inventory system—deduct if balance exists, otherwise fail. This view ignores that inventory oversell is a fulfillment issue, while wallet overdraft directly causes monetary loss, audit problems, and customer complaints. Therefore the primary principles for wallet deduction are:

Never allow duplicate deductions.

Never let the balance become negative.

Never lose the accounting record after a successful response.

Never update only the balance without a transaction log.

Never leave a partially successful operation without a fallback.

A wallet is not just a "balance field updater"; it is a full accounting system built around accounts, transaction logs, state machines, idempotency, and compensation.

Real Incident: "Slow and Wrong" During a 60 k TPS Promotion

A payment platform peaked at 60 000 TPS. Each payment required three accounting actions: user balance deduction, merchant pending settlement, and platform fee credit. The observed symptoms were:

P99 latency of the payment API jumped from 80 ms to 7 s.

MySQL active threads exploded, exhausting the connection pool.

Platform fee account updates queued heavily.

Callback retries caused a few duplicate deductions.

Transaction‑log table latency triggered balance‑mismatch alerts.

The root causes were a chain of design flaws:

All three balances were updated in a single synchronous transaction.

A single hot row (platform fee or large merchant account) was updated at high frequency.

Idempotency was only enforced with Redis SETNX and lacked a DB unique constraint.

The transaction‑log table grew beyond a billion rows, causing severe index write amplification.

All money flows required real‑time posting without distinguishing "strong consistency" from "eventual aggregation".

These issues show that high‑concurrency wallet design is not solved by simply adding DB locks; the three different money actions must be separated:

User‑side deduction (must be strongly consistent).

Hot account credit (can be aggregated asynchronously).

Audit transaction log (must be complete, traceable, and replayable).

Business Model Definition

Account

account_id

: unique account identifier. owner_id: user or merchant ID. account_type: USER, MERCHANT, PLATFORM. available_balance and frozen_balance. status: NORMAL, LOCKED, CLOSED. version: optimistic‑lock version.

AccountFlow (Transaction Log)

Each business action generates one or more flow records such as user payment, merchant receipt, platform fee, or compensation.

LedgerCommand

Business systems should submit commands like PAY_DEDUCT, TRANSFER_OUT, TRANSFER_IN, FREEZE, UNFREEZE, FEE_COLLECT instead of directly updating balances.

BizOrder

All idempotent and reconciliation designs revolve around the original business order number (e.g., pay_order_no, withdraw_order_no, transfer_order_no).

Four Core Contradictions in High‑Concurrency Wallets

High concurrency vs. no overspend.

Prevent duplicates vs. maintain throughput.

Sharding vs. keeping accounting closed‑loop.

Hot‑account throttling vs. final balance accuracy.

Overall Architecture: Split Strong‑Consistent and Throttling Paths

A production‑grade wallet should adopt a "dual‑path" architecture:

Path A: Strong‑consistent user balance deduction.

Path B: Asynchronous hot‑account credit aggregation.

Key points of the architecture:

User deduction must happen inside a local DB transaction.

Hot‑account credit must not be locked together with the user deduction.

Within the transaction, ensure:

Merchant and platform fee credits are processed via MQ, then reconciled.

Table Design for Account, Flow, and Idempotent Request

CREATE TABLE wallet_account (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT NOT NULL,
    owner_id BIGINT NOT NULL,
    account_type VARCHAR(32) NOT NULL,
    available_balance DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    frozen_balance DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    version INT NOT NULL DEFAULT 0,
    status TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_account_id (account_id),
    KEY idx_owner_type (owner_id, account_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Design highlights:

Separate available_balance and frozen_balance instead of a single balance field.

Use account_id as a globally unique key, not the user ID.

Optimistic‑lock version for low‑conflict updates.

Prefer conditional updates over pure optimistic‑lock spin for high‑frequency deductions.

CREATE TABLE account_flow (
    id BIGINT NOT NULL PRIMARY KEY,
    flow_no VARCHAR(64) NOT NULL,
    account_id BIGINT NOT NULL,
    owner_id BIGINT NOT NULL,
    biz_order_no VARCHAR(64) NOT NULL,
    biz_type VARCHAR(32) NOT NULL,
    direction VARCHAR(16) NOT NULL,
    amount DECIMAL(20,2) NOT NULL,
    before_available DECIMAL(20,2) NOT NULL,
    after_available DECIMAL(20,2) NOT NULL,
    before_frozen DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    after_frozen DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    request_id VARCHAR(64) NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'SUCCESS',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_flow_no (flow_no),
    UNIQUE KEY uk_account_biz (account_id, biz_order_no, biz_type),
    KEY idx_owner_time (owner_id, created_at),
    KEY idx_biz_order (biz_order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The unique constraints uk_flow_no and uk_account_biz guarantee that a flow number is globally unique and that the same account cannot record the same business order twice, eliminating many duplicate‑deduction incidents.

CREATE TABLE wallet_idempotent_request (
    id BIGINT NOT NULL PRIMARY KEY,
    request_id VARCHAR(64) NOT NULL,
    biz_order_no VARCHAR(64) NOT NULL,
    account_id BIGINT NOT NULL,
    biz_type VARCHAR(32) NOT NULL,
    status VARCHAR(16) NOT NULL,
    result_code VARCHAR(32) DEFAULT NULL,
    result_msg VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_request_id (request_id),
    UNIQUE KEY uk_account_biz (account_id, biz_order_no, biz_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Redis SETNX can be used for fast pre‑check, but the DB unique key provides the final idempotent decision.

Why Redis‑Only Idempotency Is Insufficient

Redis keys may be lost on expiration, eviction, or master‑slave failover.

If Redis succeeds but the DB transaction fails, a "blocked retry but not really recorded" state appears.

The authoritative audit trail always resides in the DB.

Cache alone cannot answer "Did the money really get deducted?".

Standard practice:

Redis for fast de‑duplication and traffic reduction.

DB unique index for the final idempotent verdict.

Transaction log as the immutable proof.

Funds idempotency can be accelerated with Redis, but correctness must not rely on Redis alone.

Production‑Grade Deduction Transaction

Transaction Boundary

A user‑balance deduction transaction must contain four steps:

Persist idempotent request or insert a flow placeholder.

Validate and deduct the available balance.

Write the definitive flow record.

Insert an Outbox event for asynchronous downstream processing.

This guarantees that "money is deducted, and the event can continue; if the event fails to send, compensation can still push it forward".

Core Java Example

@Service
@RequiredArgsConstructor
public class WalletDebitService {
    private final WalletAccountMapper walletAccountMapper;
    private final AccountFlowMapper accountFlowMapper;
    private final IdempotentRequestMapper idempotentRequestMapper;
    private final OutboxEventMapper outboxEventMapper;

    @Transactional(rollbackFor = Exception.class)
    public DebitResult deduct(DebitCommand command) {
        IdempotentRequestEntity request = buildRequest(command);
        try {
            idempotentRequestMapper.insert(request);
        } catch (DuplicateKeyException ex) {
            IdempotentRequestEntity existing =
                idempotentRequestMapper.selectByRequestId(command.getRequestId());
            return DebitResult.from(existing.getResultCode(), existing.getResultMsg());
        }

        WalletAccount account = walletAccountMapper.selectByAccountIdForUpdate(command.getAccountId());
        if (account == null || account.getStatus() != 1) {
            throw new IllegalStateException("account unavailable");
        }
        if (account.getAvailableBalance().compareTo(command.getAmount()) < 0) {
            idempotentRequestMapper.markFailed(request.getId(), "INSUFFICIENT_BALANCE", "余额不足");
            throw new InsufficientBalanceException("余额不足");
        }

        BigDecimal beforeAvailable = account.getAvailableBalance();
        BigDecimal afterAvailable = beforeAvailable.subtract(command.getAmount());
        int updated = walletAccountMapper.deductAvailableBalance(
            command.getAccountId(), command.getAmount(), beforeAvailable);
        if (updated == 0) {
            throw new ConcurrentUpdateException("account updated concurrently");
        }

        AccountFlowEntity flow = AccountFlowEntity.builder()
            .id(IdGenerator.nextId())
            .flowNo(IdGenerator.nextFlowNo())
            .accountId(command.getAccountId())
            .ownerId(command.getOwnerId())
            .bizOrderNo(command.getBizOrderNo())
            .bizType(command.getBizType())
            .direction("DEBIT")
            .amount(command.getAmount())
            .beforeAvailable(beforeAvailable)
            .afterAvailable(afterAvailable)
            .beforeFrozen(account.getFrozenBalance())
            .afterFrozen(account.getFrozenBalance())
            .requestId(command.getRequestId())
            .status("SUCCESS")
            .build();
        accountFlowMapper.insert(flow);

        OutboxEventEntity event = OutboxEventEntity.builder()
            .id(IdGenerator.nextId())
            .eventNo(IdGenerator.nextEventNo())
            .topic("wallet.account.debited")
            .bizKey(command.getBizOrderNo())
            .payload(Jsons.toJson(new DebitSucceededEvent(command, flow.getFlowNo())))
            .status("PENDING")
            .nextRetryTime(LocalDateTime.now())
            .build();
        outboxEventMapper.insert(event);

        idempotentRequestMapper.markSuccess(request.getId(), "SUCCESS", "扣减成功");
        return DebitResult.success(flow.getFlowNo(), afterAvailable);
    }
}

FOR UPDATE vs. Optimistic Lock

Practical experience:

Low‑conflict accounts can use optimistic lock or conditional updates.

Critical balance deductions should prefer conditional update or pessimistic lock to guarantee atomicity.

Hot accounts should never be locked together with user deductions; instead, remodel the data.

Hot‑Account Solutions

Solution 1: Shadow (Split) Accounts

Split a merchant’s main account into many shadow accounts (e.g., M10000_01 … M10000_64) and hash the order number to a specific shadow. Queries aggregate the 64 shards. Advantages: retains DB accounting model, spreads row hot‑spot, moderate migration cost. Drawbacks: balance queries need aggregation, settlement/withdrawal must consolidate shards, reconciliation must map back to the main account.

Solution 2: Redis Counter + Batch DB Write

After a successful deduction, emit an event; a consumer aggregates merchant and platform fee amounts in Redis; when thresholds or time windows are reached, batch‑write back to the DB. Pros: strong peak‑shaving, reduces hot‑row writes. Cons: not real‑time strong consistency, requires compensation, batch jobs, and robust retry mechanisms.

Solution 3: Time‑Window Aggregation

For platform fees, aggregate credits every second or few seconds into a single flow record, drastically lowering write frequency.

Solution 4: Rate‑limit, Isolation, Degradation

Apply per‑merchant rate limits, isolate hot‑account queues, and allow delayed credit visibility while never allowing duplicate or missing deductions.

Sharding Practices (ShardingSphere Example)

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2,ds3
    rules:
      sharding:
        tables:
          wallet_account:
            actual-data-nodes: ds$->{0..3}.wallet_account_$->{0..15}
            database-strategy:
              standard:
                sharding-column: account_id
                sharding-algorithm-name: account-db-inline
            table-strategy:
              standard:
                sharding-column: account_id
                sharding-algorithm-name: account-table-inline
          account_flow:
            actual-data-nodes: ds$->{0..3}.account_flow_$->{0..31}
            database-strategy:
              standard:
                sharding-column: account_id
                sharding-algorithm-name: flow-db-inline
            table-strategy:
              standard:
                sharding-column: account_id
                sharding-algorithm-name: flow-table-inline
        sharding-algorithms:
          account-db-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{account_id % 4}
          account-table-inline:
            type: INLINE
            props:
              algorithm-expression: wallet_account_$->{account_id % 16}
          flow-db-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{account_id % 4}
          flow-table-inline:
            type: INLINE
            props:
              algorithm-expression: account_flow_$->{account_id % 32}

Key points:

Account and flow tables are sharded by account_id to keep a user's deduction, its flow, and its idempotent request on the same shard.

Avoid cross‑shard transactions for a single deduction.

Do not shard configuration, rule, or small audit tables initially.

Common Sharding Pitfalls

Queries without the sharding key cause full‑cluster routing.

Unique indexes defined only per‑shard lose global uniqueness.

Large‑range pagination on flow tables becomes extremely slow.

Cross‑account transfers need two shards, dramatically increasing transaction complexity.

Mitigations: always include the sharding key in core write paths, limit queries by account or time, and offload reporting to offline warehouses.

Cross‑Account Transfer Design

Do NOT use XA/2PC for high‑throughput transfers; the performance penalty and lock duration are prohibitive.

Recommended pattern:

Create a transfer order with status INIT.

Deduct the source account and write a debit flow.

Write a pending credit event to Outbox.

Consumer processes the event, credits the destination account, and writes a credit flow.

Update transfer order status to SUCCESS or PENDING_COMPENSATE for retry.

This ensures traceability, retryability, and eventual consistency.

Outbox Pattern for Reliable Messaging

CREATE TABLE wallet_outbox_event (
    id BIGINT NOT NULL PRIMARY KEY,
    event_no VARCHAR(64) NOT NULL,
    topic VARCHAR(64) NOT NULL,
    biz_key VARCHAR(64) NOT NULL,
    payload JSON NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'PENDING',
    retry_count INT NOT NULL DEFAULT 0,
    next_retry_time DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_event_no (event_no),
    KEY idx_status_retry (status, next_retry_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Sending task scans PENDING / RETRY rows, publishes to MQ, marks SENT on success, increments retry count and schedules next retry on failure, and finally marks DEAD after exceeding thresholds.

Consumers must still be idempotent using business order unique constraints or a de‑duplication table.

Monitoring Focused on Financial Correctness

Deduction success rate.

Duplicate‑request hit rate.

Insufficient‑balance ratio.

Flow write failure count.

Outbox backlog size.

Hot‑account aggregation latency.

Reconciliation error count.

Pending compensation order count.

MQ retry attempts.

Slow SQL count from sharding.

Critical alerts include missing flow after a successful deduction, balance not updated after flow, Outbox exceeding thresholds, long‑pending merchant credit, and platform‑fee aggregation SLA breaches.

Practical Evolution Roadmap

Phase 1 – Correctness First : Build account, flow, and idempotent tables with unique constraints; enforce all deductions inside local transactions; introduce Outbox; set up basic reconciliation and compensation.

Phase 2 – Identify Hotspots : Detect platform and large‑merchant accounts; split their credit path from the synchronous deduction; apply shadow accounts or async aggregation.

Phase 3 – Capacity Upgrade : Shard account and flow tables; archive massive flow data; implement merchant‑level traffic shaping; perform joint load tests on DB, Redis, and MQ.

Phase 4 – Engineering Closure : Auto‑generate error tickets; implement automatic compensation; provide visual reconciliation dashboards; add audit‑trace and replay capabilities.

Conclusion

The essence of high‑concurrency wallet design is not merely faster SQL but guaranteeing that each cent is deducted exactly once, never over‑deducted, and eventually credited correctly. This is achieved by separating strong‑consistent user deductions from asynchronous hot‑account aggregation, enforcing unique DB constraints, employing outbox messaging, and building a full reconciliation‑compensation loop.