Idempotency Situation

#systemdesign #softwareengineering #backend #database

Ensuring Reliable Money Transfers Using Database Transactions

In a digital wallet system similar to PhonePe, Google Pay, or Paytm, users expect their money to be handled accurately and securely. Even a small inconsistency—such as deducting money from one account without crediting another—can lead to serious financial issues. To prevent such problems, database systems rely on ACID properties, especially Durability, to guarantee safe and consistent transactions.

System Overview

The system maintains an accounts table where each user has a balance. Users can:

Store money in their wallet
Transfer money to other users
View their transaction history

To ensure correctness, the database enforces rules like non-negative balances and timestamp tracking.

Performing a Secure Transfer

A typical money transfer between two users (for example, Alice to Bob) is executed within a transaction:

The system begins a transaction
Deducts money from the sender’s account
Adds the same amount to the receiver’s account
Commits the transaction

If all steps succeed, the transaction is permanently saved. After committing, querying the database shows the updated balances correctly.

What Happens During a System Failure?

Failure Before Commit

If the system crashes before the transaction is committed, none of the changes are saved. The database automatically rolls back the transaction, ensuring that no partial updates occur. This prevents situations like money being deducted without being credited.

Failure After Commit

If the system crashes immediately after the commit, the changes remain persisted. When the database restarts, the updated balances remain intact. This behavior demonstrates Durability, meaning once a transaction is committed, it will not be lost.

How the Database Ensures Durability

Modern databases like PostgreSQL use a mechanism called Write-Ahead Logging (WAL). Before applying any changes to the actual data, the database records them in a log file. In case of a crash, the system replays this log to restore the latest committed state. This guarantees that committed transactions survive unexpected failures.

Without proper transaction handling:

Users could lose money
Duplicate transactions might occur
Account balances could become incorrect

By enforcing ACID properties:

Transactions are either fully completed or not applied at all
Data remains consistent at all times
Concurrent operations do not interfere with each other
Committed data is permanently stored

Handling Idempotency in Money Transfers

In real-world payment systems, a single transaction request may be sent multiple times due to network retries, timeouts, or client-side errors. If not handled properly, this can result in duplicate money transfers, leading to incorrect balances and financial inconsistencies.

Simulating Duplicate Transactions

Consider a scenario where a transfer of ₹200 from Alice to Bob is executed more than once:

First execution: Alice → 800, Bob → 700
Second execution (duplicate): Alice → 600, Bob → 900

This shows that the same operation is applied repeatedly, causing unintended deductions and credits.

Problem Observation

The database processes each request independently. Without additional safeguards, it does not recognize whether a transaction has already been executed. As a result, duplicate requests lead to repeated updates.
Why This Is Dangerous

Users may be charged multiple times
Account balances become inaccurate
Trust in the system is reduced

How Real Systems Prevent This

To avoid duplicate processing, modern systems implement idempotency.

Unique Transaction IDs

Each transaction is assigned a unique identifier. Before processing, the system checks if the ID already exists. If it does, the request is ignored.

Idempotency Keys

Clients send a unique key with each request. The server stores this key and ensures that repeated requests with the same key produce the same result without reprocessing.