When PG cannot serializae two REPEATABLE_READ transactions?
- User
When PG cannot serializae two REPEATABLE_READ transactions?
Assistant
PostgreSQL's
REPEATABLE READisolation level prevents non-repeatable reads and phantoms but does not guarantee full serializability. It uses Snapshot Isolation (SI), which permits certain anomalies like write skew. Below are scenarios where concurrentREPEATABLE READtransactions cannot be serialized (i.e., lead to anomalies):
1. Write Skew
- Scenario: Two transactions read overlapping data, make mutually inconsistent updates, and commit without conflicts.
- Example:
- Table:
accounts(id, balance)with(1, 100)and(2, 100). - Business rule: At least one account must have
balance ≥ 0. - Transaction A (withdraws from account 1):
BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM accounts WHERE id = 1; -- Sees 100 SELECT balance FROM accounts WHERE id = 2; -- Sees 100 (safe to withdraw?) -- Withdraws 150 from account 1: New balance = -50 UPDATE accounts SET balance = -50 WHERE id = 1; COMMIT; - Transaction B (withdraws from account 2, concurrent with A):
BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT balance FROM accounts WHERE id = 2; -- Sees 100 SELECT balance FROM accounts WHERE id = 1; -- Sees 100 (safe to withdraw?) -- Withdraws 150 from account 2: New balance = -50 UPDATE accounts SET balance = -50 WHERE id = 2; COMMIT;
- Table:
- Result: Both accounts have
-50, violating the business rule. - Why?: SI allows both transactions to commit since they update disjoint rows. A truly serializable schedule would abort one.
2. Phantom Write Skew
- Scenario: Transactions read a set of rows, then update rows not directly overlapping with the read set, causing inconsistency.
- Example:
- Table:
tasks(id, status)with(1, 'pending'),(2, 'pending'). - Rule: At least one task must remain pending.
- Transaction A (completes task 1):
BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT COUNT(*) FROM tasks WHERE status = 'pending'; -- Sees 2 -- Completes task 1 UPDATE tasks SET status = 'done' WHERE id = 1; COMMIT; - Transaction B (completes task 2, concurrent with A):
BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT COUNT(*) FROM tasks WHERE status = 'pending'; -- Sees 2 -- Completes task 2 UPDATE tasks SET status = 'done' WHERE id = 2; COMMIT;
- Table:
- Result: All tasks marked
'done', violating the rule. - Why?: Updates affect different rows → no row-level conflict. SI allows both commits.
3. Read-Only Transaction Anomalies
- Scenario: A read-only transaction sees an inconsistent snapshot due to concurrent writes.
- Example:
- Tables:
users(id, credits)andproducts(id, price). - Transaction A (reads data):
BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT SUM(credits) FROM users; -- Returns 100 SELECT SUM(price) FROM products; -- Returns 200 (after concurrent update) COMMIT; - Transaction B (updates
productsduring A's execution):UPDATE products SET price = 300 WHERE id = 1; -- Runs after A's first SELECT.
- Tables:
- Result: Transaction A sees a state where
users(100) andproducts(300) are inconsistent with a business rule (e.g.,credits ≥ total_price). - Why?: SI snapshots are per-transaction but don’t guarantee global consistency across tables.
Why PostgreSQL Allows These Anomalies in
REPEATABLE READ- Snapshot Isolation (SI):
- Each transaction sees a consistent snapshot of the database at the start.
- Writes are allowed only if no concurrent transaction updated the same row (using row-level locks).
- Gaps in SI:
- SI prevents dirty reads, non-repeatable reads, and phantoms.
- It fails to detect conflicts where transactions update different but logically related data (write skew).
Solution: Use
SERIALIZABLEIsolationUpgrade to
SERIALIZABLEto leverage Serializable Snapshot Isolation (SSI):BEGIN ISOLATION LEVEL SERIALIZABLE; -- ... operations ... COMMIT;- SSI uses predicate locks to track read/write dependencies and aborts transactions risking serialization anomalies.
- In the write skew example, one transaction would be aborted with a
40001error (serialization failure).
Summary
Anomaly REPEATABLE READSERIALIZABLEDirty Read Prevented Prevented Non-Repeatable Read Prevented Prevented Phantom Read Prevented Prevented Write Skew Allowed Prevented Phantom Write Skew Allowed Prevented Use
SERIALIZABLEwhen strict serializability is required. ForREPEATABLE READ, handle write skew in application logic (e.g., explicit locks,SELECT FOR UPDATE).
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.