Transactions, Concurrency Control, and Isolation Levels Questions

READ UNCOMMITTED

• Description: Lowest isolation; transactions may see uncommitted changes from others.
• Prevents: none.
• Allows: dirty reads, non-repeatable reads, phantom reads.
  Example (dirty read):

-- T1
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- not committed
-- T2 (READ UNCOMMITTED)
SELECT balance FROM accounts WHERE id = 1; -- sees intermediate value
-- If T1 ROLLBACK, T2 read was dirty

READ COMMITTED

• Description: A statement sees only committed data; each query in a transaction sees current committed state.
• Prevents: dirty reads.
• Allows: non-repeatable reads, phantom reads.
  Example (non-repeatable read):

-- T1 (READ COMMITTED)
SELECT name FROM users WHERE id = 5; -- "Alice"
-- T2
UPDATE users SET name='Alicia' WHERE id=5; COMMIT;
-- T1
SELECT name FROM users WHERE id = 5; -- sees "Alicia" (non-repeatable)

REPEATABLE READ

• Description: Ensures rows read once will return same values within the transaction; protects against dirty and non-repeatable reads.
• Prevents: dirty reads, non-repeatable reads.
• Allows: phantom reads (in some DBs; e.g., MySQL InnoDB provides next-key locking that can prevent phantoms in many cases).
  Example (phantom read):

-- T1 (REPEATABLE READ)
SELECT COUNT(*) FROM orders WHERE customer_id=10; -- 5
-- T2
INSERT INTO orders(customer_id, ...) VALUES(10, ...); COMMIT;
-- T1
SELECT COUNT(*) FROM orders WHERE customer_id=10; -- now 6 (phantom)

SERIALIZABLE

• Description: Highest isolation; transactions behave as if executed serially.
• Prevents: dirty reads, non-repeatable reads, phantom reads.
• Trade-offs: highest contention, reduced concurrency.
  Summary table:
• READ UNCOMMITTED: prevents none
• READ COMMITTED: prevents dirty reads
• REPEATABLE READ: prevents dirty + non-repeatable reads
• SERIALIZABLE: prevents dirty + non-repeatable + phantom reads

Notes: Specific behavior can vary by DBMS (e.g., snapshot isolation, MVCC semantics). Always check your DB's guarantees.

MVCC internals (how it works)

• Each row stores multiple versions (value + metadata): a creation timestamp (TXN ID) and a deletion/replace timestamp. On update/insert the DB creates a new version; old versions remain for readers.
• Transactions get a consistent snapshot timestamp (or xmin/xmax). A read returns the newest version whose timestamps are visible to the reader’s snapshot (created before snapshot start and not deleted before snapshot start).
• Writers normally operate by creating new versions and marking old ones obsolete; GC/vacuum later reclaims unreachable versions.
• Concurrency control uses timestamps/transaction IDs and sometimes locks for writes; conflict detection can be done at commit.

Snapshot isolation vs true serializability

• Snapshot Isolation (SI): each transaction reads from a snapshot taken at start; concurrent writers that update the same row typically cause a write-write conflict and one must abort. SI prevents many anomalies but does not guarantee that the outcome is equivalent to some serial order of transactions.
• Serializability: every concurrent execution is equivalent to some serial execution. SI can admit anomalies where two transactions read overlapping data and write different rows, producing a final state impossible under any serial order.

Write-skew example

• Two physicians A and B on-call. Table shifts(doctor, on_call BOOLEAN). Initially A.on_call = true, B.on_call = true. Business rule: at least one doctor must be on_call.
• T1 (A): begin; reads A=true, B=true; sets A=false; commit.
• T2 (B): begin (snapshot taken before T1 commit, so sees A=true,B=true); sets B=false; commit.
• Both commit under SI because they wrote different rows (no write-write conflict). Result: A=false,B=false violates the invariant. No serial order yields this state, so it's a write-skew.

How to prevent write-skew under a DBMS with SI

• Use Serializable Snapshot Isolation (SSI): detect dangerous rw-dependency cycles at runtime and abort one transaction to enforce serializability.
• Acquire appropriate locks: issue SELECT ... FOR UPDATE (or FOR SHARE) on all rows that participate in the invariant so concurrent transactions conflict on write/lock and one blocks/aborts.
• Enforce constraints at the DB level: use CHECK constraints, triggers, or materialized aggregated counters with atomic updates so invariant cannot be violated without conflicting writes.
• Application-level: design transactions to update a canonical row (e.g., a single “on_call_count” row) so write-write conflict occurs.
  Choice rationale: SSI is safest and automated; explicit locking is simple and efficient when you can identify the relevant rows; constraints are robust but may be harder to express for complex predicates.

ACID (simple definitions)

• Atomicity: a transaction is all-or-nothing — either every operation commits or none do.
• Consistency: a transaction moves the database from one valid state to another, preserving constraints and invariants.
• Isolation: concurrent transactions appear to run serially (isolation controls visibility of intermediate states).
• Durability: once committed, results persist despite crashes.

Isolation levels and what can go wrong (step-by-step)

• Read Uncommitted (allows dirty reads)
  Example: T1 writes balance=90 (not committed). T2 reads 90 → uses it, then T1 aborts. T2 acted on invalid data (dirty read).
• Read Committed (prevents dirty reads, allows non-repeatable reads)
  Example: T1 reads balance=100. T2 updates and commits balance=80. T1 reads again and sees 80 (non-repeatable read).
• Repeatable Read (prevents non-repeatable reads, may allow phantom reads)
  Example: T1 queries "SELECT * FROM orders WHERE customer=1" → 2 rows. T2 inserts a new order and commits. T1 re-runs query and sees 3 rows (phantom).
• Serializable (prevents phantoms — strongest)
  Behavior: DB enforces a serial order (via locking or MVCC with predicate-range locks), so none of the anomalies above occur.

Use-case: bank transfer

• Correct pattern: begin; SELECT balance FROM A; ensure balance >= amount; UPDATE A subtract; UPDATE B add; commit.
• At Read Uncommitted or Read Committed without proper locking you risk overdrafts (lost update or dirty intermediate reads). Serializable or explicit row-level locks ensure correctness.

Why isolation level choice matters

• Higher isolation (Serializable) ensures correctness but increases contention, latency, and reduces throughput.
• Lower isolation boosts performance and concurrency but requires application-level compensations (idempotency, retries, optimistic checks).
• Choose based on invariants: if money/account balances must never violate constraints, prefer Serializable or use explicit SELECT ... FOR UPDATE. For read-heavy analytics where slight staleness is acceptable, Read Committed or Snapshot/Repeatable Read may suffice.

1. Simple definitions:

• Lost update: two writers concurrently read same value and both write; one update overwrites the other.
• Read skew: a transaction reads a mixture of stale and fresh data (non-atomic snapshot).
• Write skew: two concurrent transactions make non-conflicting writes that violate a constraint when combined.
• Stale reads: client reads an older version because replication/replica lag.

2. Step-by-step scenarios:

• Lost update: A reads balance=100, B reads balance=100. A adds 10 → writes 110. B subtracts 20 → writes 80. A’s increment lost because B overwrote without seeing A’s change.
• Read skew: Transaction T1 reads user_profile.v1 then reads settings.v2 (updated in between), producing inconsistent UX.
• Write skew: Two doctors check “on-call” schedule: each sees the other as on-call=false and both set their own to false → no one is on-call, violating invariant “≥1 on-call”.
• Stale read: Replica lag: client reads from replica that hasn’t applied recent writes — sees old values.

3. Preventive techniques (how they map):

• Locking (pessimistic): acquire row/table locks to serialize conflicting ops. Simple correctness; risks contention and deadlocks.
• Serializable transactions: provide strict serializability or snapshot isolation with conflict detection to prevent lost/write skew and read skew (serializable snapshot is strongest). Cost: increased latency, retries, coordination.
• Vector clocks / CRDTs: for eventually-consistent systems (e.g., Dynamo), detect concurrent updates and either auto-merge (CRDT) or surface conflicts for resolution. Good for availability; can’t enforce global invariants easily.
• Causal consistency: preserve cause-effect ordering (prevents some anomalies like causal read skew) with less coordination than serializability; good trade-off between consistency and availability.

4. Performance & complexity trade-offs + stakeholder explanation:

• Strong consistency (serializable, distributed locks): simplifies reasoning and prevents all anomalies but increases latency, throughput cost, and operational complexity (leader election, two-phase commit). Explain to stakeholders: “We pay latency/availability to guarantee correctness; use for critical invariants (billing, inventory).”
• Weaker models (eventual, causal, CRDTs): higher availability and lower latency; require app-level conflict handling and can’t enforce some invariants. Explain: “Faster and more resilient for user-facing features (likes, counters) but require careful design or compensating checks.”
• Practical approach: classify data by invariants and choose per-item policy (partition invariant data into strongly-consistent paths, use eventual elsewhere). Present metrics: expected latency, failure scenarios, engineering cost to implement conflict resolution, and business risk (financial loss, user trust) to justify trade-offs.

Two-Phase Commit (2PC) — overview, purpose, limitations, and an alternative (Saga) with a concrete example.

Two-Phase Commit (how it works & why used)

• Purpose: provide atomic distributed transactions across multiple services/resources so either all commit or all abort.
• Phase 1 (Prepare): Coordinator asks participants “Can you commit?” Each participant prepares (e.g., write to a pending state / lock resources) and votes YES/NO.
• Phase 2 (Commit/Abort): If all YES, coordinator sends COMMIT; otherwise sends ABORT. Participants then finalize or roll back.
• Used when strict atomicity across heterogeneous systems (databases, message queues) is required.

Limitations / Failure modes

• Blocking coordinator: If coordinator crashes after participants vote YES but before sending decision, participants may hold locks/pending state indefinitely — blocking progress.
• Latency & throughput: 2PC involves synchronous round trips and disk syncs (durable prepare), increasing latency.
• Single point of failure / coordinator becomes a bottleneck.
• Not suitable for long-running human or external-process workflows.

Alternative: Saga Pattern (for long-running distributed workflows)

• Decompose a global transaction into a sequence of local transactions. Each local transaction has a compensating transaction to undo it if later steps fail.
• Two styles: choreography (event-driven) or orchestration (central coordinator).
• Provides eventual consistency, non-blocking, and better for long-running flows; trades off strict atomicity.

Concrete example — Orchestrated Saga (Python pseudo-implementation)

• Scenario: Order service -> Payment service -> Inventory service. If inventory reservation fails after payment charged, run compensation to refund payment and cancel order.

# Simple orchestrator pseudo-code
class SagaOrchestrator:
    def __init__(self):
        self.steps = []
    def add_step(self, action, compensate):
        self.steps.append((action, compensate))
    def execute(self, ctx):
        completed = []
        for action, compensate in self.steps:
            ok = action(ctx)
            if not ok:
                # run compensations in reverse order
                for a,c in reversed(completed):
                    c(ctx)
                return False
            completed.append((action, compensate))
        return True

# Actions / compensations (simplified)
def create_order(ctx):
    ctx['order_id'] = "ORD123"
    return True

def cancel_order(ctx):
    # mark order canceled
    return True

def charge_payment(ctx):
    ctx['payment_tx'] = "PAY456"
    return True

def refund_payment(ctx):
    # call payment gateway refund
    return True

def reserve_inventory(ctx):
    # simulate failure
    return False

orchestrator = SagaOrchestrator()
orchestrator.add_step(create_order, cancel_order)
orchestrator.add_step(charge_payment, refund_payment)
orchestrator.add_step(reserve_inventory, lambda ctx: None)  # inventory compensate if needed

ctx = {}
success = orchestrator.execute(ctx)
# If reserve_inventory fails, refund_payment and cancel_order are invoked.

Practical considerations

• Design compensations carefully (idempotent, side-effect aware).
• Use durable saga state (event store or database) for retries and recovery.
• Monitor for partial failures, reconciliations, and business invariants.
• Choose saga when availability and responsiveness matter more than strict synchronous atomicity; use 2PC when strict ACID across resources is mandatory and environment supports low-latency coordination.