Transactions, Isolation Levels, MVCC, Locking, Triggers, Audit Logs & CAP Theorem in PostgreSQL 🔒

🔒 Isolation, MVCC, Locking, Triggers, Audit Logging & CAP Theorem

This is one of the MOST important database topics for a Full Stack Developer. 💪

Imagine you're building an Instagram-like Social Feed. Thousands of users might like posts, comment, follow people and update profiles at the same time. Now imagine:

• 🛒 Two users try to buy the last available product
• 🏦 Two requests update the same bank account
• ❤️ Someone likes a post while another person deletes it
• 💥 A transaction fails halfway through
• 👀 Two people read the same data while a third is modifying it

How does the database prevent incorrect data, conflicts and inconsistent results? That's exactly what we'll learn, from absolute zero, with PostgreSQL examples and connections to FastAPI and SQLAlchemy. 🚀

---

📚 Table of Contents

1. What is a transaction?
2. ACID: Isolation and Durability
3. What is concurrency?
4. Dirty read
5. Non-repeatable read
6. Phantom read
7. Isolation levels
8. MVCC
9. Row-level locking and deadlocks
10. Database triggers
11. Audit logging
12. CAP theorem
13. ACID vs CAP
14. Connecting it all to FastAPI
15. Revision sheet
16. Interview questions
17. Quiz
18. Practice checklist (two-session lab included!)

---

1️⃣ What is a transaction?

📦 A transaction is a group of database operations treated as one logical unit of work.

Transfer ₹1,000 from Account A to Account B:

Account	Before	After
A	₹5,000	₹4,000
B	₹2,000	₹3,000

Two actions are needed: subtract from A, add to B. But what if ₹1,000 is deducted from A, the server crashes, and B never receives it? 😱 Money has disappeared. Transactions prevent this.

BEGIN                  → start transaction
  UPDATE Account A     → deduct ₹1,000
  UPDATE Account B     → add ₹1,000
COMMIT                 → save changes ✅
ROLLBACK               → undo changes ❌

BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE id = 2;

COMMIT;

If an error occurs before committing:

ROLLBACK;

Command	Meaning
BEGIN	Starts a transaction
COMMIT	Permanently saves the transaction's changes
ROLLBACK	Cancels uncommitted changes
SAVEPOINT	Creates a point inside a transaction you can partially roll back to

📍 Bonus: SAVEPOINT

BEGIN;

INSERT INTO orders (id, user_id) VALUES (1, 10);

SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id) VALUES (1, 999);  -- suppose this fails

ROLLBACK TO SAVEPOINT after_order;   -- undo only the item insert

COMMIT;                               -- the order itself is still saved

🧠 A transaction is not necessarily one SQL query. It can contain many.

---

2️⃣ ACID: Isolation and Durability 🧪

ACID is a set of four properties that keep transactions reliable:

• ⚛️ A: Atomicity. All operations succeed or none do.
• ✅ C: Consistency. Data follows database rules before and after.
• 🚦 I: Isolation. Concurrent transactions don't interfere incorrectly.
• 💾 D: Durability. Once committed, changes survive crashes (under the database's durability guarantees).

You likely know Atomicity and Consistency already. Today we go deep on Isolation and Durability.

2.1 🚦 Isolation: what does it mean?

Two people use the app at the same time:

• User A wants to update a product price
• User B wants to read that price

Isolation decides how transactions interact when they run concurrently. The goal: never show users incorrect or incomplete intermediate data.

Product 101 costs ₹500.

Transaction A:

BEGIN;

UPDATE products
SET price = 700
WHERE id = 101;
-- not committed yet!

Meanwhile, Transaction B:

SELECT price
FROM products
WHERE id = 101;

Should B see ₹500 or ₹700? 🤔 It depends on the isolation level, which is the most important part of today's lesson.

2.2 💾 Durability: what does it mean?

Once a transaction is successfully committed, its changes should survive a crash or restart.

1. User updates their profile name
2. Database executes COMMIT
3. Database server crashes 💥
4. Server restarts
5. The updated name is still there ✅

How? PostgreSQL uses:

• 📓 Write-Ahead Logging (WAL): records changes in a log before the data pages need to be written
• 🔁 Recovery: replays the log after a crash
• 🏁 Checkpoints: limit how much work recovery must replay
• 🔄 Storage synchronization: persists required data per configured durability settings

📓 What is WAL?

Think of WAL as a diary kept by the database. Before changes are safely reflected in the main data files, PostgreSQL writes the needed information to its log.

1. Application runs an UPDATE
2. PostgreSQL records the change in WAL
3. COMMIT is acknowledged once required WAL durability conditions are met
4. Data pages are written to their main files LATER
5. If a crash happens → PostgreSQL replays WAL during recovery

🎤 Interview point: COMMIT does not necessarily mean every modified table page has already reached its final location. WAL is what makes crash recovery possible.

⚠️ Durability also depends on correct configuration and reliable storage. For example, weakening synchronous_commit weakens the guarantee.

---

3️⃣ What is concurrency? 🚗🚕🚙

Concurrency means multiple transactions execute during overlapping periods of time.

Your Instagram-like app at one moment:

• 👤 User A likes a post
• 💬 User B comments on the same post
• ✏️ User C updates their profile
• 📰 User D views the home feed

All these requests hit the backend at nearly the same time, and your backend sends queries for all of them.

Why is it difficult?

Because transactions may:

• Read the same row
• Update the same row
• Insert new rows
• Delete existing rows
• Depend on each other's data

Without proper concurrency control, problems occur. Let's meet the three classic read problems. 👇

---

4️⃣ Dirty Read 🧟

A dirty read happens when a transaction reads data modified by another transaction that has NOT committed yet.

In simple words: you read someone else's temporary, unconfirmed changes.

Price starts at ₹500.

Transaction A:

BEGIN;

UPDATE products
SET price = 900
WHERE id = 1;
-- not committed

Transaction B:

SELECT price FROM products WHERE id = 1;
-- imagine it reads 900

Transaction A then:

ROLLBACK;
-- price is back to 500

But B already used ₹900, a value that was never permanently accepted. That's a dirty read.

1. A: changes price 500 → 900 (not committed)
2. B: reads 900
3. A: ROLLBACK (price back to 500)
4. ❌ B used data that never officially existed

🐘 PostgreSQL note: dirty reads never happen in PostgreSQL. Even if you request READ UNCOMMITTED, it behaves like READ COMMITTED.

---

5️⃣ Non-repeatable Read 🔄

A non-repeatable read happens when the same transaction reads the same row twice and gets different values, because another transaction committed an update in between.

Price starts at ₹500.

Transaction A:

BEGIN;

SELECT price FROM products WHERE id = 1;
-- 500

Transaction B:

BEGIN;

UPDATE products
SET price = 700
WHERE id = 1;

COMMIT;

Transaction A reads again:

SELECT price FROM products WHERE id = 1;
-- 700 😮

Same transaction, same SQL, same row, different result.

Transaction A:  first read  → ₹500
Transaction B:  updates to ₹700 and COMMITS
Transaction A:  second read → ₹700   ← non-repeatable read

This matters when your app expects data to stay stable throughout a transaction.

---

6️⃣ Phantom Read 👻

A phantom read happens when a transaction runs the same query twice and gets a different set of rows, because another transaction inserted, deleted or changed rows matching the condition.

employees

id	department	salary
1	IT	₹50,000
2	IT	₹60,000
3	HR	₹40,000

Transaction A:

BEGIN;

SELECT * FROM employees WHERE department = 'IT';
-- Employee 1, Employee 2

Transaction B:

INSERT INTO employees (id, department, salary)
VALUES (4, 'IT', 55000);

COMMIT;

Transaction A runs the same query again:

SELECT * FROM employees WHERE department = 'IT';
-- Employee 1, Employee 2, Employee 4  👻

Employee 4 is the phantom row.

⚖️ Non-repeatable read vs phantom read

Problem	What changes?
Non-repeatable read	A previously read row's values change
Phantom read	The set of rows returned by a query changes

🧠 Non-repeatable read = same row, different values. Phantom read = different collection of matching rows.

🐘 PostgreSQL detail: Repeatable Read prevents phantom reads thanks to its snapshot-based MVCC implementation (even though the SQL standard would allow them at that level). Serializable also prevents them, and additionally prevents serialization anomalies.

---

7️⃣ Isolation Levels 🎚️ (the most important section!)

An isolation level decides how much protection a transaction gets from changes made by other concurrent transactions. The SQL standard defines four:

1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Serializable

7.1 🟥 Read Uncommitted (weakest)

In theory it allows dirty reads. But PostgreSQL treats it as Read Committed, so dirty reads can't occur.

7.2 🟨 Read Committed (PostgreSQL default)

A transaction only sees data committed before the start of each individual SQL statement.

Notice: each individual statement. Every statement gets a fresh snapshot of committed data.

-- Transaction A
BEGIN;
SELECT price FROM products WHERE id = 1;   -- ₹500

-- Transaction B
UPDATE products SET price = 700 WHERE id = 1;
COMMIT;

-- Transaction A again
SELECT price FROM products WHERE id = 1;   -- ₹700 (fresh snapshot!)

Therefore:

• ✅ Dirty reads: prevented
• ⚠️ Non-repeatable reads: possible
• ⚠️ Phantom reads: possible
• ⚠️ Serialization anomalies: possible

Great for: normal web apps, social feeds, ordinary CRUD.

7.3 🟧 Repeatable Read (stronger)

The transaction keeps seeing the same snapshot from its first query onward.

-- Transaction A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT price FROM products WHERE id = 1;   -- 500

-- Transaction B
UPDATE products SET price = 700 WHERE id = 1;
COMMIT;

-- Transaction A again
SELECT price FROM products WHERE id = 1;   -- still 500 ✅

A continues reading its original snapshot. In PostgreSQL this uses MVCC (coming up in section 8).

Repeatable Read prevents dirty reads, non-repeatable reads and (in PostgreSQL) phantom reads. But it does not prevent every anomaly:

🩺 Write skew example: two transactions each read that at least two doctors are on duty, then each marks a different doctor off duty. Both commit, and now nobody is on duty. Only Serializable catches this.

Also, if a Repeatable Read transaction tries to update a row that another transaction changed after its snapshot, PostgreSQL raises:

ERROR: could not serialize access due to concurrent update

and your app must retry.

7.4 🟩 Serializable (strongest)

Successfully committed concurrent transactions produce an outcome equivalent to some serial (one-after-another) execution.

Two users try to buy the last product. Both check stock, both proceed, and boom, wrong result. Serializable detects dangerous interactions and may abort one transaction. The application must retry it.

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT quantity FROM products WHERE id = 1;

UPDATE products
SET quantity = quantity - 1
WHERE id = 1;

COMMIT;

On a conflict, PostgreSQL may return:

ERROR: could not serialize access
due to read/write dependencies among transactions

You must retry the complete transaction.

🧠 Serializable does not mean transactions literally run one by one. PostgreSQL runs them concurrently and uses Serializable Snapshot Isolation (SSI) to detect dangerous conflicts.

7.5 📊 Comparison table (interview favorite!)

Isolation level	Dirty read	Non-repeatable read	Phantom read
Read Uncommitted*	Possible by the standard	Possible	Possible
Read Committed	❌ No	✅ Yes	✅ Yes
Repeatable Read (PostgreSQL)	❌ No	❌ No	❌ No
Serializable	❌ No	❌ No	❌ No

* PostgreSQL treats Read Uncommitted as Read Committed.

The table covers these three read anomalies. Serializable also prevents the broader class of serialization anomalies (like write skew).

🪜 Hierarchy

Read Uncommitted  → weakest protection
Read Committed    → prevents dirty reads
Repeatable Read   → stable transaction snapshot
Serializable      → strongest isolation guarantees

⚖️ Higher isolation generally means more conflict handling, possible retries and extra overhead. It doesn't automatically mean every query is slower.

🛠️ Setting the isolation level

-- per transaction
BEGIN ISOLATION LEVEL REPEATABLE READ;

-- or right after BEGIN
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

In SQLAlchemy:

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@localhost/db",
    isolation_level="REPEATABLE READ",   # applies to the whole engine
)

# or per connection
async with engine.connect() as conn:
    conn = await conn.execution_options(isolation_level="SERIALIZABLE")

---

8️⃣ MVCC: Multi-Version Concurrency Control 🧬

MVCC lets multiple transactions work with different versions of the same data.

Product 1 costs ₹500. Transaction A updates it to ₹700. Transaction B wants to read it. How can PostgreSQL let both happen without unnecessarily blocking each other? Through MVCC.

Conceptually, PostgreSQL keeps different row versions:

Old row version  (visible to older snapshots)   → Product 1, ₹500
        │  Transaction A updates the price
        ▼
New row version  (visible to newer snapshots)   → Product 1, ₹700

📝 This is a simplified picture. PostgreSQL actually stores tuple versions in table storage along with transaction visibility information.

The key ideas:

• 👴 An older transaction can keep seeing the old committed version
• 👶 A newer transaction sees the newly committed version
• 📖 Readers generally don't block writers
• ✍️ Writers generally don't block ordinary readers

📸 What is a snapshot?

A snapshot is the database's view of which transactions and row versions are visible to a particular query or transaction.

• A starts at 10:00
• B updates a row at 10:01 and commits
• A (under Repeatable Read) keeps seeing its original snapshot
• A new transaction starting at 10:02 sees the updated value

🧹 Does PostgreSQL keep old versions forever?

No. A process called VACUUM cleans up obsolete row versions once they're no longer needed. Old versions consume storage, and a long-running transaction can stop PostgreSQL from cleaning some of them, causing table bloat.

💡 Developer takeaway: MVCC improves concurrency, but avoid long-running transactions. Never keep a transaction open while waiting on a user or an external API.

---

9️⃣ Row-Level Locking 🔐

Two people update the same product at once:

id = 1, quantity = 10

A wants 9, B wants 8. How does the database coordinate? Through locks.

🔒 A lock controls access to database resources while transactions operate on them.

9.1 🧱 What is a row-level lock?

A row-level lock protects a particular row from conflicting operations.

BEGIN;

SELECT *
FROM products
WHERE id = 1
FOR UPDATE;

This locks the selected row. Other transactions trying for conflicting locks on that row may have to wait, but they can still work on other rows.

ID	Product	Lock status
1	Laptop	🔒 Locked
2	Mouse	🟢 Available
3	Keyboard	🟢 Available

9.2 ⭐ SELECT ... FOR UPDATE

One of the most important commands for backend developers:

BEGIN;

SELECT quantity
FROM products
WHERE id = 1
FOR UPDATE;

UPDATE products
SET quantity = quantity - 1
WHERE id = 1;

COMMIT;

What happens:

1. PostgreSQL finds the row
2. It acquires a row-level lock
3. Other transactions attempting conflicting updates or row locks wait
4. Your transaction updates the quantity
5. COMMIT releases the lock

Use it for:

• 🏦 Banking transactions
• 📦 Inventory management
• 🎟️ Booking and ticket reservation
• 👛 Wallet balance updates
• 🧠 Any "read a row, then decide based on its value" logic

9.3 🎛️ Row-lock modes

Command	Purpose
FOR UPDATE	Strong lock; blocks conflicting modifications and row-locking
FOR NO KEY UPDATE	Weaker; useful when key columns aren't changing
FOR SHARE	Blocks updates/deletes while allowing other shared locks
FOR KEY SHARE	Protects key values against changes that affect foreign keys

As a beginner, focus on FOR UPDATE.

🎁 Bonus: NOWAIT and SKIP LOCKED

-- Fail immediately instead of waiting
SELECT * FROM products WHERE id = 1 FOR UPDATE NOWAIT;

-- Skip rows that are already locked (great for job queues!)
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

9.4 💀 What is a deadlock?

A deadlock happens when two or more transactions wait for each other forever.

• A locks Row 1
• B locks Row 2
• A now wants Row 2
• B now wants Row 1

Transaction A: owns Row 1, waiting for Row 2
                  ▲                │
                  │                ▼
Transaction B: owns Row 2, waiting for Row 1

        🔁 circular waiting

PostgreSQL detects the deadlock and aborts one transaction with:

ERROR: deadlock detected   (SQLSTATE 40P01)

🛡️ How to prevent deadlocks

1. Always acquire locks in a consistent order (for example, lower account ID first)
2. Keep transactions short
3. Avoid unnecessary locks
4. Never wait for external API calls while holding database locks
5. Retry on appropriate transient errors

If every transaction locks Account A first and Account B second, you reduce the risk of opposite lock ordering.

---

🔟 Database Triggers ⚡

A trigger is a database mechanism that automatically executes a function when a particular event happens.

Think of it as an automatic reaction. Common events:

• INSERT
• UPDATE
• DELETE
• TRUNCATE (statement-level triggers)

Real-world example

Whenever a user changes their username, you want to record the old and new username. You could write that in every endpoint. But what if another developer updates the table directly with SQL? That code gets skipped. A trigger enforces it at the database level.

10.1 🔄 How a trigger works

Database event:   UPDATE users
        ▼
Trigger fires:    PostgreSQL detects the UPDATE
        ▼
Trigger function: records old and new values
        ▼
Audit table:      change history is stored

10.2 🧭 Types of triggers

By timing:

• ⏪ BEFORE: runs before the operation
• ⏩ AFTER: runs after the operation
• 🔀 INSTEAD OF: runs instead of the operation (commonly on views)

By execution level:

• 🧍 ROW-level: once for every affected row
• 📣 STATEMENT-level: once for the entire statement

UPDATE users
SET active = false
WHERE city = 'Bengaluru';

If 100 users match: a ROW trigger fires 100 times, a STATEMENT trigger fires once.

10.3 🆕🆚 NEW and OLD

• OLD → the previous version of a row
• NEW → the new version of a row

OLD.username = deepika
NEW.username = deepika_p

Event	Available
INSERT	NEW
UPDATE	OLD and NEW
DELETE	OLD

10.4 🧪 Hands-on: build an audit trigger

Step 1: the users table

CREATE TABLE users (
    id       SERIAL PRIMARY KEY,
    username VARCHAR(100),
    email    VARCHAR(255)
);

Step 2: the audit table

CREATE TABLE user_audit (
    id           SERIAL PRIMARY KEY,
    user_id      INTEGER,
    old_username VARCHAR(100),
    new_username VARCHAR(100),
    changed_at   TIMESTAMPTZ DEFAULT NOW()
);

Step 3: the trigger function

CREATE OR REPLACE FUNCTION log_username_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.username IS DISTINCT FROM NEW.username THEN
        INSERT INTO user_audit (user_id, old_username, new_username)
        VALUES (OLD.id, OLD.username, NEW.username);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

• IS DISTINCT FROM is a NULL-safe "not equal" comparison
• RETURN NEW; lets the update proceed (required in this kind of trigger)

Step 4: attach the trigger

CREATE TRIGGER username_audit_trigger
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION log_username_changes();

Step 5: test it

INSERT INTO users (username, email)
VALUES ('deepika', '[e-mail]');

UPDATE users
SET username = 'deepika_p'
WHERE id = 1;

SELECT * FROM user_audit;

user_id	old_username	new_username
1	deepika	deepika_p

🎉 The backend developer never manually inserted an audit record in the UPDATE.

⚠️ Important: trigger actions generally run in the same transaction as the statement that fired them. If that transaction rolls back, the audit row rolls back too. Triggers are great for tracking changes, but they are not a substitute for tamper-proof security auditing.

To remove a trigger:

DROP TRIGGER username_audit_trigger ON users;

---

1️⃣1️⃣ Audit Logging 📝

Audit logging means keeping a record of important actions and changes in a system.

In your social app, you may want to know:

• Who changed a username?
• Who deleted a post?
• Who changed account privacy?
• When was an email address changed?
• What was the previous value, and the new one?

User	Action	Old value	New value
101	Username update	deepika	deepika_p
102	Privacy update	Public	Private
103	Profile update	Old bio	New bio

What should an audit log contain?

audit_id
user_id
action
entity_type
entity_id
old_value
new_value
timestamp
request_id

• audit_id → unique audit record ID
• user_id → who performed the action
• action → what happened
• entity_type → which kind of resource changed
• entity_id → which specific resource changed
• old_value / new_value → before and after
• timestamp → when
• request_id → which API request caused it

⚖️ Application-level vs trigger-based logging

Application-level logging	Database trigger logging
Written in Python/FastAPI	Written in database functions
Captures business context	Captures row changes
Flexible for complex workflows	Runs automatically on configured DB events
Can be skipped if a code path misses it	Runs for applicable changes regardless of entry point
Easy to include API request info	Needs extra design to capture request context

In real systems, both are often used together.

🎁 Bonus: giving triggers request context

A trigger doesn't know which app user made the change. One approach: set a transaction-local setting from your backend, then read it inside the trigger.

-- In your app, at the start of the transaction:
SET LOCAL app.current_user_id = '101';

-- Inside the trigger function:
-- current_setting('app.current_user_id', true)

🔐 Security point: don't casually store passwords, raw auth tokens or other secrets in audit logs. Restrict access and protect sensitive logged data.

---

1️⃣2️⃣ CAP Theorem 🌐

Now we enter distributed systems. Don't worry, we'll start from zero.

12.1 🖥️ What is a distributed system?

One server:

Frontend (React) → Backend (FastAPI) → Database (PostgreSQL)

Now millions of users arrive. One server isn't enough, so you add more:

Users
  ▼
Load Balancer
  ├─ Server 1
  ├─ Server 2
  └─ Server 3
        ▼
Distributed database / replicas

Your application now runs across multiple machines communicating over a network. But networks fail! 💥

• Server 1 can't talk to Server 2
• A cable fails
• A data center loses connectivity
• A connection becomes extremely slow

These are network partitions, and this is where CAP becomes important.

12.2 📐 What is CAP theorem?

In a distributed data system, when a network partition occurs, you cannot guarantee both strong Consistency and Availability for every request.

CAP = Consistency, Availability, Partition tolerance.

🅲 Consistency (in CAP)

Every successful read sees the latest successfully committed write, or an appropriate failure is returned.

A user changes their username from deepika to deepika_p. Your database has Nodes A, B and C. Under strong consistency, once the update is acknowledged, any successful read from any node returns deepika_p, never the old name.

🚨 CAP consistency ≠ ACID consistency. ACID consistency is about constraints and valid states. CAP consistency is about visibility and ordering of data across distributed nodes.

🅰 Availability

Every request received by a non-failing node gets a non-error response, even if that response doesn't contain the latest data.

If Node A is unreachable but Node B can still serve the profile request, the system is available. But an available response isn't necessarily the newest value.

🅿 Partition Tolerance

The system keeps operating despite communication failures between groups of nodes.

Network group A: Node A, Node B
        ✂️  communication broken  ✂️
Network group B: Node C, Node D

The nodes are all running, but they can't talk to each other. That's a partition.

12.3 ⚖️ The CAP trade-off

When a partition occurs, you have two broad choices:

Option 1: prioritize Consistency (CP)

• Don't allow potentially stale or conflicting reads
• Some requests may wait or fail
• Consistency over availability

Example: a strongly consistent banking operation may refuse to proceed without quorum or a valid leader.

Option 2: prioritize Availability (AP)

• Keep responding to requests
• Some responses may be stale
• Replicas reconcile later

Example: a social feed shows a slightly older like count for a moment.

❓ "Pick any two of three?" That's a simplification. The accurate version: when a network partition occurs, a distributed system must trade off strong consistency against availability. Partition tolerance isn't something you can simply switch off, since communication failures are possible. And when there's no partition, the trade-off looks different.

12.4 🌍 Real-world examples

Use case	Typical priority
🏦 Bank balance transfers	Strong consistency
📦 Inventory reservations	Strong consistency
📰 Social media feed	Availability (staleness OK)
❤️ Like counts	Often eventual consistency
🎯 Product recommendations	Availability
🧭 Distributed coordination	Strong consistency

These are tendencies, not absolute labels for whole products. One application can use different consistency strategies for different features.

12.5 ⏳ What is eventual consistency?

If no new updates happen and communication is restored, all replicas eventually converge to the same value.

You like a post:

Primary:  Likes = 101
Replica:  Likes = 100     ← hasn't received the update yet

...a moment later...

Primary:  Likes = 101
Replica:  Likes = 101     ← converged ✅

Strong consistency	Eventual consistency
Reads reflect the latest committed write	Reads may temporarily be stale
More coordination required	Allows more independent operation
Good for financial operations	Good for feeds, analytics, some counters
May reject/delay requests during partitions	Can keep serving during partitions

In your social feed:

• 📝 Creating a post must reliably save
• 🔒 Privacy settings need careful consistency
• ❤️ Like counts can tolerate a small delay
• 🎯 Recommendations can update asynchronously

Backend developers choose guarantees based on business requirements.

---

1️⃣3️⃣ ACID vs CAP: don't confuse them! ⚔️

ACID	CAP
About transaction reliability	About distributed system trade-offs
Atomicity	Consistency
Consistency	Availability
Isolation	Partition tolerance
Durability	(no direct equivalent)

• ACID Isolation → transactions don't interfere incorrectly
• CAP Consistency → latest values observed across distributed nodes
• ACID Durability → committed transactions survive failures
• CAP Partition Tolerance → handling network communication failures

They are not interchangeable concepts.

---

1️⃣4️⃣ Connecting it all to your FastAPI project 🔌

Imagine users can reserve limited promotional slots. Only one slot is left, and two users try to reserve it simultaneously.

Without concurrency control:

1. User A reads available_slots = 1
2. User B reads available_slots = 1
3. User A reserves it
4. User B also reserves it
5. 💥 Overbooked!

✅ Solution 1: row-level locking

BEGIN;

SELECT available_slots
FROM promotions
WHERE id = 1
FOR UPDATE;

UPDATE promotions
SET available_slots = available_slots - 1
WHERE id = 1
  AND available_slots > 0;

COMMIT;

The lock makes competing transactions wait. Also check whether the UPDATE actually affected a row. If it didn't, no slot was available.

✅ Solution 2: a single conditional UPDATE (often simplest!)

UPDATE promotions
SET available_slots = available_slots - 1
WHERE id = 1
  AND available_slots > 0
RETURNING available_slots;

This is atomic:

• Row returned → reservation succeeded ✅
• No row returned → no slot available ❌

A very useful backend technique. 🧠

🐍 FastAPI + SQLAlchemy version

from sqlalchemy import select


async def reserve_slot(session, promotion_id: int):
    async with session.begin():
        result = await session.execute(
            select(Promotion)
            .where(Promotion.id == promotion_id)
            .with_for_update()
        )
        promotion = result.scalar_one_or_none()

        if promotion is None:
            raise ValueError("Promotion not found")

        if promotion.available_slots <= 0:
            raise ValueError("No slots available")

        promotion.available_slots -= 1
        return promotion

Code	Meaning
session.begin()	Starts a transaction
select(Promotion)	Builds a SELECT query
.where(...)	Filters the requested promotion
.with_for_update()	Requests a row-level lock
scalar_one_or_none()	Gets one result or None
available_slots -= 1	Updates the value
End of with block	Commits on success, rolls back on exception

⚠️ session.begin() raises an error if a transaction has already started on that session (for example, a dependency already ran a query). If your project's session dependency auto-begins, use the explicit await session.commit() / await session.rollback() style instead. Stay consistent with your project's pattern.

🔁 Retrying on serialization failures and deadlocks

Serializable (and Repeatable Read) can abort a transaction by design, so your code should retry:

from sqlalchemy.exc import DBAPIError

MAX_RETRIES = 3

async def run_with_retry(session_factory, work):
    for attempt in range(1, MAX_RETRIES + 1):
        async with session_factory() as session:
            try:
                async with session.begin():
                    return await work(session)
            except DBAPIError as exc:
                # 40001 = serialization_failure, 40P01 = deadlock_detected
                code = getattr(exc.orig, "sqlstate", None) or getattr(exc.orig, "pgcode", None)
                if code in ("40001", "40P01") and attempt < MAX_RETRIES:
                    continue
                raise

💡 Retry the entire transaction, never just the failed statement.

---

1️⃣5️⃣ Complete revision sheet 🧾

Concept	Simple meaning
Transaction	Group of operations treated as one unit
BEGIN	Start a transaction
COMMIT	Save changes
ROLLBACK	Undo uncommitted changes
Isolation	Controls how concurrent transactions interact
Dirty read	Reading uncommitted changes
Non-repeatable read	Same row returns different values
Phantom read	Same query returns a different set of rows
Read Committed	Each statement sees a fresh committed snapshot
Repeatable Read	The transaction sees a stable snapshot
Serializable	Concurrent execution behaves like serial execution
MVCC	Maintains multiple row versions
Row-level locking	Locks individual rows against conflicts
Deadlock	Transactions wait on each other's locks
Trigger	Auto-runs database logic on an event
Audit logging	Records who changed what and when
WAL	Log used for crash recovery
Durability	Committed data survives crashes (under configured guarantees)
Distributed system	Multiple machines working together
CAP theorem	Consistency vs Availability during partitions
Eventual consistency	Replicas eventually converge

---

1️⃣6️⃣ Important interview questions 🎤

Q1. Read Committed vs Repeatable Read?
Read Committed takes a new snapshot for every statement. Repeatable Read (in PostgreSQL) uses a stable snapshot for the whole transaction.

Q2. What is a dirty read?
Reading data modified by another transaction before it commits.

Q3. Non-repeatable vs phantom reads?
Non-repeatable reads involve changed values in an existing row. Phantom reads involve changes in the set of rows matching a query.

Q4. What is MVCC?
Multi-Version Concurrency Control. It lets transactions access different versions of rows, improving concurrency between readers and writers.

Q5. What is SELECT FOR UPDATE?
It acquires row-level locks on selected rows so competing transactions can't perform conflicting operations on them until the lock is released.

Q6. What is a deadlock?
Transactions waiting for locks held by each other, so none can progress. The database detects it and aborts one.

Q7. What is a database trigger?
A mechanism that automatically executes a trigger function when a specified database event occurs.

Q8. What is audit logging?
Recording important changes and actions: who did them, when, and what changed.

Q9. What is WAL?
Write-Ahead Logging. PostgreSQL records changes in a log to support crash recovery and durability.

Q10. Explain CAP theorem.
During a network partition, a distributed system must choose between guaranteeing strong consistency for successful requests and guaranteeing availability for every request.

Q11. What is eventual consistency?
Replicas may temporarily differ but converge once updates stop and synchronization succeeds.

Q12. ACID consistency vs CAP consistency?
ACID consistency means transactions preserve database rules and constraints. CAP consistency means reads across distributed nodes respect the latest successful write under the chosen guarantee.

Q13. What is write skew, and which isolation level prevents it?
Two transactions read overlapping data and then update different rows based on what they read, producing an invalid combined result. Only Serializable prevents it.

Q14. Should you retry a failed Serializable transaction?
Yes. Retry the entire transaction, since serialization failures are an expected part of the model.

---

1️⃣7️⃣ Test your understanding! 🧪

Answer without scrolling up. (Answers are at the bottom.)

1. Transaction A updates a price but hasn't committed. Transaction B reads that value. What is this?

• A) Dirty read
• B) Phantom read
• C) Deadlock
• D) Durability

2. Transaction A reads the same row twice but sees ₹500 and then ₹700 after another transaction commits. What is this?

• A) Dirty read
• B) Non-repeatable read
• C) Phantom read
• D) Deadlock

3. Which isolation level provides a stable transaction snapshot in PostgreSQL?

• A) Read Uncommitted
• B) Read Committed
• C) Repeatable Read
• D) None

4. Which SQL command locks selected rows against conflicting modifications?

• A) SELECT DISTINCT
• B) SELECT FOR UPDATE
• C) SELECT COUNT
• D) SELECT LIMIT

5. What happens to trigger-generated audit rows if the triggering transaction rolls back?

• A) They remain permanently
• B) They are automatically emailed
• C) They are rolled back too
• D) Nothing happens

6. Which PostgreSQL mechanism maintains multiple row versions?

• A) WAL
• B) MVCC
• C) CAP
• D) JSON

7. During a network partition, what trade-off does CAP describe?

• A) Atomicity vs Durability
• B) Consistency vs Availability
• C) Speed vs Storage
• D) SQL vs NoSQL

8. What does WAL primarily help PostgreSQL achieve?

• A) Frontend rendering
• B) Password hashing
• C) Crash recovery and durability
• D) API routing

👀 Click to reveal answers

1. A: dirty read
2. B: non-repeatable read
3. C: Repeatable Read
4. B: SELECT FOR UPDATE
5. C: they roll back too (same transaction)
6. B: MVCC
7. B: Consistency vs Availability
8. C: crash recovery and durability

---

1️⃣8️⃣ What to practice as a Full Stack Developer 🛠️

Practice in PostgreSQL using pgAdmin or psql, with two query windows open side by side.

✅ Practical checklist

• [ ] Create two transactions and demonstrate COMMIT and ROLLBACK
• [ ] Reproduce a non-repeatable read using two sessions
• [ ] Compare Read Committed and Repeatable Read using two sessions
• [ ] Try SELECT FOR UPDATE with two simultaneous transactions
• [ ] Create a deadlock intentionally and observe PostgreSQL's error
• [ ] Create a PostgreSQL trigger for username changes
• [ ] Create an audit table and verify old/new values
• [ ] Observe WAL and understand how PostgreSQL performs recovery
• [ ] Explain CP and AP with a real-world example

🧪 Mini lab: non-repeatable read (Read Committed)

-- Setup (run once)
CREATE TABLE products (id INT PRIMARY KEY, price INT);
INSERT INTO products VALUES (1, 500);

Step	Session 1	Session 2
1	BEGIN;
2	SELECT price FROM products WHERE id = 1; → 500
3		UPDATE products SET price = 700 WHERE id = 1; (autocommits)
4	SELECT price FROM products WHERE id = 1; → 700 😮
5	COMMIT;

🧪 Mini lab: same test under Repeatable Read

Reset the price to 500, then:

Step	Session 1	Session 2
1	BEGIN ISOLATION LEVEL REPEATABLE READ;
2	SELECT price FROM products WHERE id = 1; → 500
3		UPDATE products SET price = 700 WHERE id = 1;
4	SELECT price FROM products WHERE id = 1; → still 500 ✅
5	COMMIT; then SELECT again → 700

🧪 Mini lab: FOR UPDATE blocking

Step	Session 1	Session 2
1	BEGIN;
2	SELECT * FROM products WHERE id = 1 FOR UPDATE;
3		BEGIN;
4		UPDATE products SET price = 999 WHERE id = 1; → hangs, waiting ⏳
5	COMMIT;	Session 2's update now proceeds
6		COMMIT;

🧪 Mini lab: create a deadlock 💀

INSERT INTO products VALUES (2, 800);

Step	Session 1	Session 2
1	BEGIN;	BEGIN;
2	UPDATE products SET price = 1 WHERE id = 1;
3		UPDATE products SET price = 2 WHERE id = 2;
4	UPDATE products SET price = 3 WHERE id = 2; → waits ⏳
5		UPDATE products SET price = 4 WHERE id = 1; → 💥 deadlock detected

PostgreSQL aborts one of them. Run ROLLBACK; in the aborted session. Then think: how would consistent lock ordering have avoided this?

---

🎯 Final thoughts

💡 My biggest advice: don't just memorize the four isolation levels. Run the same scenario in two query windows. Watching Transaction A and Transaction B happen at the same time makes isolation, locking and concurrency click.

Connect it to your stack:

• ⚡ FastAPI handles incoming concurrent requests
• 🧪 SQLAlchemy manages database transactions
• 🐘 PostgreSQL handles isolation, MVCC, locks and triggers
• 📓 WAL helps with durability
• 🌐 Distributed architecture introduces CAP trade-offs

Together, these concepts form a major part of backend engineering. 🏆

---

🙌 Enjoyed this post?

• ❤️ Drop a reaction
• 🦄 Save it for revision
• 💬 Comment with questions, and I'll answer them!

Happy coding! 👩‍💻✨

postgres #sql #database #backend #concurrency