In this task, I implemented a simple wallet transfer system to understand the Atomicity property of ACID. Atomicity means that a transaction should either complete fully or not happen at all. There should be no partial updates.
First, I created a table called accounts to store user details and balance.
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 some sample data.
INSERT INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);
To verify the initial state, I checked the table.
SELECT * FROM accounts;
At this point, Alice had 1000 and Bob had 500.
Next, I performed a successful money transfer of 200 from Alice to Bob using a transaction block.
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 executing this, I checked the table again.
SELECT * FROM accounts;
Now, Alice’s balance became 800 and Bob’s balance became 700. This shows that both debit and credit operations were completed successfully.
To test Atomicity, I reset the balances.
UPDATE accounts SET balance = 1000 WHERE id = 1;
UPDATE accounts SET balance = 500 WHERE id = 2;
Then I introduced an error during the transaction.
BEGIN;
UPDATE accounts
SET balance = balance - 200,
last_updated = CURRENT_TIMESTAMP
WHERE id = 1;
-- Intentional error
UPDATE accounts
SET balanc = balance + 200
WHERE id = 2;
COMMIT;
The second query fails because the column name is wrong. So the transaction is not completed.
Now I checked the table again.
SELECT * FROM accounts;
I observed that Alice’s balance was still 1000 and Bob’s balance was still 500. Even though the first update was executed, it was not saved.
This clearly shows that PostgreSQL does not allow partial updates. If any step fails, the entire transaction is rolled back.
Top comments (0)