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:
Lost update?In this scenario, no isolation is provided to a transaction from other transactions. Multiple transactions can read the same copy of data and modify it. The last transaction to modify the dataset prevails, and the changes by all other transactions are lost.
Dirty reads?In this scenario, one transaction can read data that is being modified by other transactions. Data read by the first transaction is inconsistent because the other transaction might choose to roll back the changes.
Nonrepeatable reads?This is somewhat similar to zero isolation. In this scenario, a transaction reads the data twice, but before the second read occurs, another transaction modifies the data; therefore, the values read by the first read will be different from those of the second read. Because the reads are not guaranteed to be repeatable each time, this scenario is called nonrepeatable reads.
Phantom reads?This scenario is similar to nonrepeatable reads. However, instead of the actual rows that were read changing before the transaction is complete, additional rows are added to the table, resulting in a different set of rows being read the second time. Consider a scenario where Transaction A reads rows with key values within the range of 1?5 and returns three rows with key values 1, 3, and 5. Before Transaction A reads the data again within the transaction, Transaction B adds two more rows with the key values 2 and 4 and commits the changes. Assuming that Transaction A and Transaction B both can run independently without blocking each other, when Transaction A runs the query a second time, it is now going to get 5 rows with key values 1, 2, 3, 4, and 5. This phenomenon is called phantom reads because in the second pass, you are getting records you did not expect to retrieve.
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:
Read Uncommitted (Level 0)
Read Committed (Level 1)
Repeatable Read (Level 2)
Serializable (Level 3)
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:
T-SQL?SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.
ODBC?Use the function call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_READ_UNCOMMITTED.
OLE DB?Use the function call ITransactionLocal::StartTransaction with the isoLevel set to ISOLATIONLEVEL_READUNCOMMITTED.
ADO?Set the IsolationLevel property of the Connection object to adXactReadUncommitted.
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:
T-SQL?SET TRANSACTION ISOLATION LEVEL READ COMMITTED.
ODBC?Use the function call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_READ_COMMITTED.
OLE DB?Use the function call ITransactionLocal::StartTransaction with isoLevel set to ISOLATIONLEVEL_READCOMMITTED.
ADO?Set the IsolationLevel property of the Connection object to adXactReadcommitted.
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:
T-SQL?SET TRANSACTION ISOLATION LEVEL REPEATABLE READ.
ODBC?Use the function call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_REPEATABLEREAD.
OLE DB?Use the function call ITransactionLocal::StartTransaction with isoLevel set to ISOLATIONLEVEL_REPEATABLEREAD.
ADO?Set the IsolationLevel property of the Connection object to adXact REPEATABLEREAD.
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:
T-SQL?SET TRANSACTION ISOLATION LEVEL SERIALIZABLE.
ODBC?Use the function call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_SERIALIZABLE.
OLE DB?Use the function call ITransactionLocal::StartTransaction with isoLevel set to ISOLATIONLEVEL_SERIALIZABLE.
ADO?Set the IsolationLevel property of the Connection object to adXact SERIALIZABLE.
Only one of the transaction isolation levels can be active at any given time for a user session. The isolation level you set within your application is active for the duration of the connection or until manually reset. To check the current transaction isolation level settings, run the following command:
DBCC USEROPTIONS Go Set Option Value ---------------------------------- ------------ textsize 64512 language us_english dateformat mdy datefirst 7 quoted_identifier SET ansi_null_dflt_on SET ansi_defaults SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET isolation level serializable