In theory, each transaction should be fully isolated from other transactions. But, in such a case, data availability is significantly reduced, because read operations in a transaction block write operations in other transactions, and vice versa. If data availability is an important issue, this property can be loosened using isolation levels. Isolation levels specify the degree to which data being retrieved in a transaction is protected from changes to the same data by other transactions. Before you are introduced to the existing isolation levels, the following section takes a look at scenarios that can arise if locking isn’t used and, hence, there is no isolation between transactions.
1. Concurrency Problems
If locking isn’t used, and thus no isolation exists between transactions, the following four problems may appear:
- Lost update
- Dirty reads (discussed earlier, in the “Locking” section)
- Nonrepeatable reads
- Phantoms
The lost update concurrency problem occurs when no isolation is provided to a transaction from other transactions. This means that several transactions can read the same data and modify it. The changes to the data by all transactions, except those by the last transaction, are lost.
The nonrepeatable read concurrency problem occurs when one process reads data several times and another process changes the same data between two read operations of the first process. Therefore, the values read by both read operations of the first process are different.
The phantom concurrency problem is similar to the nonrepeatable read concurrency problem, because two subsequent read operations can display different values, but in this case, the reason for this behavior lies in the different number of rows being read the first time and the second time. (Additional rows, called phantoms, are inserted by other transactions.)
2. The Database Engine and Isolation Levels
Using isolation levels, you can specify which of the concurrency problems discussed in the preceding section may occur and which you want to avoid. The Database Engine supports the following five isolation levels, which control how your read operations are executed:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
- SNAPSHOT
READ UNCOMMITTED, REPEATABLE READ, and SERIALIZABLE are available only in the pessimistic concurrency model, whereas SNAPSHOT is available only in the optimistic concurrency model. READ COMMITTED is available in both models. The four isolation levels available in the pessimistic concurrency model are described next. SNAPSHOT is described in the section titled “Row Versioning.”
2.1. READ UNCOMMITTED
READ UNCOMMITTED provides the simplest form of isolation between transactions, because it does not isolate the read operations from other transactions at all. When a transaction retrieves a row at this isolation level, it acquires no locks and respects none of the existing locks. The data that is read by such a transaction may be inconsistent. In this case, a transaction reads data that is updated from some other active transaction. If the latter transaction rolls back later, the former transaction reads data that never really existed.
Of the four concurrency problems described in the preceding section, READ UNCOMMITTED allows dirty reads, nonrepeatable reads, and phantoms.
NOTE The READ UNCOMMITTED isolation level is usually very undesirable and should be used only when the accuracy of the data read is not important or the data is seldom modified.
2.2. READ COMMITTED
As you already know, the READ COMMITTED isolation level has two forms. The first form applies to the pessimistic concurrency model, while the second form applies to the optimistic concurrency model. This section discusses the former. The second form, READ COMMITTED SNAPSHOT, is discussed in the upcoming section “Row Versioning.”
A transaction that reads a row and uses the READ COMMITTED isolation level tests only whether an exclusive lock is placed on the row. If no such lock exists, the transaction fetches the row. (This is done using a shared lock.) This action prevents the transaction from reading data that is not committed and that can be subsequently rolled back. After reading the data values, the data can be changed by some other transaction.
Shared locks used by this isolation level are released immediately after the data is processed. (Generally, all locks are released at the end of the transaction.) For this reason, the access to the concurrent data is improved, but nonrepeatable reads and phantoms can still happen.
NOTE The READ COMMITTED isolation level is the default isolation level of the Database Engine.
2.3. REPEATABLE READ
In contrast to the READ COMMITTED isolation level, REPEATABLE READ places shared locks on all data that is read and holds these locks until the transaction is committed or rolled back. Therefore, in this case, the execution of a query several times inside a transaction will always display the same result. The disadvantage of this isolation level is that concurrency is further reduced, because the time interval during which other transactions cannot update the same data is significantly longer than in the case of READ COMMITTED.
This isolation level does not prevent another transaction from inserting new rows, which are included in subsequent reads, so phantoms can appear.
2.4. SERIALIZABLE
SERIALIZABLE is the strongest isolation level, because it prevents all four concurrency problems already discussed. It acquires a range lock on all data that is read by the corresponding transaction. Therefore, this isolation level also prevents the insertion of new rows by another transaction until the former transaction is committed or rolled back.
NOTE The SERIALIZABLE isolation level is implemented using a key-range locking method. This method locks individual rows and the ranges between them. A key-range lock acquires locks for index entries rather than locks for the particular pages or the entire table. In this case, any modification operation of another transaction cannot be executed, because the necessary changes of index entries are not possible.
As a conclusion to the previous discussion of all four isolation levels, you have to know that each isolation level in the preceding description reduces the concurrency more than the previous one. Thus, the isolation level READ UNCOMMITTED reduces concurrency the least. On the other hand, it also has the smallest isolation from concurrent transactions. SERIALIZABLE reduces concurrency the most, but guarantees full isolation between concurrent transactions.
2.5. Setting and Editing Isolation Levels
You can set an isolation level by using the following:
- The TRANSACTION ISOLATION LEVEL clause of the SET statement
- Isolation-level hints
The TRANSACTION ISOLATION LEVEL option of the SET statement provides five constant values, which have the same names and meanings as the standard isolation levels just described. The FROM clause of the SELECT statement supports four hints for isolation levels:
- READUNCOMMITTED
- READCOMMITTED
- REPEATABLEREAD
- SERIALIZABLE
These hints correspond to the isolation levels with the same name (but with a space in the name for the first three). The specification of isolation levels in the FROM clause of the SELECT statement overrides the current value set by the SET TRANSACTION ISOLATION LEVEL statement.
The DBCC USEROPTIONS command returns, among other things, information about the isolation level. Look at the value of the ISOLATION LEVEL option of this statement to find out the isolation level of your process.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.