Atomicity

** 1: Initial Setup**

We already have the accounts table:

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 dummy data:

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

** 2: Check Initial State**

SELECT * FROM accounts;

Output:

Alice is 1000
Bob is 500

This is our starting point.

** 3: Correct Money Transfer**

We transfer 200 from Alice to Bob.

BEGIN;

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

COMMIT;

Explanation:

BEGIN starts the transaction next update deducts money from Alice.Then update adds money to Bob.COMMIT saves changes permanently

After Transaction

SELECT * FROM accounts;

Alice is 800
Bob is 700

This is correct behavior.

4: Failure Scenario

Now we simulate a failure after deducting money.

BEGIN;

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

Simulated error

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

What Happens Here?

First query succeeds belongs Alice becomes 800
Second query fails belongs column error
Transaction stops
Important Point

Since an error occurred, the transaction is not committed.

Check Data

SELECT * FROM accounts;

Result
Alice is 1000
Bob is 500
Explanation

Even though the debit query ran, the database rolled back everything.

This proves Atomicity:
Either both updates happen, or none happen.

5: Failure Scenario

BEGIN;
UPDATE accounts SET balance = balance - 2000 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 2000 WHERE name = 'Bob';
COMMIT;

What Happens?
Alice’s balance would go negative But we have a constraint: CHECK (balance >= 0)
So the query fails
Final State

SELECT * FROM accounts;

Alice is 1000
Bob is 500
Explanation

The database prevents invalid data and rolls back the transaction.

Step 6: Failure Scenario

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

Result
Transaction is canceled manually,No changes are saved
Final State
Alice is 1000
Bob is 500

Step 8: Real-World Improvement

In real systems, we also add:

BEGIN;
UPDATE accounts SET balance = balance - 200WHERE id = 1 AND balance >=200;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;