ACID PROPERTY

ACID is a set of properties that guarantee reliability and consistency in database transactions. Whether you’re working with MySQL, PostgreSQL, or SQLite, understanding ACID is crucial for designing safe and robust applications.

Let’s break down ACID and see how it works in practice.

1. What is ACID?

ACID stands for:

Atomicity – A transaction is all-or-nothing. Either every step succeeds, or none does.
Consistency – Transactions always leave the database in a valid state.
Isolation – Transactions run independently without interfering with each other.
Durability– Once a transaction is committed, the data persists even if the system crashes.

1. Setup: Sample Accounts Table

CREATE TABLE Accounts (
    acc_no INTEGER PRIMARY KEY,
    name TEXT,
    balance INTEGER CHECK (balance >= 0)
);

INSERT INTO Accounts (acc_no, name, balance) VALUES
(1, 'Alice', 5000),
(2, 'Bob', 3000),
(3, 'Charlie', 2000);

Here, the CHECK (balance >= 0) ensures no negative balances, which helps demonstrate consistency.

1. Atomicity

A money transfer should be all-or-nothing. Let’s try transferring 1000 from Alice to Bob:

BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;

ROLLBACK;  -- Undo the transaction

Result: balances remain unchanged.
This shows that partial updates do not occur, ensuring atomicity.

1. Consistency

The database should always stay valid. For example:

INSERT INTO Accounts (acc_no, name, balance) VALUES (4, 'David', -500);

This fails due to the CHECK constraint, demonstrating that invalid data cannot enter the system.

1. Isolation

Two transactions running concurrently should not interfere.

• Session 1:

BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
-- Do NOT commit yet

• Session 2 (simultaneous):

SELECT * FROM Accounts WHERE acc_no = 1;

Depending on the isolation level, Session 2 may or may not see the uncommitted changes from Session 1.
This ensures transactions run independently.

1. Durability

Once a transaction is committed, it persists:

BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 3;
COMMIT;

Even if the database crashes or restarts, the update remains. This is durability in action.

1. Notes for SQLite Users

If you’re using Programiz or an online SQLite editor, some features like BEGIN TRANSACTION or COMMIT may not work due to automatic transaction management. In that case:

• Run statements sequentially to observe effects.
• Use local SQLite installations (CLI or DB Browser) to test transactions fully.

Conclusion

ACID properties are essential for safe and reliable databases. Understanding and testing them with practical SQL examples prepares you for real-world applications like banking systems, e-commerce, and more.

Try it yourself:
Experiment with transfers, constraints, and concurrent sessions to see ACID in action. It’s one of the best ways to really understand database reliability.