@Transactional Part 3: Isolation Levels

Part 2 covered propagation: whether methods share a transaction. This last part covers the other main setting of @Transactional, isolation. Part 1's critical section showed the danger: several transactions touching the same rows at the same time. Isolation decides how much they see of each other.

The path through this part: the three problems that concurrent transactions can cause, the locks databases use, the four isolation levels and which problems each one prevents, then real runs of two transactions on H2 to watch the problems happen.

The isolation level decides how the changes made by one transaction are visible to other transactions running in parallel.

@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)

There are four levels:

Isolation level	Dirty read	Non-repeatable read	Phantom read	Concurrency
READ_UNCOMMITTED	possible	possible	possible	highest
READ_COMMITTED	no	possible	possible
REPEATABLE_READ	no	no	possible
SERIALIZABLE	no	no	no	lowest

If you do not set one, the database's default applies (Isolation.DEFAULT). It varies: PostgreSQL, Oracle and SQL Server default to READ_COMMITTED, MySQL's InnoDB to REPEATABLE_READ. Check the documentation of the database you use.

Higher concurrency sounds better, so why not always use READ_UNCOMMITTED? Because each step up the table removes a problem, at the cost of concurrency. First, the three problems.

The three problems

Dirty read

Transaction A reads data that transaction B has changed but not committed. If B then rolls back, A has read a value that never really existed.

Time	Transaction A	Transaction B	Row 123 in the DB
T1	begin	begin	status: free
T2		update row 123 to booked	status: booked (not committed)
T3	read row 123: booked		status: booked (not committed)
T4		rollback	status: free

A acted on "booked", which was rolled back: a dirty read.

Non-repeatable read

Transaction A reads the same row twice and gets different values, because another transaction changed it and committed in between.

Time	Transaction A	Row 1 in the DB
T1	begin	status: free
T2	read row 1: free	status: free
T3		another transaction sets booked and commits
T4	read row 1: booked	status: booked
T5	commit

Phantom read

Transaction A runs the same query twice and gets different rows, because another transaction inserted (or deleted) matching rows and committed.

Time	Transaction A	Rows in the DB
T1	begin	1 (free), 3 (booked)
T2	read rows where 0 < id < 5: 1, 3
T3		another transaction inserts row 2 and commits
T4	read rows where 0 < id < 5: 1, 2, 3
T5	commit

Row 2 is a "phantom".

Locks

The classic way to implement isolation is with two kinds of lock:

• Shared lock (S), also called a read lock.
• Exclusive lock (X), also called a write lock.

A transaction holds	Can another take a shared lock?	Can another take an exclusive lock?
Shared lock	Yes	No
Exclusive lock	No	No

Several transactions can read a row together, but while one holds an exclusive lock, nobody else can read or write it.

The four levels

Under the classic locking model:

Isolation level	Locking
Read uncommitted	Read: no lock. Write: exclusive lock until the end of the transaction (writes are always locked: two transactions can never change the same row at once, at any level).
Read committed	Read: shared lock, released as soon as the read is done. Write: exclusive lock until the end of the transaction.
Repeatable read	Read: shared lock, kept until the end of the transaction. Write: exclusive lock until the end of the transaction.
Serializable	Like repeatable read, plus a range lock, kept until the end of the transaction.

READ_UNCOMMITTED: solves nothing

Reads take no lock, so a reader sees changes other transactions have not committed: all three problems are possible. Concurrency is at its highest, since readers never wait. Use it only when those problems cannot hurt, for example an application that only reads static data.

READ_COMMITTED: no dirty reads

A writer keeps its exclusive lock until commit or rollback, so other transactions cannot read the uncommitted change: no dirty read.

But a reader releases its shared lock right after reading. Another transaction can then update the row (or insert rows) and commit, and the next read sees the change: non-repeatable and phantom reads remain.

REPEATABLE_READ: no non-repeatable reads

A reader keeps its shared lock until the end of its transaction, so nobody can change the rows it read; reading them again gives the same values: no dirty read, no non-repeatable read.

But the locks cover only the rows that exist. Another transaction can insert a new row in the queried range (row 2 between 1 and 4) and commit: phantom reads remain.

SERIALIZABLE: nothing possible

Repeatable read plus a range lock: the lock covers the whole queried range (0 < id < 5), including gaps, so no transaction can insert into it until A finishes: no phantoms. Concurrency is at its lowest.

See them happen

Two transactions run in parallel threads on H2. Transaction A reads the status of car 1001; transaction B changes it. Each method prints what it sees:

@Service
public class CarService {

    @Autowired
    JdbcTemplate jdbcTemplate;

    String status() {
        return jdbcTemplate.queryForObject("select status from car where id = 1001", String.class);
    }

    // transaction A: read, wait a second, read again
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void readTwiceReadCommitted() {
        System.out.println("T2 read: " + status());
        sleep(1000);
        System.out.println("T4 read: " + status());
    }

    // transaction B: book the car and commit, while A is waiting
    @Transactional
    public void bookAndCommit() {
        sleep(400);
        jdbcTemplate.update("update car set status = 'booked' where id = 1001");
    }
}

Non-repeatable read under READ_COMMITTED:

[A, READ_COMMITTED] T2 read: free
[B] T3 updated to booked and committed
[A, READ_COMMITTED] T4 read: booked

The same row, read twice in one transaction, gave two different values.

The same run under REPEATABLE_READ (only the annotation changed):

[A, REPEATABLE_READ] T2 read: free
[B] T3 updated to booked and committed
[A, REPEATABLE_READ] T4 read: free

B's change committed, but A keeps seeing the value it read first, until A's transaction ends.

Dirty read under READ_UNCOMMITTED. Now B books the car, waits, and rolls back; A reads in between:

[B] T2 updated to booked, not committed
[A, READ_UNCOMMITTED] T3 read: booked
[B] T4 rollback
after rollback: free

A read booked, a value that was never committed and no longer exists.

The same under READ_COMMITTED:

[B] T2 updated to booked, not committed
[A, READ_COMMITTED] T3 read: free
[B] T4 rollback

No dirty read. Note that A did not wait for B's exclusive lock: H2, like most modern databases, uses MVCC (explained at the end of this chapter) and returned the last committed value instead.

Which to choose

In practice the choice is usually between READ_COMMITTED and REPEATABLE_READ: both solve dirty reads and still allow reasonable concurrency. Ask whether a non-repeatable read would break your use case. If not, READ_COMMITTED; if it would, REPEATABLE_READ.

Who does the locking?

You write only the SQL (SELECT * FROM car WHERE id = 1). Taking and releasing locks is done inside the database, by its own transaction manager, according to the isolation level your transaction asked for. It is invisible at the application level.

Many modern databases (PostgreSQL, MySQL InnoDB, Oracle, H2) use MVCC (multi-version concurrency control) instead of plain read locks. Each transaction reads a snapshot of committed data, so readers and writers usually do not block each other. Under READ_COMMITTED, a reader of a row being changed gets its last committed value instead of waiting. The guarantees in the first table still hold; they are the minimum the SQL standard requires, and some databases do better (in H2 and MySQL InnoDB, REPEATABLE_READ also prevents phantoms for ordinary reads).

Next: Transactions keep data consistent. The next two chapters are about responsiveness instead: running work in the background with @Async.

Check yourself

1. What does an isolation level control?
2. What isolation applies if you do not set one?
3. Define dirty read, non-repeatable read and phantom read.
4. Can another transaction take a shared lock while you hold a shared lock? An exclusive lock?
5. Why does READ_UNCOMMITTED allow all three problems? When is it acceptable?
6. How does READ_COMMITTED prevent dirty reads, and why does it still allow non-repeatable reads?
7. Why does REPEATABLE_READ still allow phantom reads?
8. How does SERIALIZABLE prevent phantoms, and at what cost?
9. How do you choose between READ_COMMITTED and REPEATABLE_READ?
10. Where are the locks actually taken and released?

Answers

1. How changes made by one transaction are visible to other transactions running in parallel.
2. The database's default (Isolation.DEFAULT), e.g. read committed in PostgreSQL, repeatable read in MySQL InnoDB.
3. Dirty: reading another transaction's uncommitted change. Non-repeatable: the same row read twice gives different values. Phantom: the same query returns different rows.
4. Yes. No.
5. Reads take no locks, so uncommitted and changing data is visible. Only when those problems cannot matter, e.g. read-only static data.
6. Writers hold exclusive locks until commit, so uncommitted data cannot be read. Readers release shared locks immediately, so others can change the row between two reads.
7. Its locks cover only existing rows; new rows can be inserted into the range.
8. With range locks that block inserts into the queried range; concurrency drops to its lowest.
9. If a non-repeatable read would break your logic, REPEATABLE_READ; otherwise READ_COMMITTED.
10. Inside the database, by its own transaction manager, according to the requested isolation level.