CA - 25 Atomicity

Verifying Atomicity in a Digital Wallet Transaction

In this experiment, I focused on designing a secure money transfer system similar to PhonePe or GPay, where transactions must be reliable and consistent. The main objective was to verify the Atomicity property of transactions — ensuring that either all operations in a transaction are completed successfully or none of them are applied.

---

Initial Setup

I created the accounts table and inserted sample data:

```sql id="1k8v3n"
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 INTO accounts (name, balance)
VALUES
('Alice', 1000),
('Bob', 500);

To check the initial state:



```sql id="n8k2xq"
SELECT * FROM accounts;

Initial balances:

• Alice → 1000
• Bob → 500

---

Performing a Successful Transaction

I first implemented a correct transfer of 200 from Alice to Bob.

```sql id="x4v9pj"
BEGIN;

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

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

COMMIT;

After execution:



```sql id="l7c3zm"
SELECT * FROM accounts;

Updated balances:

• Alice → 800
• Bob → 700

This confirms that both operations (debit and credit) were successfully completed.

---

Introducing an Error After Debit

Next, I simulated a failure scenario by introducing an error after deducting money from Alice but before crediting Bob.

```sql id="d2r5qw"
BEGIN;

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

-- Intentional error (wrong column name)
UPDATE accounts
SET bal = balance + 300
WHERE name = 'Bob';

COMMIT;

 The second query fails because `bal` does not exist.

---

 Observing the Result

Now, I checked the account balances:



```sql id="f6t8yn"
SELECT * FROM accounts;

• Alice’s balance remains unchanged
• Bob’s balance remains unchanged

This shows that the entire transaction was rolled back automatically due to the error.

---

Explicit Rollback Example

To further confirm atomicity, I tested manual rollback:

```sql id="q1m7zs"
BEGIN;

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

ROLLBACK;

Checking again:



```sql id="h3v9pk"
SELECT * FROM accounts;

---

Key Observations

• Even though the debit operation was executed, it was not permanently saved
• The database prevented partial updates
• Both operations (debit and credit) are treated as a single unit

---

Understanding Atomicity

This experiment demonstrates the Atomicity property of ACID:

• A transaction is all or nothing
• If any part fails → entire transaction is rolled back
• No intermediate or partial changes are stored

---

Conclusion

Through this experiment, I verified that PostgreSQL ensures atomic transactions. Even when errors occur in the middle of a transaction, the system automatically prevents partial updates and restores the database to its previous consistent state.

This reinforces the importance of using transactions in applications like digital wallets, where accuracy and reliability are critical. Ensuring atomicity guarantees that financial operations remain safe and consistent, even in the presence of unexpected failures.