Questions About TSQL Transaction Isolation Levels You Were Too Shy to Ask

Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask

Every time you access a relational database to make a query, you have an important decision to make: What is the appropriate isolation level for your query? If you get this wrong, the consequences can be serious. Deadlocks, Dirty reads, Non-repeatable reads, or poor performance. We're in luck, because Robert Sheldon once more answers those questions that are awkward to ask in public.

1. “How do I set the transaction isolation level when connecting to a SQL Server database?”
2. “I still don’t get all this dirty reads and phantom reads business. What do they have to do with concurrency problems?”
3. “What is the Read Committed Snapshot isolation level and how do I enable it?”
4. “How do I set the default transaction isolation level on the current database?”
5. “What transaction isolation levels can I specify on a memory-optimized table?”
6. “How do I set the default transaction isolation level for write operations?”
7. “How do I enable the snapshot isolation level on my transactions?”
8. “The Serializable and Snapshot isolation levels appear to achieve the same results. What are the differences between them?”
9. “How do I verify which snapshot-related database options are enabled on my database?”
10. “How do I verify the transaction isolation levels that an application is using when connecting to the database?”
11. “What’s the difference between using the NOLOCK table hint and the Read Uncommitted transaction level?”
12. “Why would I ever use the Read Uncommitted isolation level?”

“How do I set the transaction isolation level when connecting to a SQL Server database?”

To set the isolation level, you can issue a SET TRANSACTION ISOLATION LEVEL statement after you connect to SQL Server. The isolation level will apply to the rest of that session, unless you explicitly change the level again.

Within the SET TRANSACTION ISOLATION LEVEL statement, you must specify one of the following five isolation levels:

• READ UNCOMMITTED: A query in the current transaction can read data modified within another transaction but not yet committed. The database engine does not issue shared locks when Read Uncommitted is specified, making this the least restrictive of the isolation levels. As a result, it’s possible that a statement will read rows that have been inserted, updated or deleted, but never committed to the database, a condition known as dirty reads. It’s also possible for data to be modified by another transaction between issuing statements within the current transaction, resulting in nonrepeatable reads or phantom reads.
• READ COMMITTED: A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. However, data can still be modified by other transactions between issuing statements within the current transaction, so nonrepeatable reads and phantom reads are still possible. The isolation level uses shared locking or row versioning to prevent dirty reads, depending on whether the READ_COMMITTED_SNAPSHOT database option is enabled. Read Committed is the default isolation level for all SQL Server databases.
• REPEATABLE READ: A query in the current transaction cannot read data modified by another transaction that has not yet committed, thus preventing dirty reads. In addition, no other transactions can modify data being read by the current transaction until it completes, eliminating nonrepeatable reads. However, if another transaction inserts new rows that match the search condition in the current transaction, in between the current transaction accessing the same data twice, phantom rows can appear in the second read.
• SERIALIZABLE: A query in the current transaction cannot read data modified by another transaction that has not yet committed. No other transaction can modify data being read by the current transaction until it completes, and no other transaction can insert new rows that would match the search condition in the current transaction until it completes. As a result, the Serializable isolation level prevents dirty reads, nonrepeatable reads, and phantom reads. However, it can have the biggest impact on performance, compared to the other isolation levels.
• SNAPSHOT: A statement can use data only if it will be in a consistent state throughout the transaction. If another transaction modifies data after the start of the current transaction, the data is not visible to the current transaction. The current transaction works with a snapshot of the data as it existed at the beginning of that transaction. Snapshot transactions do not request locks when reading data, nor do they block other transactions from writing data. In addition, other transactions writing data do not block the current transaction for reading data. As with the Serializable isolation level, the Snapshot level prevents dirty reads, nonrepeatable reads and phantom reads. However, it is susceptible to concurrent update errors. (not ANSI/ISO SQL standard)

There is much more to the isolation levels than what I’ve covered here, and the differences between them can be quite subtle, so be sure to refer to SQL Server documentation for more information. In the meantime, let’s look at how to actually specify the isolation level after making a connection. As already noted, you must use the SET TRANSACTION ISOLATION LEVEL statement, as shown in the following T-SQL statement:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT * FROM EmployeeInfo

WHERE EmpID = 1;

Notice that we simply specify the isolation level in our SET TRANSACTION ISOLATION LEVEL statement, in this case, Read Uncommitted. We can then run our query under that isolation level. Afterwards, we can return our session to the default level by issuing the following statement:

SETTRANSACTION ISOLATION LEVEL READ COMMITTED;

That’s all there is to setting the isolation level. The trick is in understanding how the isolation levels work and the implications of each one. Tread carefully when changing the isolation level. You can easily impact performance if you use a level too restrictive, or create application problems by implementing an isolation level that leads to transactions prone to concurrency issues.

“I still don’t get all this dirty reads and phantom reads business. What do they have to do with concurrency problems?”

In the previous question, we touched upon the issue of dirty reads, nonrepeatable reads and phantom reads and how they’re tied to transaction isolation levels. Perhaps a different take on each of these would be helpful:

• Dirty read: Session 1 begins a transaction and modifies data. Session 2 reads the modified data before Session 1 commits the transaction. Session 2 is reading data that exists only in theory because the Session 1 transaction might be rolled back. If that occurs, the data read by Session 2 is no longer valid.
• Nonrepeatable read: Session 1 begins a transaction and retrieves a row of data from a table. Session 2 updates that row. Session 1 tries to retrieve the row once more, still within the original transaction, but receives different results because of the Session 2 update.
• Phantom read: Session 1 begins a transaction and retrieves several rows of data from a table. Session 2 inserts a row into the same table, and the insert coincides with the search criteria of the Session 1 statement. When Session 1 reruns the query, the new row added by Session 2 is now included in the results.

Notice the similarities between nonrepeatable reads and phantom reads. In fact, you can think of a phantom read as a variation of the nonrepeatable read. The problem with these definitions, however, is that it can be difficult to get agreement on any one being the “correct” one, particularly when it comes to phantom reads and nonrepeatable reads. Even within Microsoft documentation the definitions are not consistent. However, the scenarios described here should at least give you some sense of the differences between these concurrency problems.

The important point to remember is that the possibility of running into any of these issues depends on the isolation levels you’re using when you run your queries. The following table shows the concurrency issues that each isolation level is susceptible to:

Isolation level	Dirty read	Nonrepeatable read	Phantom read
Read uncommitted	Y	Y	Y
Read committed	N	Y	Y
Repeatable read	N	N	Y
Serializable	N	N	N
Snapshot	N	N	N

Let’s look at a few examples to better illustrate how the isolation levels can help prevent concurrency problems. The examples are based on the test EmployeeInfo table, which includes the EmpID, FirstName, and LastName columns. However, you can use whatever table you like, as long as you can update data in it.

In the first example, we simulate a dirty read against the EmployeeInfo table. (If you want to try this out, you’ll need to establish two different connections to your database.) Let’s start with Session 1. First, we begin a transaction and run an UPDATE statement, as shown in the following T-SQL:

-- session 1

BEGIN TRANSACTION;

UPDATE EmployeeInfo

SET FirstName = 'Frank'

WHERE EmpID = 1;

WAITFOR DELAY '00:00:05' 

ROLLBACK TRANSACTION;

Notice that the transaction includes a WAITFOR DELAY statement, with five seconds specified. This is included to give us time to run our statements in the second session. You can take this approach, adjusting the seconds as necessary, or simply run the BEGIN TRANSACTION and UPDATE statements and stop there. You can then run the statements in Session 2, and then return to Session 1 to run the ROLLBACK statement. The goal is to make sure we run the statements in the second session in between the UPDATE and ROLLBACK statements in the first session.

When we hit the delay, we can run the following the following T-SQL statements in Session 2:

--session 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

We start by specifying that the Read Uncommitted isolation level be used. We then run the SELECT statement, which immediately returns the value Frank, the new value inserted into the table in Session 1. However, if we run the SELECT statement a second time, after the Session 1 transaction has been rolled back, we’ll receive the value Ken, the original value in the table. The returned value Frank in Session 2 is an example of a dirty read.

Now let’s repeat our experiment, only this time with a different isolation level. In Session 1, we again start our transaction and do our update:

-- session 1

BEGIN TRANSACTION;

UPDATE EmployeeInfo

SET FirstName = 'Frank'

WHERE EmpID = 1;

WAITFOR DELAY '00:00:05' 

ROLLBACK TRANSACTION;

This time in Session 2, we set the transaction level to Read Committed:

--session 2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

When we run the SELECT statement, it waits until the Session 1 transaction has been rolled back and then returns the value Ken. As you can see, no dirty read. But another problem is lurking: the nonrepeatable read.

To demonstrate the nonrepeatable read, we take a different approach. In Session 1, we set the isolation level to Read Committed (which is the default), start a transaction, and retrieve a row from the EmployeeInfo table:

--session 1

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

WAITFOR DELAY '00:00:05' 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

ROLLBACK TRANSACTION;

During the delay, we run an UPDATE statement in Session 2:

--session 2

UPDATE EmployeeInfo

SET FirstName = 'Frank'

WHERE EmpID = 1;

This time around, the first SELECT statement in Session 1 returns the value Ken, the original value. However, the second time the SELECT statement runs-after the five-second delay and the Session 2 update, the statement returns the value Frank, thus demonstrating a nonrepeatable read.

Let’s do our experiment again, only this time specify the Repeatable Read isolation level in Session 1:

--session 1SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

WAITFOR DELAY '00:00:05' 

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

ROLLBACK TRANSACTION;

In Session 2, during the Session 1 delay, we run the same UPDATE statement as in the previous example:

--session 2

UPDATE EmployeeInfo

SET FirstName = 'Ken'

WHERE EmpID = 1;

In this case, both executions of the SELECT statement in Session 1 return the value Frank, the last value to be inserted (from the preceding example). The UPDATE statement will not run until the Session 1 transaction has been rolled back. If we were to then run the SELECT statement again, after the update, it would return Ken, as expected.

We could create more examples to demonstrate various scenarios, but you get the picture. The isolation level determines which read concurrency problems can occur. It’s up to you to decide how strictly you want to control access. Just remember, the stricter your isolation policy, the longer the database engine will lock your data and the greater the impact on performance.

“What is the Read Committed Snapshot isolation level and how do I enable it?”

In some SQL Server documentation, you’ll see references to the Read Committed Snapshot isolation level as a sixth type. What this actually refers to is the READ_COMMITTED_SNAPSHOT database option. When a database is first created, this option is set to OFF, so it has no impact on the database or the existing isolation levels. When set to ON, however, the option modifies the Read Committed isolation level to behave more like the Snapshot isolation level, thus the label Read Committed Snapshot.

By default, when the Read Committed transaction level is enabled, the database uses shared locks to prevent other transactions from modifying rows when the current transaction is reading those rows. At the same time, the locks block the current transaction from reading rows modified by other running transactions until those transactions have completed.

When the READ_COMMITTED_SNAPSHOT database option is set to ON, the database engine uses row versioning for Read Committed to ensure transactionally consistency, rather than using shared locks. The engine stores the row versions in the tempdb system database as long as necessary to facilitate this process. In this way, the current read operation does not block other transactions, or vice versa.

To enable the READ_COMMITTED_SNAPSHOT option on a database, you can run an ALTER DATABASE statement similar to the one shown in the following example:

ALTER DATABASE AdventureWorks2014

SET READ_COMMITTED_SNAPSHOT ON;

Notice that we need only set the option to ON. To disable the option, simply set it to OFF:

ALTER DATABASE AdventureWorks2014

SET READ_COMMITTED_SNAPSHOT OFF;

The READ_COMMITTED_SNAPSHOT option can help avoid locking contention and subsequently improve query performance by minimizing wait times, but it does not eliminate the risks of non-repeatable reads and phantom reads. For that, you need the Snapshot isolation level. The challenge with Snapshot, however, is that it can result in update conflict errors. With Read Committed Snapshot, the database engine will block a connection until the active connection releases its locks, and then it will update the data.

Also be aware that enabling the READ_COMMITTED_SNAPSHOT option-and the subsequent row versioning that goes with it-incurs additional overhead in maintaining the versioned rows in tempdb, as is the case with the Snapshot isolation level. You must also ensure that tempdb has plenty of room to accommodate all the versioned data.

“How do I set the default transaction isolation level on the current database?”

You can’t. The default isolation level for all SQL Server databases is Read Committed, and your only option is to set the isolation level within a session, if you want to use a level other than the default. Workarounds have been suggested, such as creating a logon trigger that sets the isolation level, but these approaches tend not to deliver the expected results.

That said, SQL Server does support several database options that can impact the isolation levels: READ_COMMITTED_SNAPSHOT, ALLOW_SNAPSHOT_ISOLATION, and MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. All of these are disabled by default, but you can easily use T-SQL to turn them on, as shown in the following examples:

ALTER DATABASE AdventureWorks2014

SET READ_COMMITTED_SNAPSHOT ON;

ALTER DATABASE AdventureWorks2014

SET ALLOW_SNAPSHOT_ISOLATION ON;

ALTER DATABASE AdventureWorks2014

SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;

The READ_COMMITTED_SNAPSHOT option affects the behavior of the Read Committed isolation level, as previously mentioned, and the ALLOW_SNAPSHOT_ISOLATION option controls whether the Snapshot isolation level can be enabled during a session. When you set either of these options to ON, the database engine maintains row versions in the tempdb database to meet the requirements of transactions running under the Read Committed Snapshot or Snapshot isolation level.

The MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT option is specific to memory-optimized tables, new in SQL Server 2014. When the option is enabled, the database engine uses Snapshot isolation for all interpreted T-SQL operations on memory-optimized tables, whether or not the isolation level is set explicitly at the session level. (Interpreted T-SQL refers to batches or stored procedures other than natively compiled procedures.)

You can disable any of these options by setting them to OFF, as shown in the following ALTER DATABASE statements:

ALTER DATABASE AdventureWorks2014

SET READ_COMMITTED_SNAPSHOT OFF;

ALTER DATABASE AdventureWorks2014

SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE AdventureWorks2014

SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT OFF;

Other than these options, there’s not much else you can do about the default behavior. You’re stuck with Read Committed whether or not row versioning is used. To get around this, you must use the SET TRANSACTION ISOLATION LEVEL statement at the session level, or use a table hint at the statement level, if you want your change to apply only to that statement. For example, the following SELECT statement specifies the TABLOCK table hint:

SELECT EmpID, FirstName, LastName

FROM EmployeeInfo WITH(TABLOCK)

WHERE EmpID > 99

ORDER BY LastName;

The TABLOCK table hint directs the database engine to lock the data at the table level, rather than the row or page level. The table hint will apply only to the table targeted in this statement and will not impact the rest of the session, as would a SET TRANSACTION ISOLATION LEVEL statement.

You can also use table hints for a specific table in a join. For example, the following SELECT statement specifies the SERIALIZABLE table hint for the SalesOrderDetail table:

SELECT d.ProductID, d.OrderQty, h.OrderDate

FROM Sales.SalesOrderHeader h INNER JOIN

  Sales.SalesOrderDetail d WITH(SERIALIZABLE)

  ON h.SalesOrderID = d.SalesOrderID

ORDER BY d.ProductID, h.OrderDate DESC;

This tells the database engine to apply the Serializable isolation level only to the SalesOrderDetail table for this query. The Read Committed isolation level (the default) still applies to the SalesOrderHeader table. As a result, a shared lock will be held on the SalesOrderDetail table for the entire transaction, rather than just the initial table read.

“What transaction isolation levels can I specify on a memory-optimized table?”

Memory-optimized tables are part of the new In-Memory OLTP technology introduced in SQL Server 2014 to help improve application performance. Memory-optimized tables support the Read Committed, Repeatable Read, Serializable, and Snapshot isolation levels, with Read Committed the default level, as with disk-based tables. Notice that the Read Uncommitted isolation level is not included.

All isolation levels supported for memory-optimized tables are based on row versioning. The tables rely on multiple row versions to guarantee isolation, providing optimistic concurrency control. As a result, the database engine does not issue shared data locks. Row versions are not stored in tempdb like the Snapshot isolation level for disk-based tables, but are instead part of the memory-optimized table itself.

Isolation levels used for memory-optimized tables provide the same guarantees as those used for disk-based tables; only the processes used to get there are different. Because the isolation levels implement row versioning instead of shared locks, a write conflict will occur when two transactions attempt to update the same row concurrently, rather than one transaction waiting for the other to complete before attempting the update.

If you’re working with memory-optimized tables, there are a number of guidelines you should follow when specifying an isolation level. For example, you should avoid long-running transactions and implement retry logic in your apps to deal with conflicts, validation errors, and commit-dependency failures. You can find details about the various guidelines in the MSDN article “Guidelines for Transaction Isolation Levels with Memory-Optimized Tables.”

Keep in mind, however, that SQL Server’s in-memory technologies are still in their infancy. Consequently, the available information about the technology is often confusing and inconsistent. If you plan to implement in-memory tables, be sure to test your operations thoroughly and compare the behavior for different isolation levels. No doubt we’ll see changes in the technology and guidelines going forward.

“How do I set the default transaction isolation level for write operations?”

It’s best to think of the isolation levels in terms of read operations, rather than write operations. The database engine issues exclusive locks on all write operations and holds the locks until the transactions have completed, regardless of which isolation level is used. This ensures that your write operations are always protected. Concurrency issues such as dirty reads and phantom reads, as their names suggest, apply to read operations, not write. The database engine governs the locking behavior of all write operations, and you cannot change that behavior at the database level.

Even for read operations, you have few options for changing the default behavior, as noted earlier. When a transaction initiates a read option, the database engine uses either shared locks or row versions (to support Snapshot isolation) in order to handle concurrency issues. The way in which the database engine issues shared locks depends on the isolation level. For example, in a transaction running at the Read Uncommitted isolation level, the database engine issues no shared locks. However, if the transaction is running at the Serializable level, the database engine will lock the selected data throughout the transaction.

Multiple shared locks can exist on the selected data at any one time, but only one exclusive lock can exist on data at a time. Exclusive locks are not compatible with shared locks. Once a transaction has taken an exclusive lock, the database engine cannot issue a shared lock on that data until the transaction has completed.

There are a number of other rules that govern locking, but the point is, you cannot change the database’s default behavior when it comes to write operations. You can, however, override that behavior by using table hints, just like you can use table locks for read operations. For example, the following UPDATE statement specifies the TABLOCKX table hint:

UPDATE EmployeeInfo WITH(TABLOCKX)

SET FirstName = 'Ken'

WHERE EmpID = 1;

The TABLOCKX table hint tells the database engine to issue an exclusive lock on the entire table, rather than at the row or page level, as would normally be the case for this type of statement.

“How do I enable the snapshot isolation level on my transactions?”

As noted previously, the Snapshot isolation level relies on row versions being maintained in the tempdb database to avoid locking contention issues. This approach incurs overhead to maintain those versioned rows. For this reason, you must explicitly enable snapshot isolation at the database level before you can implement it at the session level.

To enable snapshot isolation, you can run an ALTER DATABASE statement to set the ALLOW_SNAPSHOT_ISOLATION option to ON, as shown in the following example:

ALTER DATABASE AdventureWorks2014

SET ALLOW_SNAPSHOT_ISOLATION ON;

When you enable the ALLOW_SNAPSHOT_ISOLATION option, you activate a mechanism in your database for storing the versioned rows in tempdb. You can then set the Snapshot isolation level within your session for specific transactions:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

COMMIT TRANSACTION;

A transaction using the Snapshot isolation level sees the data as it existed at the beginning of the transaction. As a result, if you try to update data that has changed during the course of the transaction, the database engine rolls back the transaction and raises an error.

“The Serializable and Snapshot isolation levels appear to achieve the same results. What are the differences between them?”

Although the Serializable and Snapshot isolation levels work differently, they both protect against dirty reads, nonrepeatable reads, and phantom reads. The Serializable isolation level relies on shared data locks to address concurrency issues, and the Snapshot isolation level relies on row versioning. (It’s true that all isolation levels use row versioning for memory-optimized tables, but for now, let’s stick with disk-based tables.)

The key to understanding the differences between the two isolation levels is to look at what happens when data gets updated. With the Serializable isolation level, the database engines issues a shared lock on the accessed data and holds that lock until the transaction has completed. As a result, no other transactions can modify the data as long as the current transaction is active.

With the Snapshot isolation level, the database engine does not lock the data. Other transactions can update the data at the same time as the current transaction, resulting in update conflicts. Let’s try another little experiment to demonstrate how this works. In Session 1, we set the connection to the Serializable isolation level, begin a transaction and issue several statements:

--session 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

WAITFOR DELAY '00:00:05' 

UPDATE EmployeeInfo

SET FirstName = 'Roger'

WHERE EmpID = 1;

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

COMMIT TRANSACTION;

As in our previous examples, we’re using a WAITFOR DELAY statement to give us time to run the second session. Before the delay, we retrieve the data (Session 1). After the delay, we update the data and retrieve it once again. During the delay, we run our second session:

--session 2

UPDATE EmployeeInfo

SET FirstName = 'Harold'

WHERE EmpID = 1;

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

In Session 2, we’re simply updating the same row as in Session 1 and then retrieving the results. Because we’re using the Serializable isolation level in Session 1, the statements in Session 2 will not run until the Session 1 transaction has completed. Consequently, the first SELECT statement in Session 1 returns Ken, the original value, and the second SELECT statement returns the value Roger. The SELECT statement in Session 2 then returns the value Harold.

If we repeat the experiment, but instead specify the Snapshot isolation level in Session 1, we will receive different results. The first SELECT statement in Session 1 will still return the value Ken (assuming we reset that value to the original), and the SELECT statement in Session 2 will return the value Harold. However, when we run the UPDATE statement in Session 1, we receive the following error:

Msg 3960, Level 16, State 6, Line 297

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.EmployeeInfo' directly or indirectly in database 'AdventureWorks2014' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

As you can see, the Snapshot isolation level doesn’t handle concurrent updates as gracefully as the Serializable isolation level. However, Snapshot can offer improved query performance because the queries don’t have to wait for locks to be released before being able to access the data. If trying to decide which of the two to use, you’ll have to weigh query performance against tempdb overhead, as well as take into consideration the type of queries you’ll be running against your tables.

“How do I verify which snapshot-related database options are enabled on my database?”

You can verify the snapshot-related database options by using the sys.databases catalog view to retrieve the information on a specific database. For example, if you want to know the setting for the ALLOW_SNAPSHOT_ISOLATION option, you would query the snapshot_isolation_state_desc column in the sys.databases view, as shown in the following SELECT statement:

SELECT snapshot_isolation_state_desc

FROM sys.databases

WHERE name = 'AdventureWorks2014'

The statement will return the value ON if the option is enabled or OFF if it’s disabled. You can just as easily retrieve the current setting for the READ_COMMITTED_SNAPSHOT option by querying the is_read_committed_snapshot_on column:

SELECT is_read_committed_snapshot_on

FROM sys.databases

WHERE name = 'AdventureWorks2014'

In this case, the statement will return the value 1 if the option is enabled or 0 if it’s disabled. To retrieve the current setting of the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT option, use the is_memory_optimized_elevate_to_snapshot_on column:

SELECT is_memory_optimized_elevate_to_snapshot_on

FROM sys.databases

WHERE name = 'AdventureWorks2014'

Once again, the statement will return the value 1 if the option is enabled or 0 if it’s disabled.

“How do I verify the transaction isolation levels that an application is using when connecting to the database?”

You can use the sys.dm_exec_sessions dynamic management view (DMV) to verify a connection’s isolation level. For example, suppose you have an application that issues the following statements:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT FirstName FROM EmployeeInfo

WHERE EmpID = 1;

The application sets the isolation level to Read Uncommitted and then retrieves data from the EmployeeInfo table. You can run the following statement (within the same session) to return the isolation level:

SELECT transaction_isolation_level

FROM sys.dm_exec_sessions

WHERE session_id = @@SPID;

The SELECT statement retrieves the transaction_isolation_level column from the DMV. The statement also includes a WHERE clause that uses the @@SPID system variable to specify the current session ID.

In this case, the SELECT statement returns a value of 1. SQL Server uses the following values to represent the isolation levels available through the sys.dm_exec_sessions view:

• 0 = Unspecified
• 1 = Read Uncommitted
• 2 = Read Committed
• 3 = Repeatable
• 4 = Serializable
• 5 = Snapshot

If you can’t query the sys.dm_exec_sessions view within the session whose isolation level you want to verify, you’ll need to take a different approach to identify the application and its session. For example, the view supports the host_name, login_name, and program_name columns. Perhaps querying one of these will give you the information you need.

“What’s the difference between using the NOLOCK table hint and the Read Uncommitted transaction level?”

Both approaches tell the database engine not to issue shared locks when reading the requested data; however, the two approaches differ in scope, within the context of the current session. For example, suppose we issue the following T-SQL statements:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT FirstName, LastName

FROM EmployeeInfo

WHERE EmpID = 1;

In this case, we’re setting the connection to the Read Uncommitted isolation level before running our statement. The entire session will use this isolation level until the session ends or we explicitly change the level. That means, for every table we access throughout the session, no shared locks are placed on any of the tables for our read operations. Consequently, our transactions are not protected from dirty reads as well as nonrepeatable or phantom reads.

However, we might decide that only one or two of our target tables require the “lock-less” state, in which case, we can use the NOLOCK table hint to limit that isolation level to the specific query, as shown in the following example:

SELECT FirstName, LastName

FROM EmployeeInfo WITH(NOLOCK)

WHERE EmpID = 1;

The NOLOCK table hint is the equivalent of the READUNCOMMITTED table hint. The database engine ignores the hint if used in the FROM clause of a DELETE or UPDATE statement.

“Why would I ever use the Read Uncommitted isolation level?”

When the Read Uncommitted isolation level is used, the database engine does not issue shared locks on the accessed data, which can lead to the types of concurrency issues described earlier. That said, the Read Uncommitted isolation level can be useful in certain situations, particularly if you’re trying to avoid heavy lock contention.

For example, if you’re pulling historical data from a data warehouse or reporting database, your queries might benefit from the minimal locking, especially those complex joins that can easily escalate to table locks. In such a scenario, the primary DML operations might be inserts only and often not part of the queries. Plus, you might also be able to schedule your data loads to occur at a time when the data is not being accessed for reporting and analytics.

Another way to look at this is that the Read Uncommitted isolation level can be handy if you’re querying data that is not likely to change or you’re aggregating large quantities of data that permit an acceptable margin for error. On the other hand, if the data is time-sensitive or requires drill-down precision, you should forego Read Uncommitted, especially when an application frequently writes against the data. Your typical OLTP operation, for example, is usually a lousy candidate for Read Uncommitted, except for simple queries and small tables with fairly static data.

Developers can also benefit from the Read Uncommitted isolation level because they can use it to help debug a transaction by allowing them to see what is happening within the transaction while it’s still running. In this case, the dirty read can provide line-by-line insight into a transaction, which can be especially useful for those overly complex stored procedures.

Despite the advantages in performance that Read Uncommitted can offer in certain situations, you should use this isolation level judiciously. Make sure you understand your data and your queries, and that you’re not using Read Uncommitted to cover up for other problems.