How isolation levels affect locking

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.

Table 10-6: Transaction isolation levels

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.

NoteFor tables that use the allpages locking scheme, requesting isolation level 2 also enforces isolation level 3.