Transaction isolation levels
28/Nov 2017
Transaction isolation levels in relational databases. A reminder post.
ANSI/SQL standard isolation levels
We have 4 standard-defined transaction isolation levels:
- Read uncommited. Dirty reads - YES, Non-Repeatable reads - YES, Phantom reads - YES.
- Read commited. Dirty reads - NO, Non-Repeatable reads - YES, Phantom reads - YES.
- Repeatable read. Dirty reads - NO, Non-Repeatable reads - NO, Phantom reads - YES.
- Serializable. Dirty reads - NO, Non-Repeatable reads - NO, Phantom reads - NO.
Here YES
means may occur
, while NO
means should not occur at all
. So NO
here is more strong expression than YES
.
Different DBMS may make this YES
more restrictive. E.g. PostgreSQL does not allow Phantom Reads
on its Repeatable read
level.
See details here: PostgreSQL transaction isolation levels.
Read phenomena
The ANSI/ISO standard SQL 92 refers to three different read phenomena when Transaction 1 reads data that Transaction 2 might have changed:
- Dirty reads
- Non-Repeatable reads
- Phantom reads
Let’s consider them. Let’s operate with following database table users that has following data.
id | name | age |
---|---|---|
1 | John | 20 |
2 | Eva | 30 |
Dirty reads
A dirty read (aka uncommitted dependency) occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.
Transaction 1 | Transaction 2 |
---|---|
/* Query 1. Will read 20*/ |
|
select age from users where id=1 |
|
/* Query 2. No commit here. */ |
|
update users set age=50 where id=1 |
|
/* Query 1. Will read 50 */ |
|
select age from users where id=1 |
|
/* Lock-based dirty read. */ |
|
rollback |
We can see here that dirty read will read age value of 50
in the 2nd query, even if Transaction 2 will be rolled back afterwards.
Non-Repeatable reads
A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.
Transaction 1 | Transaction 2 |
---|---|
/* Query 1. */ |
|
select * from users where id=1 |
|
/* Query 2. */ |
|
update users set age = 50 where id = 1; |
|
commit; |
|
/* Query 1. */ |
|
select * from users where id=1; |
|
commit; |
During Transaction 1 Transaction 2 is started and committed, and it changes rows that should be returned as a result of Transaction 1. But Transaction 1 has already seen the different value before Transaction 2 starts. So what value should it return?
If transaction isolation level is set to either to Repeatable read
or Serializable
(levels at which Non-Repeatable reads
do not occur) - then Transaction 1 should return old values (where age is 20).
If transaction isolation level is set to either to Read uncommited
or Read commited
(levels at which Non-Repeatable reads
may occur) - then Transaction 1 may return new values (which incorporate changes made by Transaction 2, i.e. where age is 20).
Phantom reads
A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.
Transaction 1 | Transaction 2 |
---|---|
/* Query 1. */ |
|
select * from users |
|
where age between 10 and 30; |
|
/* Query 2. */ |
|
insert into users(id,name,age) |
|
values (3, 'Bob', 27); commit; |
|
/* Query 1. */ |
|
select * from users |
|
where age between 10 and 30; |
At the highest isolation level (Serializable
) Transaction 1 will return the same set of rows
(i.e. it will return 2 rows). While at lower transaction isolation levels (Uncommited read
, Commited read
, Non-repeatable read
)
it will return set of rows including those rows added with Transaction 2, i.e. it will return 3 rows.
JDBC
JDBC driver can have following transaction isolation levels:
Connection.TRANSACTION_NONE
- a constant indicating that transactions are not supportedConnection.TRANSACTION_READ_UNCOMMITTED
- read uncommited levelConnection.TRANSACTION_READ_COMMITTED
- read commited levelConnection.TRANSACTION_REPEATABLE_READ
- repeztable read levelConnection.TRANSACTION_SERIALIZABLE
- serializable level
See more details here: java.sql.Connection.