Understanding Atomicity through a Simple Wallet Transfer System
Introduction
While using payment apps, we usually don’t think about what happens behind the scenes. When we send money, we simply expect that if it leaves our account, it should reach the other person. There should never be a situation where money is deducted but not received.
This behavior is handled by the database using something called Atomicity. It ensures that a transaction either completes fully or does not happen at all.
To understand this better, I tried building a simple wallet transfer system using PostgreSQL and tested different cases to see how the database behaves.
Setting up the table
I started by creating a basic table to store account details.
CREATE TABLE accounts (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
balance INT NOT NULL CHECK (balance >= 0),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Then I inserted two users:
INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);
Checking the table:
SELECT * FROM accounts ORDER BY id;
At this point:
- Alice has 1000
- Bob has 500
Trying a normal transfer
First, I wanted to see how a proper transfer works.
I made Alice send 200 to Bob inside a transaction.
BEGIN;
UPDATE accounts
SET balance = balance - 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 1;
UPDATE accounts
SET balance = balance + 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 2;
COMMIT;
After running this:
- Alice’s balance became 800
- Bob’s balance became 700
This is exactly what we expect. Both operations happened together.
Resetting the data
Before testing failures, I reset the balances.
UPDATE accounts SET balance = 1000 WHERE id = 1;
UPDATE accounts SET balance = 500 WHERE id = 2;
What if something breaks in the middle
Now comes the interesting part.
I started a transaction, deducted money from Alice, but intentionally made a mistake in the next query.
BEGIN;
UPDATE accounts
SET balance = balance - 200
WHERE id = 1;
UPDATE accounts
SET balanc = balance + 200
WHERE id = 2;
This gave an error because of the wrong column name.
Then I ran:
ROLLBACK;
After checking the table, I noticed:
- Alice still had 1000
- Bob still had 500
Even though the first update ran, it was not saved. The database completely ignored it because the transaction failed later.
Another failure after a valid query
Next, I tried a different approach.
BEGIN;
UPDATE accounts
SET balance = balance - 300
WHERE id = 1;
SELECT * FROM non_existing_table;
The second query failed because the table does not exist.
After rollback, the balances were still:
- Alice = 1000
- Bob = 500
Again, nothing changed.
Trying to break it using constraints
I then tried to transfer more money than Alice actually had.
BEGIN;
UPDATE accounts
SET balance = balance - 1500
WHERE id = 1;
UPDATE accounts
SET balance = balance + 1500
WHERE id = 2;
This failed because the balance cannot go below zero.
After rollback:
* Alice = 1000
* Bob = 500
No changes were made.
What happens without a transaction
To really understand why transactions matter, I tried the same thing without using BEGIN and COMMIT.
UPDATE accounts
SET balance = balance - 200
WHERE id = 1;
UPDATE accounts
SET balanc = balance + 200
WHERE id = 2;
The first query succeeded, but the second failed.
After checking:
* Alice had 800
* Bob still had 500
This is a partial update. Money was deducted but not credited.
What I learned
From all these tests, a few things became very clear:
* A transaction ensures that all steps are treated as a single unit
* If any step fails, the entire transaction is rolled back
* The database does not allow partial updates inside a transaction
* Without transactions, inconsistent data can occur very easily
This is why atomicity is critical for systems that handle money.
Conclusion
This simple experiment helped me understand how databases protect data integrity during operations like money transfers.
Atomicity ensures that either everything happens or nothing happens. In real-world applications, this is what prevents issues like money loss or incorrect balances.
Even though the example was simple, the concept is very powerful and is used in all reliable financial systems.
Top comments (0)