Transaction with Isolation Level

Introduction

This is a concept created by Postgres as a set of behavior rules to decide whether other people can see or edit data when someone is modifying it.
The isolation level only makes sense and only works when accompanied by a Transaction. This is the factor that ensures Isolation, helping to decide how parallel transactions handle operations independently of each other.
When you execute a single statement like UPDATE products SET price = 100, Postgres activates Autocommit mode to automatically wrap the statement with BEGIN...COMMIT. It automatically creates a Transaction for extremely fast execution.

Levels

There are currently 4 levels ranging from the most relaxed to the strictest. The stricter the level, the more accurate the data, but the system runs slightly slower.

Read Uncommitted
- This is the most relaxed level, allowing you to see what others are drafting even if they have not yet committed.
- Because of that characteristic, using it is very dangerous as it causes Dirty Read errors. Therefore, when you use this level in Postgres, the system automatically upgrades it to the Read Committed level.
Read Committed
- This is the default level of Postgres, allowing you to see data only when others have committed. If they are making ongoing changes, you will only see the old version before the modification.
- The problem that may be encountered is Non-repeatable Read.
  - In the same transaction, you read that row value for the first time and get result A.
  - Right after that, someone else modifies it to B in another transaction.
  - When you read it again for the second time, you get result B.
  - As a result, two reads within the same transaction produce two different results.
Repeatable Read
- This is a consistent read mode that freezes the data at the moment you start the transaction. Throughout your work process, no matter how many times others modify and save the data, you still only see a single version of the data exactly as it was at the beginning.
- The mechanism of operation is that Postgres creates a Snapshot at the start of the Transaction so that you process based on the data of that Snapshot.
- Using this level also prevents the Phantom Read phenomenon, which occurs when another person inserts a new row into the table while the Transaction is processing. You will not see that new row until the Transaction ends.
Serializable
- This is the strictest level. Transactions are processed by the system as if there were only a single user using the database at any given time.
- If Postgres detects that your action and another person's action have the potential to cause data inconsistency or deviation, Postgres will directly abort the transaction of one of the two users and report an error. The canceled user will have to restart from the beginning.
- SERIALIZABLE doesn't exist just because some problems have "no other way." It exists because there are highly complex business rules where self-designing correct locking mechanisms for every single scenario becomes difficult, error-prone and hard to maintain.
- In other words, the greatest value of SERIALIZABLE is that PostgreSQL automatically detects and prevents dependency cycles between transactions, saving you from having to prove that your entire locking strategy is correct across all concurrent scenarios. This is especially useful when business rules span multiple tables, multiple queries and dynamic conditions, where there's no single row or simple constraint to rely on.
- This should only be used when building highly sensitive systems such as finance and banking, where even a small logical error is not allowed to occur.

Comparison

This is the summary result of the Isolation Levels Comparison

| Isolation Level          | Can see uncommitted changes? | Data changes between 2 reads? | Performance             |
| :----------------------- | :--------------------------- | :---------------------------- | :---------------------- |
| Read Committed (Default) | No                           | Yes, results can vary         | Very Fast               |
| Repeatable Read          | No                           | No, always consistent         | Moderate                |
| Serializable             | No                           | No, always consistent         | Slower, high retry risk |

And this is the result of the Transaction Concurrency Behavior, which occurs when two Transactions modify the same row simultaneously

| Isolation Level | Does Tx2 have to wait for Tx1? | What happens after Tx1 COMMIT?                                     |
| :-------------- | :----------------------------- | :----------------------------------------------------------------- |
| Read Committed  | Yes, queues up to wait         | Tx2 continues to run successfully on the new data from Tx1         |
| Repeatable Read | Yes, queues up to wait         | Tx2 is aborted and throws a data conflict error                    |
| Serializable    | Yes, queues up to wait         | Tx2 is aborted, or aborted earlier if read-write logic is violated |

In most cases, you only need to use the default Read Committed level, which can be combined with Row level locks to sufficiently resolve issues and optimize performance.

Detail

First, create the tables as follows

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    rank VARCHAR(20) DEFAULT 'MEMBER', 
    dob DATE NOT NULL                 
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
 user_id INT REFERENCES users(id),
    product_id INT REFERENCES products(id),
    quantity INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE gifts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    stock INT NOT NULL
);

CREATE TABLE gift_claims (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    gift_id INT REFERENCES gifts(id),
    claimed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP DEFAULT NULL 
);

READ COMMITTED

This is a commonly used example in ordering systems, with the requirement to properly deduct the stock quantity to avoid overselling.

-- Transaction 1 and 2
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;

SELECT stock FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;

You can see that the three BEGIN commands above all yield the same result.
- If you use READ UNCOMMITTED, Postgres automatically upgrades the level to READ COMMITTED.
- The second line is the full syntax for READ COMMITTED.
- If you only use BEGIN, the default level is also READ COMMITTED.
This is a common practice combined with the Row level lock FOR UPDATE to handle ordering Transactions one by one, preventing incorrect stock deductions.

REPEATABLE READ

Used in data statistics.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SELECT 
    TO_CHAR(created_at, 'YYYY-MM') AS month,
    COUNT(id) AS total_orders,
    SUM(quantity) AS total_products_sold,
    SUM(total_amount) AS total_revenue
FROM orders
GROUP BY TO_CHAR(created_at, 'YYYY-MM')
ORDER BY month DESC;

It is necessary to use REPEATABLE READ because it creates a Snapshot at that moment to perform statistics.
If you use READ UNCOMMITTED instead, while statistics are being processed and an order is placed, the SELECT data might be incorrect, because data could be inserted into this table but not yet inserted into another table.

SERIALIZABLE

This is an example related to aggregate constraints. Suppose the system requires that each VIP level user must have a total payment of at least 10,000 last year and if this month is their birthday month, they will receive a gift.

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Statement 1
WITH user_qualification AS (
    SELECT 1
    FROM users u
    WHERE u.id = 1
      AND u.rank = 'VIP'
      AND EXTRACT(MONTH FROM u.dob) = EXTRACT(MONTH FROM CURRENT_DATE)
      AND COALESCE(
          (SELECT SUM(total_amount) 
           FROM orders 
           WHERE user_id = u.id 
             AND EXTRACT(YEAR FROM created_at) = EXTRACT(YEAR FROM CURRENT_DATE) - 1
          ), 0
      ) >= 10000.00
)
UPDATE gifts 
SET stock = stock - 1 
WHERE id = 1 
  AND stock > 0
  AND EXISTS (SELECT 1 FROM user_qualification);

-- Statement 2
WITH check_already_claimed AS (
    SELECT 1 
    FROM gift_claims 
    WHERE user_id = 1 
      AND gift_id = 1
      AND deleted_at IS NULL
      AND EXTRACT(MONTH FROM claimed_at) = EXTRACT(MONTH FROM CURRENT_DATE)
      AND EXTRACT(YEAR FROM claimed_at) = EXTRACT(YEAR FROM CURRENT_DATE)
)
INSERT INTO gift_claims (user_id, gift_id)
SELECT 1, 1
WHERE NOT EXISTS (SELECT 1 FROM check_already_claimed);

Statement 1: Used to deduct the quantity in the gifts table when meeting the condition that the user has a VIP level, spent at least 10,000 last year and has their birthday in the current month.
Statement 2: Inserts a row into the gift_claims table with the condition that the user has not claimed the gift this year.
You can see that with so many conditions, it is impossible to describe them using only the schema. If you use READ COMMITTED/REPEATABLE READ, when two Transactions are somehow executed at the same time and the logical verification conditions are satisfied, the Database will record two rows requesting gift claims.
If you use SERIALIZABLE, Postgres activates SSI (Serializable Snapshot Isolation) technology.
- It does not use physical locks to lock the entire table, but uses an invisible lock called SIREAD Lock to track the data context.
- Even if two Transactions meet the conditions and run at the same time, Postgres will still detect data conflicts, as data changes by the Transactions would lead to Write Skew.
- Example:
  - The current data state is A.
  - Two Transactions run simultaneously.
  - After Transaction 1 finishes running, it changes the state to B.
  - If you use SERIALIZABLE, Postgres only allows Transaction 2 to execute with the initial state A. If it realizes after Transaction 1 ends that the state has changed to B, the modifications of Transaction 2 could lead to unwanted erroneous results, so Transaction 2 will be blocked.