ACID Practical

Run Postgress docker container

docker run --name pgacid -d -e POSTGRES_PASSWORD=postgres postgres:13
 
docker exec -it pgacid psql -U postgres

---

ACID Properties in Databases

In relational databases, ACID is a set of properties that ensure reliable and consistent transaction processing.

What is ACID?

Property	Description
Atomicity	Ensures that all operations in a transaction are completed or none are.
Consistency	Ensures the database transitions from one valid state to another.
Isolation	Ensures transactions are executed independently, avoiding interference.
Durability	Once committed, changes are permanent, even in case of system failure.

---

Example Setup

CREATE TABLE products (
	pid serial primary key,
	name text,
	price float,
	inventory integer
);
 
CREATE TABLE sales (
	saleid serial primary key,
	pid integer,
	price float,
	quantity integer
);
 
-- Insert initial product
INSERT INTO products (name, price, inventory) VALUES('Phone', 999, 100);

---

Transaction Block Example

BEGIN TRANSACTION;
    SELECT * FROM products; -- Check current inventory
    UPDATE products SET inventory = inventory - 10; -- Reduce stock
    SELECT * FROM products; -- Confirm updated inventory
    INSERT INTO sales (pid, price, quantity) VALUES(1, 999, 10); -- Record sale
COMMIT;

---

Breakdown of ACID in This Example

Atomicity

• The block of operations is treated as a single unit.
• If any step (e.g., the INSERT INTO sales) fails, the entire transaction is rolled back, and no partial changes are saved.

Consistency

• The transaction ensures that:
  • The inventory is correctly reduced by 10.
  • A corresponding entry is added to sales.
• The database moves from one valid state (inventory: 100) to another valid state (inventory: 90 with matching sale record).

Isolation

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

• This isolation level ensures that:
  • This transaction reads from a snapshot taken at its first query, so it never sees other transactions’ later commits.
  • Prevents issues like non-repeatable reads and (in Postgres) phantom reads.
• Other transactions can still modify the rows this transaction read (plain reads take no row locks under MVCC).
• A plain SELECT never waits for row locks at any isolation level, even inside BEGIN; it returns the last committed value. Only SELECT ... FOR SHARE/FOR UPDATE and UPDATE/DELETE of the same row wait for the other transaction to commit or roll back (DDL like ALTER TABLE/TRUNCATE blocks even plain SELECTs).
• If this transaction later tries to update a row that another transaction changed and committed, Postgres aborts it with a serialization error (could not serialize access due to concurrent update); the application must retry.

Durability

• Once COMMIT; is executed:
  • The changes to both products and sales are persisted permanently.
  • Even if the system crashes immediately afterward, the data remains intact in the database.

---

Summary

Property	Ensured By
Atomicity	BEGIN … COMMIT; - All or nothing
Consistency	Proper business logic and relational constraints
Isolation	ISOLATION LEVEL REPEATABLE READ or higher
Durability	Underlying database commit and logging system

---

This example illustrates how a single logical unit of work adheres to ACID principles to maintain data integrity and reliability.