What is Isolation level?

A setting that determines how far interference between concurrently running transactions is prevented. The SQL standard defines four levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Higher levels prevent more of dirty reads, non-repeatable reads and phantom reads, but reduce the amount of work that can be processed concurrently.

Japanese name: 分離レベル

Isolation level appears in 3 practice questions across 1 topic, including Database Basics.

Field: Database Basics

Try it: questions about Isolation level

Among the problems that occur when transactions run concurrently, which correctly describes a dirty read?

AA transaction reads an update that another transaction has not yet committed, and because that transaction then rolls back, it ends up using a value that never existed
BReading the same row twice within one transaction gives a different value, because another transaction updated the row and committed in between
CRunning a search with the same condition twice gives a different number of rows, because another transaction added a row and committed in between

Show answer and explanation

Answer: A. A transaction reads an update that another transaction has not yet committed, and because that transaction then rolls back, it ends up using a value that never existed

A dirty read is the problem of reading uncommitted (not yet permanent) data. If the transaction that made the update later rolls back, the value read is one that was never made permanent, and any processing based on it is wrong. Finding that the same row has a different value when read twice is a non-repeatable read, and finding that rows matching a search condition have increased or decreased is a phantom read. These problems occur when transaction isolation is insufficient, and in SQL you choose how many of them to prevent by setting the isolation level. At isolation levels of READ COMMITTED and above, dirty reads do not occur.

Transaction T1 ran SELECT COUNT(*) FROM orders WHERE amount >= 1000 and got 8. Immediately afterwards, T2 inserted an order row with an amount of 2000 and committed. When T1 ran the same SELECT statement again, it got 9. What is this phenomenon?

ANon-repeatable read
BDirty read
CPhantom read

Show answer and explanation

Answer: C. Phantom read

When a search with the same condition is repeated within one transaction and the set of result rows changes because another transaction added (or deleted) matching rows and committed in between, the phenomenon is called a phantom read (or phantom). The name comes from the newly appearing rows being like ghosts (phantoms). A non-repeatable read is when a row already read has a different value (or has disappeared) on rereading, because another transaction updated or deleted it and committed. Locks on existing rows cannot prevent rows that do not yet exist from being added, so preventing phantoms requires mechanisms such as locking the range covered by the search condition. Under the SQL standard, phantoms do not occur at the SERIALIZABLE isolation level. T2 committed, so this is not a dirty read.

Isolation level in the explanations

These problems occur when transaction isolation is insufficient, and in SQL you choose how many of them to prevent by setting the isolation level.
Among the problems that occur when transactions run concurr…
Under the SQL standard, phantoms do not occur at the SERIALIZABLE isolation level.
Transaction T1 ran SELECT COUNT(*) FROM orders WHERE amount…
The higher the isolation level, the more lock waits and retries there are and the less work can be processed concurrently, so the level is chosen to suit the application.
Among the isolation levels defined by the SQL standard, whi…

Questions that mention Isolation level (3)

Books on Database Basics Ad

Search Amazon for semiconductor Database Basics

As an Amazon Associate I earn from qualifying purchases.

Back to the glossary