Adam's Perspective

I looked at SQL queries through the lens of isolation levels ever since my brother showed me that concept about 16 years ago. I’ve noticed many, many senior devs are unaware of this fundamental database feature. Here is a short primer on isolation levels that every backend developer should read.

The core problem

Suppose we have two concurrent SQL connections and this situation happens:

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- hasn't committed yet...
-- Transaction B
BEGIN;
SELECT balance FROM accounts WHERE id = 1;

Question: What balance will the transaction B see?

Answer: It depends! We must consider at least:

• The current isolation level. MySQL’s default is REPEATABLE READ.
• Table engine. Isolation levels affect InnoDB but not MyISAM since MyISAM doesn’t support transactions.

The four isolation levels

The SQL 1992 document defines four transaction isolation levels. While this article focuses on MySQL, the same ideas apply to other major databases.

A few important notes about MySQL before we dive in. First, all examples in this section assume InnoDB tables. Second, you might think “oh, locking surely makes this slow.” While that may be true, InnoDB uses Multi-Version Concurrency Control (MVCC) so it’s not as bad. And now, let’s dive right in:

READ UNCOMMITTED

A transaction can see changes made by other transactions before they commit. The other transaction may roll back and invalidate the data you’ve just read. This is called a dirty read and it’s very risky:

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- hasn't committed yet...
-- Transaction B (READ UNCOMMITTED)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- sees the deducted balance, even though A hasn't committed
-- Transaction A
ROLLBACK;
-- Transaction B (READ UNCOMMITTED)
SELECT balance FROM accounts WHERE id = 1;
-- sees the original, undeducted balance again.

READ COMMITTED

A transaction only sees data that has been committed. No dirty reads. If another transaction is in the middle of a change, you won’t see it until they commit. This is the default in PostgreSQL and Oracle:

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- hasn't committed yet...
-- Transaction B (READ COMMITTED)
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Still sees the original, undeducted balance
-- Transaction A
COMMIT;
-- Transaction B (READ COMMITTED)
SELECT balance FROM accounts WHERE id = 1;
-- sees the deducted balance.

This sounds reasonable at first – transaction B only sees the atomic changes committed by other transactions. However, sometimes the atomicity of the transaction depends on the underlying data not changing between your BEGIN and COMMIT. READ COMMITTED won’t give you that. Transaction B gets different results for the same query even though it didn’t execute any UPDATEs.

REPEATABLE READ

Once your transaction reads a row, it will see the same value for that row for the rest of the transaction, even if another transaction changes and commits it. This is MySQL’s default:

-- Transaction B (REPEATABLE READ)
BEGIN
SELECT balance FROM accounts WHERE id = 1;
-- returns 1000
-- Transaction A
BEGIN
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- balance is now 500
-- hasn't committed yet...
-- Transaction B (REPEATABLE READ)
SELECT balance FROM accounts WHERE id = 1;
-- still returns 1000
-- Transaction B sees a stable snapshot

MySQL also protects you from another gotcha scenario called phantom reads. While each read row remains stable once you’ve read it, your repeated SELECT might match a different set of rows.

-- Transaction A (REPEATABLE READ)
SELECT * FROM orders WHERE customer_id = 42;
-- returns 3 rows
-- Transaction B inserts a new order for customer 42 and commits
SELECT * FROM orders WHERE customer_id = 42;
-- in MySQL, it's 3 rows
-- in standard SQL, this could return 4 rows (a phantom)

SERIALIZABLE

The strictest level. It has the same guarantees as REPEATABLE READ and it also places shared locks on the data you read in a transaction, preventing other transactions from writing:

-- A starts (SERIALIZABLE)
BEGIN;
SELECT quantity FROM products WHERE id = 5;
-- A reads 1, shared lock placed automatically
-- B starts (SERIALIZABLE)
BEGIN;
SELECT quantity FROM products WHERE id = 5;
-- B reads 1, shared lock placed automatically
-- A tries to decrement
UPDATE products SET quantity = quantity - 1 WHERE id = 5;
-- A needs exclusive lock, but B holds a shared lock
-- A waits...
-- B tries to decrement
UPDATE products SET quantity = quantity - 1 WHERE id = 5;
-- B needs exclusive lock, but A holds a shared lock
-- DEADLOCK — MySQL rolls back B
-- A proceeds
COMMIT; -- A
-- quantity is now 0
-- in REPEATABLE READ, the quantity would be -1

SERIALIZABLE trades performance for correctness. Locking can mean waiting, but it can also prevent a negative inventory or an overdraft financial account.

How to set the isolation level

In MySQL, you can set the isolation level per transaction, per session, or globally:

-- for the next transaction only
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- for the entire current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- globally (affects new sessions, not existing ones)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

To check your current level:

SELECT @@transaction_isolation;

A summary

*In standard SQL, phantoms are possible at REPEATABLE READ. MySQL’s InnoDB prevents them using next-key locking.

And now you know more about the isolation levels!