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 summarized in Table 1-6, and are described in more detail on the following pages.

Table 1-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 are 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 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 allpages locking, requesting isolation level 2 also enforces isolation level 3. The Adaptive Server default isolation level is level 1.