Transactions & Concurrency Language

ACID property vocabulary with real-world mapping:

Option A is the only response that correctly maps all four ACID properties to their database-level guarantees.

Property	Guarantee	Bank transfer example
Atomicity	All operations in the transaction succeed or all are rolled back	Debit and credit are a single unit — no partial transfer
Consistency	The transaction moves the database from one valid state to another, honouring all constraints	Total balance is conserved; no constraint (e.g., balance >= 0) is violated
Isolation	Concurrent transactions appear to execute serially — intermediate states are not visible to others	A concurrent balance read does not see the "mid-transfer" state where A is debited but B is not yet credited
Durability	Committed data persists even after a crash (guaranteed by WAL)	The committed transfer record survives a power failure

Isolation level anomaly vocabulary:

Option D is correct. A phantom read occurs when a transaction re-executes a range query and finds rows that were inserted (or deleted) by another committed transaction since the first execution.

Anomaly	Description	Prevented from
Dirty read	Reads uncommitted data from another transaction	READ COMMITTED and above
Non-repeatable read	Same row read twice returns different values	REPEATABLE READ and above
Phantom read	Same range query returns different rows	SERIALIZABLE only
Serialization anomaly	Result is not achievable by any serial execution order	SERIALIZABLE only

Isolation level comparison:

Level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Prevented	Possible	Possible
REPEATABLE READ	Prevented	Prevented	Possible
SERIALIZABLE	Prevented	Prevented	Prevented

Cost caveat to raise in architecture reviews: SERIALIZABLE has the highest overhead — transactions may fail with serialization errors and require retry logic. Use it where correctness requires it (e.g., financial ledgers, seat reservation), but it is overkill for most CRUD workloads.

Optimistic vs. pessimistic locking vocabulary:

Option B is the correct description of optimistic locking. It is called "optimistic" because it assumes conflicts are rare — it does not hold any lock during the think-time between read and write, but checks for conflict only at commit time.

Approach	Lock held during user think-time?	Conflict detection	Best for
Optimistic locking	No	Version/timestamp check at update time	Low conflict rate; long user sessions
Pessimistic locking	Yes (SELECT FOR UPDATE)	Blocked until lock released	High conflict rate; short critical sections

Optimistic locking implementation vocabulary:

-- Read phase: capture version
SELECT id, name, version FROM products WHERE id = 42;
-- version = 7

-- Write phase: conditional update
UPDATE products SET name = 'New Name', version = 8
WHERE id = 42 AND version = 7;
-- If 0 rows updated → conflict; someone else changed it first

Key vocabulary:

• Version column — an integer incremented on every UPDATE; the "optimistic lock token"
• Stale write / lost update — the conflict that optimistic locking prevents: overwriting another writer's change
• Conflict retry — application logic that re-reads and re-attempts the update when a conflict is detected

Dirty read vocabulary:

Option C is the correct definition. A dirty read is the most dangerous read anomaly because the data you read may never have been "real" from the perspective of committed database state.

Dirty read scenario:

-- Transaction A begins a transfer:
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1; -- balance now 0

-- Transaction B (READ UNCOMMITTED) reads before A commits:
SELECT balance FROM accounts WHERE id = 1; -- sees 0 ← dirty read!

-- Transaction A is rolled back (e.g., network error):
ROLLBACK; -- balance restored to 500

-- Transaction B made a decision based on balance = 0 that never committed

Term	Definition
Dirty read	Reading uncommitted data from another in-progress transaction
Uncommitted data	Data written by a transaction that has not yet executed COMMIT
Rollback	Undoing all changes of a transaction — makes dirty reads retroactively invalid

Why READ UNCOMMITTED is rarely used: The isolation benefit is trivial in PostgreSQL (it behaves identically to READ COMMITTED in PG's MVCC implementation) and in most other databases it creates serious correctness risks — applications can make decisions based on data that was never durable. Legitimate use cases are extremely limited (e.g., approximate reporting where slightly stale data is fully acceptable and throughput is critical).

2PL vs. 2PC vocabulary:

Option B correctly distinguishes the two protocols. They share "two-phase" in their name but operate at entirely different levels of the database system.

Protocol	Domain	Two phases	Problem it solves
2PL (Two-Phase Locking)	Single node; concurrency control	Growing phase (acquire locks) + Shrinking phase (release locks)	Serializability — prevents anomalies from interleaved concurrent transactions
2PC (Two-Phase Commit)	Distributed systems; agreement	Prepare phase (vote) + Commit/Abort phase (finalize)	Atomicity across distributed nodes — ensures all-or-nothing outcome across participants

2PL key vocabulary:

• Growing phase — a transaction may only acquire locks; cannot release any
• Shrinking phase — a transaction releases locks; cannot acquire new ones
• Strict 2PL — holds all locks until COMMIT/ROLLBACK (standard in most RDBMS)

2PC key vocabulary:

• Coordinator — the node that orchestrates the distributed commit
• Participant — a node that votes and applies the coordinator's decision
• Prepare — participant acknowledges it can commit (durably logs the transaction)
• In-doubt transaction — a transaction stuck in prepared state because the coordinator crashed before sending the final decision

This question tests understanding of *optimistic locking* within the context of a PR description. The correct answer highlights that optimistic locking is suitable when conflicts are rare and avoids performance overhead associated with pessimistic locking. The other options misinterpret the scenario – transaction management isn't directly relevant here, and pessimistic locking might be overly restrictive in this case.

Optimistic locking relies on version numbers. When a user reads an order record, they also grab its current version number. If they then try to update it and the version number hasn't changed since their read, the update succeeds. However, if *another* transaction has updated the order in the meantime (and incremented the version), the subsequent update will fail with a conflict error. Option A is therefore correct – the system handles this by rolling back one of the transactions to prevent data corruption. Options B and C are incorrect because optimistic locking doesn't usually throw exceptions or silently ignore updates; it's designed for efficient concurrent access, albeit with the potential for conflicts needing resolution. Option D describes pessimistic locking, not optimistic.

This question tests understanding of *optimistic locking* within the context of a PR description. The correct answer highlights that optimistic locking is suitable when conflicts are rare and avoids performance overhead associated with pessimistic locking. The other options misinterpret the scenario – transaction management isn't directly relevant here, and pessimistic locking might be overly restrictive in this case.

Optimistic locking relies on version numbers. When a user reads an order record, they also grab its current version number. If they then try to update it and the version number hasn't changed since their read, the update succeeds. However, if *another* transaction has updated the order in the meantime (and incremented the version), the subsequent update will fail with a conflict error. Option A is therefore correct – the system handles this by rolling back one of the transactions to prevent data corruption. Options B and C are incorrect because optimistic locking doesn't usually throw exceptions or silently ignore updates; it's designed for efficient concurrent access, albeit with the potential for conflicts needing resolution. Option D describes pessimistic locking, not optimistic.

This question tests understanding of *optimistic locking* within the context of a PR description. The correct answer highlights that optimistic locking is suitable when conflicts are rare and avoids performance overhead associated with pessimistic locking. The other options misinterpret the scenario – transaction management isn't directly relevant here, and pessimistic locking might be overly restrictive in this case.

Optimistic locking relies on version numbers. When a user reads an order record, they also grab its current version number. If they then try to update it and the version number hasn't changed since their read, the update succeeds. However, if *another* transaction has updated the order in the meantime (and incremented the version), the subsequent update will fail with a conflict error. Option A is therefore correct – the system handles this by rolling back one of the transactions to prevent data corruption. Options B and C are incorrect because optimistic locking doesn't usually throw exceptions or silently ignore updates; it's designed for efficient concurrent access, albeit with the potential for conflicts needing resolution. Option D describes pessimistic locking, not optimistic.

This question tests understanding of *optimistic locking* within the context of a PR description. The correct answer highlights that optimistic locking is suitable when conflicts are rare and avoids performance overhead associated with pessimistic locking. The other options misinterpret the scenario – transaction management isn't directly relevant here, and pessimistic locking might be overly restrictive in this case.

Optimistic locking relies on version numbers. When a user reads an order record, they also grab its current version number. If they then try to update it and the version number hasn't changed since their read, the update succeeds. However, if *another* transaction has updated the order in the meantime (and incremented the version), the subsequent update will fail with a conflict error. Option A is therefore correct – the system handles this by rolling back one of the transactions to prevent data corruption. Options B and C are incorrect because optimistic locking doesn't usually throw exceptions or silently ignore updates; it's designed for efficient concurrent access, albeit with the potential for conflicts needing resolution. Option D describes pessimistic locking, not optimistic.

Transactions provide atomicity – all parts of a transaction must succeed or fail as one unit. This prevents partial updates that could lead to inconsistencies. Locking mechanisms—like row-level locks—are then used within transactions to enforce this isolation and prevent concurrent modifications from corrupting data. Option A correctly captures this dual role.

A `Connection refused` error typically indicates a conflict in resource access. This almost always happens when multiple processes attempt to connect to or modify the same database simultaneously without proper transaction isolation and locking strategies. While overload can contribute, it wouldn't manifest as 'connection refused'. Option 1 describes transient failures – a different SQLSTATE would likely be returned.

ACID is a well-established acronym in the context of databases. It stands for Atomicity, Consistency, Isolation, and Durability – these are the four key properties that guarantee reliable transaction processing. Understanding ACID compliance is crucial to ensuring data integrity during concurrent operations. The other options represent different concepts in IT.

Optimistic locking relies on detecting conflicts *after* changes have been made. When a user attempts to update data that has been modified by another user since the last read, a conflict is detected. This triggers a mechanism (often an error) requiring the user to retry their operation. Option 1 describes eventual consistency; option 3 describes how locking works, not optimistic locking. Option 4 represents a serious design flaw.

The danger of `READ UNCOMMITTED` lies in its lack of data consistency. It permits a transaction to read data that hasn't yet been formally committed by another transaction – this is known as a 'dirty read'. If the other transaction rolls back, the application has read invalid data and could make incorrect decisions based on it. This highlights the importance of stronger isolation levels for critical operations.