Atomicity

#beginners #database #sql #tutorial

Design a transaction to transfer money from one account to another (atomic operation)

BEGIN;

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

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

COMMIT;

Both updates are wrapped inside one block so they either succeed together or not at all. This avoids any mismatch between sender and receiver balances.

Check balances before and after successful transaction

SELECT * FROM accounts;

Look at the balances before running the transfer to know the starting point. After commit, confirm both accounts changed correctly.

Introduce failure after debit (simulate error before credit)

BEGIN;

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

-- forced error
SELECT 10/0;

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

COMMIT;

An intentional error is placed after deducting money. This is to see if the system allows the first update to stay or cancels everything.

Observe rollback behavior after failure

SELECT * FROM accounts;

After the error, check balances again. If rollback works, Alice’s balance should remain unchanged.

Explicit rollback after partial step

BEGIN;

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

ROLLBACK;

Rollback is used to undo any changes made in the transaction. Even though one update ran, it should not persist.

Simulate failure in credit step

BEGIN;

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

UPDATE accounts 
SET balance = balance + 150 
WHERE name = 'Jarvish John';

COMMIT;

The second update targets a non-existing user. This checks if the system prevents incomplete transfers.

Verify no partial update after failed credit

SELECT * FROM accounts;

Balances should be exactly as before the transaction. Sender’s money should not be reduced alone.

Safer version with condition check

BEGIN;

UPDATE accounts 
SET balance = balance - 150 
WHERE name = 'Alice' AND balance >= 150;

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

COMMIT;