Isolation Level SERIALIZABLE: Full Isolation and Preventing Phantom Read

SERIALIZABLE is the highest transaction isolation level in PostgreSQL. This level guarantees that the results of parallel transactions will be exactly as if they were executed SEQUENTIALLY, one after another. No parallel execution anomalies (like Dirty Read, Non-Repeatable Read, Phantom Read) can happen here.

In plain English, SERIALIZABLE gives you total order and consistency between parallel transactions. It's like PostgreSQL saying: "Everybody get in line, folks!"

Why do you need the SERIALIZABLE level? Sometimes you just want to be 100% sure your data stays totally consistent, no matter what parallel changes are happening. Imagine a supermarket scene where cashiers are checking out customers at the same time. If nobody kept track of the order, you could end up with more goods leaving the store than were actually bought. With SERIALIZABLE, that just can't happen.

How to Set the SERIALIZABLE Level

To set the SERIALIZABLE isolation level, use this command:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

For example, let's create a transaction using this level:

BEGIN; -- Start the transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Set isolation level
SELECT * FROM products WHERE category = 'Electronics'; -- Get product list
UPDATE products SET stock = stock - 1 WHERE product_id = 123; -- Update stock
COMMIT; -- Commit changes

Case: Booking Movie Theater Tickets

Let's look at a real-world example where SERIALIZABLE is a must. Imagine you're building an online movie ticket booking system. Your users pick seats, and you want to guarantee that the same seat can't be bought by two customers at the same time.

First, let's create a table for seats:

CREATE TABLE seats (
    seat_id SERIAL PRIMARY KEY,
    is_booked BOOLEAN DEFAULT FALSE
);

Now let's add a few seats:

INSERT INTO seats (is_booked) VALUES (FALSE), (FALSE), (FALSE);

Here's an example transaction with SERIALIZABLE.

This is how you can safely book a seat:

BEGIN; -- Start transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- SERIALIZABLE isolation level

-- Check if the seat is free
SELECT is_booked FROM seats WHERE seat_id = 1;

-- Book the seat
UPDATE seats SET is_booked = TRUE WHERE seat_id = 1;

COMMIT; -- Confirm booking

If a second parallel transaction tries to book the same seat, PostgreSQL won't let any confusion happen and will throw a serialization conflict error.

Preventing Phantom Read

Now let's break down "phantom reads," the thing we really want to avoid. Phantom Read happens when a transaction sees data changes (like new rows) made by another transaction during its own work. For example, your transaction expects a certain number of rows, but suddenly another transaction adds or deletes rows, changing your results.

Here's an example:

Data before transactions start

Transaction 1

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Count users with balance over 400
SELECT COUNT(*) FROM accounts WHERE balance > 400;

-- Expecting result: 2 (Alice and Bob)

Transaction 2

In another session, a parallel transaction runs:

BEGIN;
INSERT INTO accounts (id, balance, user) VALUES (3, 700, 'Charlie');
COMMIT;

Back to Transaction 1

-- Repeat the query
SELECT COUNT(*) FROM accounts WHERE balance > 400;

Now, if you don't use SERIALIZABLE, the result will be 3 instead of 2, since Charlie was added during Transaction 1. That's a Phantom Read.

But with SERIALIZABLE, PostgreSQL guarantees that Transaction 1 won't see Charlie, because its "view of the world" is frozen at the moment the transaction started.

Features and Limitations of SERIALIZABLE Level

We've figured out how SERIALIZABLE helps you get perfect isolation. But what in this world is perfect without some downsides? Let's be real.

Performance Hit
SERIALIZABLE needs way more resources than READ COMMITTED or REPEATABLE READ. Why? PostgreSQL has to emulate sequential execution, tracking all possible conflicts between transactions.

Serialization Errors
If PostgreSQL finds it can't execute transactions in the "perfect order," it throws a serialization error (serialization_failure) and rolls back the transaction.

Example error:

ERROR: could not serialize access due to concurrent update

To handle this, you can just retry the transaction after a failure:

DO $$
DECLARE
    done BOOLEAN := FALSE;
BEGIN
    WHILE NOT done LOOP
        BEGIN
            -- Start transaction
            BEGIN;
            SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

            -- Do your stuff
            UPDATE accounts SET balance = balance - 100 WHERE id = 1;

            -- Commit changes
            COMMIT;
            done := TRUE; -- Exit loop if all good
        EXCEPTION WHEN serialization_failure THEN
            ROLLBACK; -- Rollback on error
        END;
    END LOOP;
END;
$$;

This is the usual approach in systems using SERIALIZABLE.

When Should You Use SERIALIZABLE?

This isolation level makes sense when the cost of a mistake is super high:

• Financial transactions, like payment processing or bonus distribution.
• Inventory management systems, to avoid duplicate orders.
• Online bookings, where you really need to avoid resource booking conflicts.

If you're building a system where data must be 100% consistent and performance isn't the top priority, SERIALIZABLE will be your best buddy.