SQL Phenomena

· 576 words · 3 minute read

When using PostgreSQL, certain phenomena can occur due to concurrent transactions. Below are some potential issues that might arise:

  • Dirty Read

    A transaction reads data written by a concurrent uncommitted transaction.

    Example:

    AccountID Balance
    1 1000

    Two transactions are running concurrently:

    • Transaction A: Updates the balance of Account 1 but hasn’t committed yet.
    • Transaction B: Reads the balance of Account 1, expecting it to be the committed amount.

    Steps Leading to a Dirty Read Anomaly:

    1. Transaction A starts and decreases the balance of Account 1 by 200 but does not commit.

       UPDATE Accounts SET Balance = 800 WHERE AccountID =  ;  -- Temporary Balance: 800 (uncommitted)

    2. Transaction B reads the balance of Account 1 while Transaction A’s update is still uncommitted.

      SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Result: 800 (uncommitted value from Transaction A)

    3. Transaction A encounters an issue and performs a rollback, undoing its changes.

      ROLLBACK;  -- Balance reverts back to 1000

    4. Now, Transaction B has read a balance of 800, which is incorrect, as it’s based on an uncommitted and rolled-back transaction from Transaction A. The correct balance is actually 1000.

  • Non Repeatable Read

    A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).

    Example:

    AccountID Balance
    1 1000

    Two transactions are running concurrently:

    • Transaction A: Reads the balance of Account 1 twice, expecting it to remain the same.
    • Transaction B: Updates the balance of Account 1 after Transaction A’s first read but before its second read.

    Steps Leading to a Non-Repeatable Read Anomaly:

    1. Transaction A starts and reads the Balance of Account 
      SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Result: 1000
    2. Transaction B starts, updates the balance of Account 1, and commits. 
       UPDATE Accounts SET Balance = 1200 WHERE AccountID = 1;  -- New Balance: 1200
    3. Transaction A reads the Balance of Account 1 again, expecting it to be the same as before. 
       SELECT Balance FROM Accounts WHERE AccountID = 1;  -- Result: 1200

  • Phantom Read

    A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

    Example:

    BookID Genre
    1 Fiction
    2 Fiction
    3 Mystery
    4 Fiction

    Two transactions run concurrently:

    • Transaction A: Counts the number of “Fiction” books available and plans to add a new one if there are fewer than five.
    • Transaction B: Inserts a new “Fiction” book into the database.

    Steps Leading to a Phantom Read Anomaly

    1. Transaction A starts and counts the number of books in the “Fiction” genre. 
      SELECT COUNT(*) FROM Books WHERE Genre = 'Fiction';  -- Result: 3
    2. Transaction B inserts a new book into the “Fiction” genre and commits. 
      INSERT INTO Books (BookID, Genre) VALUES (5, 'Fiction');
    3. Transaction A re-runs its query to count the number of “Fiction” books to check before adding its new book. 
      SELECT COUNT(*) FROM Books WHERE Genre = 'Fiction';  -- Result: 4 (including new row added by Transaction B)
    4. Transaction A now sees 4 books in the “Fiction” genre instead of 3, which means it may decide differently based on this phantom row. If it decides to add a book only if there are fewer than five, it will see a different result than expected.

  • Serialization Anomaly

    The result of successfully committing a group of transactions is inconsistent with all possible orderings of running those transactions one at a time.

sql