Atomicity

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

BEGIN;

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

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

COMMIT;

Both debit and credit are grouped inside one transaction so they act as a single unit. This ensures money is not lost between steps.

---

Check balances before and after successful transaction

SELECT * FROM accounts;

First check initial state to know starting balances. After commit, verify both accounts reflect the correct updated values.

---

Introduce failure after debit (simulate error before credit)

BEGIN;

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

-- simulate failure
SELECT 1/0;

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

COMMIT;

An error is triggered after deducting money. This helps check whether the system allows partial updates or not.

---

Observe rollback behavior after failure

SELECT * FROM accounts;

After failure, the transaction should not commit. The deducted amount should not persist if rollback works correctly.

---

Explicit rollback after error

BEGIN;

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

ROLLBACK;

Rollback cancels everything inside the transaction. Even completed queries inside the block are undone.

---

Simulate failure in credit step

BEGIN;

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

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

COMMIT;

Second query fails logically. This checks whether system prevents inconsistent state.

---

Verify no partial update after failed credit

SELECT * FROM accounts;

Balances should remain unchanged if transaction fails. Sender’s balance should not be reduced alone.

---

Safe version with validation

BEGIN;

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

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

COMMIT;

Added condition to avoid negative balance. Helps ensure business rule is enforced before transfer completes.

---

Final verification of consistency

SELECT * FROM accounts;

Final check confirms either both updates happened or none. Ensures system maintains atomicity and consistency.