ACID Properties in DBMS Explained — With Real-World Examples and Code

-- Create a simple accounts table to demonstrate atomicity
CREATE TABLE bank_accounts (
    account_id   INT PRIMARY KEY,
    holder_name  VARCHAR(100) NOT NULL,
    balance      DECIMAL(12, 2) NOT NULL CHECK (balance >= 0)
);

-- Seed with two accounts
INSERT INTO bank_accounts VALUES (1, 'Alice', 1000.00);
INSERT INTO bank_accounts VALUES (2, 'Bob',    500.00);

-- ─────────────────────────────────────────────────────
-- SCENARIO A: Successful atomic transfer
-- Both the debit and credit must succeed together.
-- ─────────────────────────────────────────────────────
BEGIN TRANSACTION;

    -- Step 1: Debit Alice's account by $200
    UPDATE bank_accounts
    SET balance = balance - 200.00
    WHERE account_id = 1;

    -- Step 2: Credit Bob's account by $200
    UPDATE bank_accounts
    SET balance = balance + 200.00
    WHERE account_id = 2;

COMMIT; -- Both changes are written to disk together

-- Check the result — both balances changed
SELECT account_id, holder_name, balance FROM bank_accounts;

-- ─────────────────────────────────────────────────────
-- SCENARIO B: Failed transfer — atomicity kicks in
-- Simulate a failure after the debit but before the credit.
-- ─────────────────────────────────────────────────────
BEGIN TRANSACTION;

    -- Debit Alice successfully
    UPDATE bank_accounts
    SET balance = balance - 300.00
    WHERE account_id = 1;

    -- Something goes wrong here — wrong account ID, app crash, etc.
    -- We call ROLLBACK to undo everything in this transaction.
    ROLLBACK; -- Alice's $300 is restored. Bob is untouched.

-- Confirm Alice's balance is back to $800 (not $500)
SELECT account_id, holder_name, balance FROM bank_accounts;

-- Demonstrate how constraints enforce consistency

CREATE TABLE product_inventory (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(150) NOT NULL,
    stock_qty     INT NOT NULL CHECK (stock_qty >= 0), -- Can't go negative
    unit_price    DECIMAL(10,2) NOT NULL CHECK (unit_price > 0)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    product_id    INT NOT NULL,
    quantity      INT NOT NULL CHECK (quantity > 0),
    -- Foreign key ensures we can't order a product that doesn't exist
    FOREIGN KEY (product_id) REFERENCES product_inventory(product_id)
);

-- Seed inventory
INSERT INTO product_inventory VALUES (101, 'Wireless Keyboard', 50, 49.99);

-- ─────────────────────────────────────────────────────
-- CONSISTENCY TEST 1: Valid transaction — passes all rules
-- ─────────────────────────────────────────────────────
BEGIN TRANSACTION;
    INSERT INTO order_items VALUES (1, 101, 2); -- Valid: product 101 exists
    UPDATE product_inventory
    SET stock_qty = stock_qty - 2               -- Valid: 50 - 2 = 48, still >= 0
    WHERE product_id = 101;
COMMIT;

SELECT product_name, stock_qty FROM product_inventory WHERE product_id = 101;

-- ─────────────────────────────────────────────────────
-- CONSISTENCY TEST 2: Violates CHECK constraint
-- Trying to set stock to -1 breaks the rule.
-- The database will REJECT this entire transaction.
-- ─────────────────────────────────────────────────────
BEGIN TRANSACTION;
    UPDATE product_inventory
    SET stock_qty = stock_qty - 9999  -- Would result in -9951, violating CHECK
    WHERE product_id = 101;
COMMIT; -- This will throw an error and auto-rollback

-- ─────────────────────────────────────────────────────
-- CONSISTENCY TEST 3: Violates foreign key constraint
-- Trying to order a product that does not exist.
-- ─────────────────────────────────────────────────────
BEGIN TRANSACTION;
    INSERT INTO order_items VALUES (2, 999, 1); -- product_id 999 doesn't exist!
COMMIT; -- Foreign key violation — transaction rolled back

-- This script demonstrates isolation levels by simulating two concurrent sessions.
-- Run Session A commands in one terminal, Session B commands in another.
-- We're using PostgreSQL syntax here.

-- ─── SETUP ───────────────────────────────────────────
CREATE TABLE flight_seats (
    seat_id        INT PRIMARY KEY,
    flight_code    VARCHAR(10) NOT NULL,
    is_booked      BOOLEAN NOT NULL DEFAULT FALSE,
    passenger_name VARCHAR(100)
);

INSERT INTO flight_seats VALUES
    (1, 'AA101', FALSE, NULL),
    (2, 'AA101', FALSE, NULL),
    (3, 'AA101', FALSE, NULL);

-- ─────────────────────────────────────────────────────
-- DIRTY READ DEMO (READ UNCOMMITTED level)
-- ─────────────────────────────────────────────────────

-- SESSION A: Start booking seat 1 but don't commit yet
-- Run this FIRST in Session A terminal
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE flight_seats
SET is_booked = TRUE, passenger_name = 'Alice'
WHERE seat_id = 1;
-- DO NOT COMMIT YET — simulate slow processing

-- SESSION B: Read seat 1 while Session A hasn't committed
-- Run this in Session B terminal WHILE Session A is still open
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT seat_id, is_booked, passenger_name
FROM flight_seats
WHERE seat_id = 1;
-- Danger! This might show is_booked=TRUE, passenger='Alice'
-- even though Session A hasn't committed.
-- If Session A rolls back, we read data that never existed.
COMMIT;

-- SESSION A: Now roll back (the booking 'never happened')
ROLLBACK;

-- ─────────────────────────────────────────────────────
-- SAFE VERSION: Using SERIALIZABLE isolation
-- This is how you prevent double-booking a seat.
-- ─────────────────────────────────────────────────────

-- SESSION A
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT seat_id FROM flight_seats
WHERE flight_code = 'AA101' AND is_booked = FALSE
FOR UPDATE; -- Lock all unbooked rows so Session B must wait

UPDATE flight_seats
SET is_booked = TRUE, passenger_name = 'Alice'
WHERE seat_id = 1;
COMMIT; -- Session B can now proceed and will see Alice already has seat 1

-- SESSION B (runs after Session A commits)
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT seat_id FROM flight_seats
WHERE flight_code = 'AA101' AND is_booked = FALSE
FOR UPDATE;
-- Now only sees seats 2 and 3 as available — seat 1 is correctly taken
UPDATE flight_seats
SET is_booked = TRUE, passenger_name = 'Bob'
WHERE seat_id = 2;
COMMIT;

-- Final state
SELECT seat_id, is_booked, passenger_name FROM flight_seats ORDER BY seat_id;

-- This script demonstrates how to verify durability-related settings
-- and simulate durable vs. non-durable write behavior.
-- Using PostgreSQL.

-- ─────────────────────────────────────────────────────
-- CHECK 1: Verify WAL (Write-Ahead Log) is enabled
-- In PostgreSQL, wal_level controls what gets logged.
-- 'replica' or 'logical' means WAL is fully active.
-- ─────────────────────────────────────────────────────
SHOW wal_level;
-- Expected output: replica  (the safe default in production PostgreSQL)

-- ─────────────────────────────────────────────────────
-- CHECK 2: synchronous_commit setting
-- This is the dial between performance and durability.
-- 'on'  = Wait until WAL is flushed to disk before confirming COMMIT (DURABLE)
-- 'off' = Confirm COMMIT immediately; WAL flush happens async  (RISKY)
-- ─────────────────────────────────────────────────────
SHOW synchronous_commit;
-- Safe default output: on

-- ─────────────────────────────────────────────────────
-- DEMO: A committed transaction that is durable
-- ─────────────────────────────────────────────────────
CREATE TABLE payment_audit_log (
    log_id          SERIAL PRIMARY KEY,
    transaction_ref VARCHAR(50) NOT NULL,
    amount          DECIMAL(12,2) NOT NULL,
    committed_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- This COMMIT is fully durable when synchronous_commit = on.
-- The database will NOT return control to the client until this
-- record is safely written to the WAL on disk.
BEGIN;
    INSERT INTO payment_audit_log (transaction_ref, amount)
    VALUES ('TXN-2024-98234', 1500.00);
    -- If the server crashes right here (before COMMIT), nothing is written.
    -- That's correct — the transaction never completed.
COMMIT;
-- From this line forward, the record is guaranteed durable.
-- Even a power cut won't lose it.

-- Verify the record exists
SELECT log_id, transaction_ref, amount, committed_at
FROM payment_audit_log
WHERE transaction_ref = 'TXN-2024-98234';

-- ─────────────────────────────────────────────────────
-- WARNING PATTERN: Setting synchronous_commit = off per-session
-- Only do this for genuinely non-critical, high-volume logging
-- where occasional loss of last few records is acceptable.
-- ─────────────────────────────────────────────────────
SET LOCAL synchronous_commit = off; -- Affects this session only
BEGIN;
    INSERT INTO payment_audit_log (transaction_ref, amount)
    VALUES ('TXN-2024-98235', 0.01); -- Low-value, high-frequency event
COMMIT;
-- COMMIT returns fast, but WAL flush is async.
-- In a crash window of ~200ms, this record COULD be lost.
-- Never use this for financial transactions.