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:
SET TRANSACTION 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.
