The labs on this site need JavaScript to run. The same content is written out below.

Learn › Database › Transactions and locking

Isolation levels

How does data that another transaction is changing look to me?

While transaction A runs a query, transaction B changes the same data. Depending on the isolation level, A may or may not see the changes made by B.

Checked against real execution · Ran all 12 combinations on MySQL 8.4.11 with two sessions and checked the results against them

Concepts in this lab

Transaction isolation level

A level that sets how much of each other's changes concurrent transactions can see. There are four levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. The later levels block more of the effects that transactions have on each other, but the amount of work that can be handled at the same time goes down. The default is REPEATABLE READ in MySQL (InnoDB) and READ COMMITTED in PostgreSQL and Oracle.

Dirty read

A situation in which a transaction reads changes that another transaction has not committed yet. If that other transaction rolls back, the reader has read data that never existed. It does not occur at the READ COMMITTED isolation level or higher.

Non-repeatable read

A situation in which a transaction reads the same row twice and gets two different results, because another transaction changed the value and committed in between. It can occur at READ COMMITTED and does not occur at REPEATABLE READ.

Phantom read

A situation in which a transaction runs a query with the same condition twice and gets a different number of rows, because another transaction inserted or deleted rows in between. It differs from a non-repeatable read in that values do not change. Instead, rows that did not exist appear, or rows that existed disappear.

MVCC (multiversion concurrency control)

A method that keeps earlier versions of data when the data changes, so that a reading transaction can see the version that matches the time of its snapshot. The snapshot time depends on the isolation level: in MySQL, REPEATABLE READ takes it at the first read of the transaction, and READ COMMITTED takes a new one each time a statement runs. Reads do not wait for writes and writes do not wait for reads, so concurrent performance is high. MySQL InnoDB, PostgreSQL, and Oracle all use this method.

Undo log and redo log

An undo log records data as it was before a change. It is used to roll back a transaction, and in InnoDB it is also used to show past versions to other transactions (MVCC). A redo log records the changes themselves. If a failure occurs before committed changes are fully written to the data files, the changes are applied again at restart to recover them.

Shared locks and exclusive locks

A shared lock (S) is a lock for reading, and several transactions can hold it at the same time. An exclusive lock (X) is a lock for writing, and only one transaction can hold it. While data has an exclusive lock, no other transaction can take a shared lock or an exclusive lock on it.

Try these

• Default settings — READ COMMITTED. A runs the same query twice and gets different values.
• Raising it to REPEATABLE READ — The second query sees the same value as the first.
• Reading an uncommitted value — READ UNCOMMITTED. A reads a value that will be rolled back and disappear.
• The same situation, READ COMMITTED — The value from before the commit is not visible.
• A row that did not exist appears — READ COMMITTED. The row count goes up in the second query.
• SERIALIZABLE — There are no anomalies, but B waits for A.

Running with the default settings

1. A starts a transaction.
2. This is the first query of A. The balance is 1000.
3. B changes the balance to 500. The query of A took no locks, so B does not have to wait.
4. B commits. The committed balance in the DB is now 500.
5. This is the second query of A. READ COMMITTED reads the latest data committed at the time of each query. The same query in the same transaction now returns 500 instead of 1000. This is a non-repeatable read.
6. A commits.

Result of this run

• Isolation level READ COMMITTED: first query of A 1000, second query 500. Anomaly: Non-repeatable Read.
• If a transaction follows a flow such as "query, calculate, query again", the value can change in the middle and throw off the calculation. It is prevented at REPEATABLE READ and above.

Code

-- Session A
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;   -- first query

-- Session B
START TRANSACTION;
UPDATE account SET balance = 500 WHERE id = 1;
COMMIT;

-- Session A
SELECT balance FROM account WHERE id = 1;   -- second query
COMMIT;

Assumptions of this model

The lab follows the behavior of MySQL InnoDB. What each isolation level allows follows the definitions in the SQL standard and the MySQL documentation. That a phantom read is not seen at REPEATABLE READ applies only to plain SELECT statements that take no locks; a statement that works on the latest data, such as SELECT ... FOR UPDATE or UPDATE, can give a different result. That a query takes shared locks at SERIALIZABLE is how InnoDB implements it; other databases such as PostgreSQL work differently. All 12 combinations (3 situations × 4 isolation levels) were run on a real MySQL 8.4.11 with two sessions, and the values that A read and the lock waits were confirmed to match the screen.

Previous lab: Transaction basics · Next lab: Gap locks and next-key locks