Intro to Transaction Isolation Levels

Imagine you’re working at a cafe, where one waiter is checking the cake stock in the kitchen, and another is filling out a cake order for a new customer. In a perfect world, they’d both be working with the same info about how many cakes are left, so you don’t get weird mistakes like “double booking.” But in the real world, parallel operations can cause some headaches.

Here are three main gotchas that can happen:

1. Dirty Read: One query sees changes made by another, but those changes aren’t committed yet. If those changes get rolled back, the first query ends up looking as naive as a student at their first job interview.

2. Non-Repeatable Read: One query reads the same data twice, but in between, someone else changes it. It’s like showing up at the train station, checking the schedule, coming back a minute later—and finding out your train got canceled. Or your ticket got bought by someone else while you were digging for cash :)

3. Phantom Read: One query sees a subset of rows, but between two runs, someone adds new rows that change the result. It’s like your company lost a tender, but then all the bids except yours and the mayor’s wife’s bid got canceled.

Transaction Isolation Levels

Now that we know about the problems, let’s check out the tool PostgreSQL gives us to deal with them—transaction isolation levels. It’s like setting up rules for how parallel transactions interact. The higher the isolation level, the more guarantees that transactions won’t mess with each other. But you pay for that with slower “service speed”—aka performance.

Isolation Levels in PostgreSQL

1. Read Uncommitted:

   • Lets you read changes that haven’t been committed yet (yep, that’s dirty read in all its glory).
   • In PostgreSQL, this level is actually implemented as Read Committed, so it’s not really supported in pure form. PostgreSQL refuses to implement it because it’s just too unreliable.

2. Read Committed:

   • Prevents dirty reads.
   • The transaction only sees data that was committed at the time its command runs.
   • But Non-Repeatable Read and Phantom Read can still happen.

3. Repeatable Read:

   • Guarantees that the data you read stays the same during your transaction.
   • Prevents dirty reads and non-repeatable reads.
   • But phantom rows can still sneak in.

4. Serializable:

   • Guarantees that transactions run as if they were executed one after another, in sequence.
   • Prevents all three problems: dirty reads, non-repeatable reads, and phantom rows.
   • The strictest—and slowest—level of isolation.

Why Does Transaction Isolation Matter?

Now picture a database for an online store, where a thousand users are all trying to place orders at the same time. Without a well-tuned isolation level, you could run into a ton of conflicts: from “missing” products to duplicate orders.

Picking the right isolation level helps you handle concurrent transactions, balancing performance and data integrity. For example:

• Analytics systems usually go for minimal isolation levels (like Read Committed), since perfect data accuracy isn’t always a must.
• Financial systems prefer Serializable, to avoid calculation errors or duplicate operations.

Examples of Using Isolation Levels

1. Read Committed

This level makes sure you never read data that could have been rolled back.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;

-- Read account data.
SELECT balance FROM accounts WHERE account_id = 1;

-- If another transaction updates the balance, you’ll see the new data right away.
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

COMMIT;

2. Repeatable Read

This level guarantees that if you read data, it’ll stay the same for you throughout the transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

-- Read account data.
SELECT balance FROM accounts WHERE account_id = 1;

-- Even if another transaction updates this balance, you won’t see the change.
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

COMMIT;

3. Serializable

At this level, your transaction acts like it’s the only one in the system.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;

-- Read account data.
SELECT balance FROM accounts WHERE account_id = 1;

-- Any other transaction trying to change this data will be blocked until yours finishes.
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

COMMIT;

How to Pick an Isolation Level?

Picking an isolation level depends on what you need:

• If speed matters more than accuracy, and you’re cool with phantom rows—go with Read Committed.
• If accuracy is important, but you still want good performance—use Repeatable Read.
• If you need 100% data correctness, even if it’s slower—Serializable is your friend.

Heads up! Strict isolation levels can cause locks and slow down your system. The smart move is to find a balance between performance and data consistency.

2

Task

SQL SELF, level 39, lesson 4

Locked

Set the transaction isolation level

Set the transaction isolation level

Open

1

Survey/quiz

Intro to Transactions, level 39, lesson 4

Unavailable

Intro to Transactions

Intro to Transactions

Open