1. Why do Phantom Reads occur, and how does Serializable prevent them?

A phantom read happens when you run the same query twice and new rows appear between the two executions.

Example

Suppose T1 asks:

SELECT * FROM orders
WHERE amount > 100;

Initially:

Orders
+------+--------+
| ID   | Amount |
+------+--------+
| 1    | 150    |
| 2    | 200    |
+------+--------+

T1 sees → 2 rows

Now another transaction inserts a new row:

T1                          T2
│                           │
│ SELECT amount > 100      │
│ → 2 rows                  │
│                           │
│                           │ INSERT 500
│                           │ COMMIT
│                           │
│ SELECT amount > 100      │
│ → 3 rows  ← PHANTOM!     │
│                           │

The new row is a phantom row because it wasn’t there during the first query.

Why ordinary row locks aren’t enough

Existing rows:
[150] [200]

Locks:
 ↑     ↑
 row   row

New INSERT:
          ↓
[150] [200] [500]
                  ↑
            this row didn't
            exist to lock!

Serializable

Serializable prevents this by making the transaction behave as if transactions ran one at a time.

Depending on the database, this can involve:

Range/GAP locking:

[100 ----------- 200]
       🔒 LOCKED

INSERT 150 → BLOCKED
INSERT 180 → BLOCKED
INSERT 500 → allowed

Some systems instead use Serializable Snapshot Isolation (SSI) to detect dangerous conflicts and abort one transaction.

Remember:

Phantom Read
    ↓
Same query
    ↓
New matching rows appear
    ↓
Serializable prevents it

---

2. PostgreSQL vs MySQL InnoDB MVCC

Both use MVCC (Multi-Version Concurrency Control), but they store old versions differently.

PostgreSQL

When a row changes:

OLD ROW
   ↓ UPDATE
NEW ROW

The old row remains in the table as a dead tuple.

PostgreSQL heap

[Row A - old version]  ← dead tuple
[Row A - new version]  ← current version
[Row B]
[Row C]

Later, VACUUM cleans up dead tuples.

UPDATE
  ↓
New version created
  ↓
Old version remains
  ↓
VACUUM
  ↓
Old version cleaned

MySQL InnoDB

InnoDB generally updates the current row and stores information needed to reconstruct older versions in the Undo Log.

Main table
[Current Row]
      ↑
      │
      │ reconstruct old version
      │
Undo Log
[Old version information]

Easy comparison

PostgreSQL:
Old versions → inside table
Cleanup      → VACUUM

InnoDB:
Old versions → Undo Log
Cleanup      → purge

	PostgreSQL	InnoDB
Current row	Heap	Data page
Old version	Dead tuple	Undo log
Cleanup	VACUUM	Purge
MVCC	Yes	Yes

Memory trick:

PostgreSQL leaves the old row behind. InnoDB puts the old information in Undo.

---

3. What anomaly can still occur under Snapshot Isolation?

The answer is Write Skew.

Snapshot Isolation prevents many common problems:

Dirty Read              ❌
Non-repeatable Read     ❌
Many phantom problems   ❌
Write Skew              ✅ possible

Classic doctor example

Rule:

At least ONE doctor must be on call.

Initial state:

Doctor A → ON
Doctor B → ON

Now:

Transaction A              Transaction B
      │                          │
      │ Read B = ON              │ Read A = ON
      │                          │
      │ "I can go OFF"           │ "I can go OFF"
      │                          │
      │ A → OFF                  │ B → OFF
      │                          │
      └────── COMMIT ────────────┘

Final state:

Doctor A → OFF
Doctor B → OFF

🚨 Nobody is on call!

Neither transaction violated the rule by itself.

The problem happens because their two independent writes combine badly.

Write Skew vs Lost Update

Lost Update:
T1 ──writes Row A──┐
T2 ──writes Row A──┘
             ↑
       same row

Write Skew:
T1 ──writes Row A
T2 ──writes Row B
       ↑       ↑
    different rows
       ↓
  combined result
  violates a rule

Remember:

Write skew = different rows, bad combined decision.

---

4. When is Optimistic Locking preferred?

Optimistic locking is best when conflicts are rare.

Imagine:

100 users read a record
        ↓
Only 1 user actually modifies it

There’s little point making all 100 users wait for locks.

Optimistic approach

Use a version number:

Product
+----+-------+---------+
| ID | Price | Version |
+----+-------+---------+
| 1  | 100   | 5       |
+----+-------+---------+

Application reads:

Price = 100
Version = 5

Later it performs:

UPDATE products
SET price = 120,
    version = 6
WHERE id = 1
  AND version = 5;

If someone already changed version 5 → 6:

WHERE version = 5
       ↓
     FALSE
       ↓
0 rows updated
       ↓
CONFLICT!

The application can retry or tell the user.

Comparison

Pessimistic
───────────
Read
 ↓
LOCK 🔒
 ↓
Work
 ↓
Update
 ↓
Commit
 ↓
Unlock

Optimistic
──────────
Read
 ↓
Work without lock
 ↓
Check version
 ↓
Same? → UPDATE
Changed? → Conflict

Best use

Low conflicts       → Optimistic ✅
Many conflicts      → Pessimistic ✅
Long user sessions  → Optimistic ✅
Read-heavy systems  → Optimistic ✅

---

5. Classic Write Skew Example

The textbook example is the doctor/on-call scenario.

RULE:
At least 1 doctor must remain ON CALL

Initial:

A = ON
B = ON

Transactions:

        T1                       T2
         │                        │
    Read B = ON              Read A = ON
         │                        │
    A → OFF                   B → OFF
         │                        │
       COMMIT                  COMMIT
         │                        │
         └─────────┬──────────────┘
                   ↓
              A = OFF
              B = OFF

              ❌ RULE BROKEN

Why?

T1 changes A
T2 changes B

Different rows!

This isn’t a lost update.

Lost Update
→ same row overwritten

Write Skew
→ different rows
→ decisions interact
→ global rule breaks

---

6. How does a Deadlock Manager select a victim?

Suppose:

T1 holds A → wants B
T2 holds B → wants A

T1 ──waits──> T2
 ↑             │
 └────waits────┘

That’s a cycle:

T1 → T2 → T1
      ↑
   DEADLOCK

The DBMS must kill one transaction.

Typical decision

It tries to choose the cheapest transaction to roll back.

Factors may include:

Transaction
    │
    ├── Amount of work done
    ├── Number of changes
    ├── Undo/log cost
    ├── Transaction priority
    └── Age

For example:

T1 → 10,000 changes
T2 → 10 changes

Kill T2
   ↓
Much cheaper rollback

Then:

T2 → ROLLBACK
      ↓
Locks released
      ↓
T1 continues

Remember:

Deadlock victim = usually the transaction that is cheapest to kill.

---

7. Why can Lock Escalation hurt performance?

Suppose a transaction needs to update 5,000 rows.

Initially:

Row locks:

🔒 Row 1
🔒 Row 2
🔒 Row 3
🔒 ...
🔒 Row 5000

That’s a lot of lock-management memory.

The DBMS may decide:

5,000 row locks
       ↓
LOCK ESCALATION
       ↓
1 TABLE LOCK

Now:

Entire table
┌─────────────────────────┐
│ 🔒 TABLE LOCK           │
│                         │
│ Row 1                   │
│ Row 2                   │
│ ...                     │
│ Row 100,000             │
└─────────────────────────┘

The problem:

T1 needs Row 10
T2 needs Row 90,000

Without escalation:

T1 🔒 Row 10
T2 🔒 Row 90,000

→ Both can work ✅


With table lock:

T1 🔒 ENTIRE TABLE

T2 → WAIT ❌

So:

Fine-grained locks
→ more memory
→ better concurrency

Table lock
→ less memory
→ worse concurrency

Memory trick:

Lock escalation = save memory, sacrifice concurrency.

---

8. Update (U) Lock Compatibility

An Update lock is designed to avoid a particular type of deadlock when a transaction intends to update data.

Simplified compatibility:

        Existing
        S     U     X
New
S       ✅    ✅    ❌
U       ✅    ❌    ❌
X       ❌    ❌    ❌

The important point:

S + S → allowed
S + U → allowed
U + U → NOT allowed
U + X → NOT allowed

Why U locks help

Without U locks:

T1: S lock ─────────→ wants X
T2: S lock ─────────→ wants X

       T1 waits for T2
       T2 waits for T1

          DEADLOCK

With Update locks:

T1 → U lock 🔒
T2 → requests U
         ↓
       WAIT

Only one transaction gets the U lock.

Then:

T1
 ↓
U lock
 ↓
X lock
 ↓
UPDATE
 ↓
COMMIT
 ↓
T2 gets U

Remember:

U lock = “I intend to update this.”

---

9. Why does InnoDB use Gap Locks?

A gap lock locks the space between index records, not just existing records.

Suppose:

Index:

100       150       200
 |---------|---------|
      GAP       GAP

Transaction:

SELECT *
FROM orders
WHERE amount BETWEEN 100 AND 200
FOR UPDATE;

InnoDB can protect the range:

100 🔒──────🔒 150 🔒──────🔒 200
       GAPS LOCKED

Now another transaction tries:

INSERT INTO orders(amount)
VALUES (175);

It falls inside the locked gap:

100 -------- 175 -------- 200
             ↑
           INSERT
             ↓
           BLOCKED

Why?

Without gap locking:

T1: SELECT 100–200
       ↓
     sees 150

T2: INSERT 175
       ↓
     COMMIT

T1: SELECT 100–200
       ↓
     sees 150 + 175

🚨 Phantom

Gap locks help prevent that kind of phantom under InnoDB’s locking behavior.

Memory trick:

Row lock protects the row. Gap lock protects the empty space where a new row could appear.

---

10. Read Committed: What happens to the balance?

Initial:

Balance = 100

T1:

BEGIN;

SELECT Balance
FROM Accounts
WHERE id = 1;

Result:

100

Meanwhile T2:

T2
 │
 ├── UPDATE balance = 200
 │
 └── COMMIT

Now T1 runs the same query again:

SELECT Balance
FROM Accounts
WHERE id = 1;

Result:

200

Why?

Read Committed normally gives each statement its own view/snapshot of committed data.

T1
 │
 ├── SELECT #1
 │      ↓
 │     100
 │
 │   T2 commits 200
 │
 ├── SELECT #2
 │      ↓
 │     200
 │
 └── COMMIT

Therefore:

First read  → 100
Second read → 200

Same query
Different result
      ↓
NON-REPEATABLE READ

Isolation comparison

READ COMMITTED
    ↓
Each statement sees
latest committed data

T1 SELECT → 100
T2 COMMIT 200
T1 SELECT → 200

Whereas conceptually under Repeatable Read:

T1 starts
   ↓
Snapshot = 100
   ↓
T2 commits 200
   ↓
T1 reads again
   ↓
Still sees 100

Easy memory trick:

Read Committed = fresh view per statement. Repeatable Read = stable view for the transaction (subject to the database’s specific MVCC/locking behavior).

---