Isolation levels determine the proportion to which data being accessed or modified in one transaction is protected from changes to the data by other transactions. In theory, each transaction should be fully isolated from other transactions. However, in practice, for practical and performance reasons, this might not always be the case. In a concurrent environment in the absence of locking and isolation, the following four scenarios can happen:
Ideally, a DBMS must provide levels of isolation to prevent these types of scenarios. Sometimes, because of practical and performance reasons, databases do relax some of the rules. ANSI has defined four transaction isolation levels, each providing a different degree of isolation to cover the previous scenarios. ANSI SQL-92 defines the following four standards for transaction isolation:
SQL Server does support all these levels. Each higher level incorporates the isolation provided at the lower levels. You can set these isolation levels for your entire session by using the SET TRANSACTION ISOLATION LEVEL T-SQL command, or for individual SELECT statements by specifying the isolation level hints within the query. Using table-level hints will be covered later in this chapter in the "Table Hints for Locking" section.
If you set the Read Uncommitted mode for a session, no isolation is provided to the SELECT queries in that session. A transaction that is running with this isolation level is not immune to dirty reads, nonrepeatable reads, or phantom reads.
To set the Read Uncommitted mode for a session, run the following statements from the client:
Be careful when running queries at Read Uncommitted isolation; it is possible to read changes that have been made to data that are subsequently rolled back. In essence, the accuracy of the results cannot be guaranteed. You should only use this mode when you need to get information quickly from an OLTP database without impacting or being impacted by the ongoing updates, and when the accuracy of the results is not critical.
The Read Committed mode is the default locking-isolation mode for SQL Server. With Read Committed as the transaction isolation level, read operations can only read pages for transactions that have already been committed. No "dirty reads" are allowed. Locks acquired by update transactions are held for the duration of the transaction. However, in this mode, read requests release locks as soon as the query finishes reading the data. Although this improves concurrent access to the data for updates, it does not prevent nonrepeatable reads or phantom reads. For example, within a transaction, a process could read one set of rows early in the transaction, and then before reading the information again, another process could modify the resultset, resulting in a different resultset being read the second time.
Because Read Committed is the default isolation level for SQL Server, you do not need to do anything to set this mode. If you need to set the isolation level back to Read Committed mode for a session, run the following statements from the client:
In Repeatable Read mode, SQL Server provides the same level of isolation for updates as in Read Committed mode, but it also allows the data to be read many times within the same transaction and guarantees that the same values will be read each time. Repeatable Read isolation mode prevents other users from updating data that has been read within the transaction until the transaction in which it was read is committed or rolled back. This way, the reading transaction will not pick up changes to the rows it read previously within the transaction. However, this isolation mode does not prevent additional rows (phantom reads) from appearing in the subsequent reads.
Although preventing nonrepeatable reads is desirable for certain transactions, it requires holding locks on the data that has been read until the transaction is completed. This reduces concurrent access for multiple update operations and causes performance degradation due to lock waits and locking contention between transactions. It can also potentially lead to deadlocks. (Deadlocking will be discussed in more detail in the "Deadlocks" section later in this chapter.)
To set Repeatable Read mode for a session, run the following statements from the client:
Serializable Read mode is similar to repeatable reads but adds to it the restriction that rows cannot be added to a resultset that was read previously within a transaction. This prevents phantom reads. In other words, Serializable Read locks the existing data being read as well as rows that do not yet exist. It accomplishes this by locking the data being read. In addition, SQL Server puts locks on the range of values being read so that additional rows cannot be added to the range.
For example, perhaps you run a query in a transaction that retrieves all records for the Sales table in the Pubs database for a store with the stor_id of 7066. To prevent additional sales records from being added to the sales table for this store, SQL Server locks the range of values with stor_id of 7066. It accomplishes this by using key-range locks, which will be discussed in the "Serialization and Key-Range Locking" section later in this chapter.
Although preventing phantom reads is desirable for certain transactions, Serializable mode, like Repeatable Read, reduces concurrent access for multiple update operations and can cause performance degradation due to lock waits and locking contention between transactions, and potentially lead to deadlocks.
To set the Serializable mode for a session, run the following statements from the client: