The SQL standard defines four levels of isolation for SQL transactions. Each isolation level specifies the kinds of interactions that are not permitted while concurrent transactions are executing—that is, whether transactions are isolated from each other, or if they can read or update information in use by another transaction. Higher isolation levels include the restrictions imposed by the lower levels.
The isolation levels are shown in Table 10-6, and described in more detail on the following pages.
Number |
Name |
Description |
|---|---|---|
0 |
read uncommitted |
The transaction is allowed to read uncommitted changes to data. |
1 |
read committed |
The transaction is allowed to read only committed changes to data. |
2 |
repeatable read |
The transaction can repeat the same query, and no rows that have been read by the transaction will have been updated or deleted. |
3 |
serializable read |
The transaction can repeat the same query, and receive exactly the same results. No rows can be inserted that would appear in the result set. |
You can choose the isolation level for all select queries during a session, or you can choose the isolation level for a specific query or table in a transaction.
At all isolation levels, all updates acquire exclusive locks and hold them for the duration of the transaction.
For tables that use the allpages locking scheme, requesting
isolation level 2 also enforces isolation level 3.
