Atomicity - Design a Reliable Wallet Transfer System with ACID Guarantees

Atomicity in Action: Designing a Reliable Wallet Transfer System with ACID Guarantees

Building a wallet system like PhonePe, GPay, or Paytm requires strict data consistency. Even a small error can lead to money loss, duplicate transactions, or incorrect balances. This is why ACID properties, especially Atomicity, are critical in such systems.

Atomicity ensures that all operations in a transaction are completed successfully. If any step fails, the entire transaction is rolled back, and the database remains unchanged.

CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance INT NOT NULL CHECK (balance >= 0),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert sample data:

INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);

Initial state of the table:

ID	Name	Balance
1	Alice	1000
2	Bob	500

Successful Transaction

Transfer 200 from Alice to Bob using a transaction:

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

After execution:

Name	Balance
Alice	800
Bob	700

Both operations succeed, and the transaction is committed. The data remains consistent.

Failure After Debit Operation

Now introduce an error after deducting money from Alice:

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

UPDATE accounts
SET balanc = balance + 200
WHERE name = 'Bob';
COMMIT;

The second query fails due to an invalid column name. The database aborts the transaction.

Final state:

Name	Balance
Alice	1000
Bob	500

The deduction from Alice is rolled back. No partial update occurs.

UPDATE accounts
SET balance = balance - 300
WHERE name = 'Alice';

ROLLBACK;

Final state remains unchanged:

Name	Balance
Alice	1000
Bob	500

The rollback cancels all changes made during the transaction.

Without Transaction

If the same operations are executed without a transaction:

UPDATE accounts
SET balance = balance - 200
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 200
WHERE name = 'Bob';

Alice’s balance is reduced, but Bob does not receive the money. This leads to inconsistency.

Transactions ensure all operations are executed completely or not at all.
Errors automatically trigger rollback.
Partial updates are prevented.
Data integrity is maintained.
Transactions are essential for financial systems.

Atomicity guarantees that a transaction is treated as a single unit of work. In a wallet system, this ensures that money is neither lost nor duplicated. By using transaction blocks, the system remains consistent even in the presence of failures.