Concurrency Control in PostgreSQL

Hello! Welcome to our third lesson.

In our previous session, we established a theoretical foundation for PostgreSQL's isolation levels. We defined the common concurrency anomalies and discussed, at a high level, which isolation levels—Read Committed, Repeatable Read, and Serializable—prevent which anomalies.

Today, we transition from theory to practice. Our goal is to demonstrate concurrency anomalies in PostgreSQL and resolve them using appropriate isolation levels. We will intentionally create scenarios that trigger these anomalies, observe the incorrect state, and then apply the right isolation level or locking strategy to enforce correctness. This hands-on experience is crucial for building robust, high-concurrency applications, where subtle race conditions can lead to significant data integrity issues.

1. Setting Up the Environment

To observe these phenomena, you'll need two concurrent connections to a PostgreSQL database. We'll refer to them as Terminal 1 (T1) and Terminal 2 (T2).

The article "Different Isolation levels and anomalies in Postgres" provides a convenient way to set up a sandboxed PostgreSQL instance using Docker.

Different Isolation levels and anomalies in Postgres

Let's begin by setting up our practical environment. This article provides a Docker command to spin up a PostgreSQL instance and the schema for a simple accounts table that we will use for our first few demonstrations.

Please follow the instructions in the 'Pre-requisites' section. Specifically: Run the docker run... command to start a PostgreSQL container. Connect to the database using psql in two separate terminal windows (T1 and T2). In one of the terminals, create the accounts table using the CREATE TABLE statement provided.

Once you have two psql prompts connected to your database, you're ready to begin. All examples will involve running commands alternately in T1 and T2.

2. Anomalies at `Read Committed` (The Default)

PostgreSQL's default isolation level is Read Committed. As we learned, each statement gets its own database snapshot. This is great for concurrency but can lead to inconsistencies within a single transaction.

2.1 Non-Repeatable Read

A non-repeatable read occurs when a transaction reads the same row twice but sees different data because another transaction modified it in between.

Let's trigger this. We'll use the accounts table you just created.

Different Isolation levels and anomalies in Postgres

The article 'Different Isolation levels and anomalies in Postgres' has a clear, step-by-step example of a non-repeatable read. We will execute these steps now.

Follow the SQL commands under the 'Non-Repeatable Reads' section. Execute the steps for 'Tab1' in your T1 and 'Tab2' in your T2. Observe how the second SELECT in T1 sees the data committed by T2.

Walkthrough Summary:

T1: BEGIN;
T1: INSERT INTO accounts... (Insert two accounts for 'Jane'). COMMIT;
T1: BEGIN;
T1: SELECT * FROM accounts WHERE customer = 'Jane'; (You see the initial balances).
T2: BEGIN;
T2: UPDATE accounts SET amount = 900 WHERE id = 1;
T2: COMMIT;
T1: SELECT * FROM accounts WHERE customer = 'Jane'; (You now see the updated balance of 900, which is different from the first read).
T1: COMMIT;

Resolution: Upgrading to Repeatable Read

This anomaly is prevented by Repeatable Read because it uses a single snapshot for the entire transaction.

Now, let's re-run the scenario but with a higher isolation level in T1.

T1: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
T1: SELECT * FROM accounts WHERE customer = 'Jane'; (You see the current state).
T2: BEGIN;
T2: UPDATE accounts SET amount = 700 WHERE id = 1;
T2: COMMIT;
T1: SELECT * FROM accounts WHERE customer = 'Jane'; (You still see the old balance of 900, not 700. The read is repeatable).
T1: COMMIT;

2.2 Phantom Read

A phantom read occurs when a transaction re-runs a query and finds new rows that were inserted by another committed transaction.

Understanding Phantom Reads Problem with hands on examples

This video gives a great conceptual overview of the Phantom Read problem using a practical social media application scenario. It clearly illustrates how data inconsistency can manifest to the end-user.

Watch from the beginning to 11:03. Pay attention to how two concurrent API calls to publish a post can lead to a state where the API response contains a different number of posts than the value being stored in the stats table.

Now, let's reproduce a phantom read ourselves using the accounts table.

Walkthrough:

T1: BEGIN;
T1: SELECT SUM(amount) FROM accounts WHERE customer = 'Jane'; (Note the total).
T2: BEGIN;
T2: INSERT INTO accounts (id, customer, amount) VALUES (3, 'Jane', 300);
T2: COMMIT;
T1: SELECT * FROM accounts WHERE customer = 'Jane'; (You will now see a new, "phantom" row for Jane that wasn't there before).
T1: SELECT SUM(amount) FROM accounts WHERE customer = 'Jane'; (The sum is now different within the same transaction).
T1: COMMIT;

Resolution:
Just like non-repeatable reads, phantom reads are prevented by the REPEATABLE READ isolation level because the transaction's snapshot is fixed. If you were to re-run the above scenario with BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; in T1, the INSERT from T2 would be invisible to T1.

3. The Limits of `Repeatable Read`: Write Skew

Upgrading to Repeatable Read solves many problems, but it's not a silver bullet. Its major weakness is a subtle anomaly called Write Skew. This occurs when two transactions read the same data, but then make changes to different data (disjoint write sets). Since neither transaction updates a row the other is interested in, the database doesn't detect a direct conflict, yet a business invariant can still be violated.

This is arguably one of the most important anomalies to understand for building complex systems.

A Practical Guide to Taming Postgres Isolation Anomalies

The guide 'A Practical Guide to Taming Postgres Isolation Anomalies' has a superb, detailed walkthrough of a Write Skew with disjoint write sets. It uses a scenario where two users, Alice and Bob, try to claim a single 'free seat' by updating their own separate booking records.

Please read the subsection 'Write Skew With Disjoint Write Sets' within the 'Repeatable Read' section. Follow the logic of the example where both Alice and Bob read a total seat count of 2, and then both proceed to update their own booking to claim an extra seat. Notice that both transactions commit successfully, leading to an incorrect final state.

Walkthrough Summary (Conceptual):

Business Rule: A total of one free seat can be claimed between Alice and Bob. Their current total is 2 seats.
T1 (Alice, REPEATABLE READ): BEGIN;
T1: Reads the total seats for Alice and Bob. The sum is 2. The condition seat_count == 2 is met.
T2 (Bob, REPEATABLE READ): BEGIN;
T2: Reads the total seats for Alice and Bob. The sum is 2. The condition seat_count == 2 is also met.
T1: UPDATE alice_booking SET seats = 2; (Updates a row T2 didn't read directly).
T1: COMMIT;
T2: UPDATE bob_booking SET seats = 2; (Updates a row T1 didn't read directly).
T2: COMMIT;

Result: Both transactions commit. The total number of seats is now 4, violating the business rule that only one extra seat could be claimed. Repeatable Read failed to protect us because the write sets were disjoint (Alice updated her row, Bob updated his).

3.1 Resolution: `Serializable` Isolation

This is precisely the kind of anomaly that the SERIALIZABLE isolation level is designed to prevent. It works by tracking not just row-level dependencies, but also predicate dependencies (the logic of the WHERE clauses).

A Practical Guide to Taming Postgres Isolation Anomalies

Now, let's see how Serializable isolation fixes the Write Skew. The next section of the guide demonstrates this.

Read the subsection 'Write Skew With Disjoint Write Sets' within the 'Serializable' section. Follow the same scenario, but this time both transactions use ISOLATION LEVEL SERIALIZABLE. Observe that the second transaction to commit fails with a serialization failure. This is the database correctly preventing the anomaly.

Walkthrough Summary (SERIALIZABLE):

T1 (Alice, SERIALIZABLE): BEGIN;
T1: Reads the total seats.
T2 (Bob, SERIALIZABLE): BEGIN;
T2: Reads the total seats.
T1: UPDATE alice_booking SET seats = 2;
T1: COMMIT; (Succeeds).
T2: UPDATE bob_booking SET seats = 2;
T2: COMMIT;
Result: T2's COMMIT fails with ERROR: could not serialize access due to read/write dependencies among transactions.

The database detected that T1's committed write (to alice_booking) invalidated the premise of T2's read (the SUM). It correctly aborted T2 to maintain serializability. This demonstrates the critical takeaway: any code using Repeatable Read or Serializable transactions must include logic to catch serialization failures and retry the transaction.

4. Conclusion

Today, we put theory into practice by triggering and resolving key concurrency anomalies. This hands-on experience is vital for making informed architectural decisions when designing systems that rely on relational databases.

Key Takeaways:

Read Committed allows non-repeatable reads and phantom reads, which can be fixed by upgrading to Repeatable Read.
Repeatable Read provides a stable snapshot but is vulnerable to Write Skew when transactions modify different rows based on a shared, initial read.
Serializable is the ultimate safeguard, preventing even the most subtle write skews by detecting predicate-level conflicts.
The price for the safety of Repeatable Read and Serializable is the introduction of serialization failures, which require application-level retry logic. This is a fundamental design consideration. Choosing an isolation level is a direct trade-off between performance, blocking, and the complexity of your application's error handling logic.

Preview of the Next Lesson:

We have now thoroughly covered the "I" for Isolation in ACID. In our next lesson, we will turn our attention to the "D" for Durability. We will explore PostgreSQL's write-ahead logging (WAL), analyzing how its configuration parameters create a critical trade-off between durability guarantees and write performance, a key tuning area for any high-throughput system.