Skip to content
TheChatArchiveThe Chat Archive
DeepSeekdeepseek-r1-0528· DeepSeekHugging Face LMArena Human Preference 140K

When PG cannot serializae two REPEATABLE_READ transactions?

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Metallama-4-maverick-17b-128e-instruct’s answer.
  1. User

    When PG cannot serializae two REPEATABLE_READ transactions?

  2. DeepSeek Assistant

    PostgreSQL's REPEATABLE READ isolation 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 concurrent REPEATABLE READ transactions 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;
        
    • 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;
        
    • 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) and products(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 products during A's execution):
        UPDATE products SET price = 300 WHERE id = 1; -- Runs after A's first SELECT.
        
    • Result: Transaction A sees a state where users (100) and products (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 SERIALIZABLE Isolation

    Upgrade to SERIALIZABLE to 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 40001 error (serialization failure).

    Summary

    AnomalyREPEATABLE READSERIALIZABLE
    Dirty ReadPreventedPrevented
    Non-Repeatable ReadPreventedPrevented
    Phantom ReadPreventedPrevented
    Write SkewAllowedPrevented
    Phantom Write SkewAllowedPrevented

    Use SERIALIZABLE when strict serializability is required. For REPEATABLE READ, handle write skew in application logic (e.g., explicit locks, SELECT FOR UPDATE).

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.