Manage Isolation Levels

Introduction

The Manage Isolation Levels module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Managing concurrency with Read Uncommitted
• Managing concurrency with Repeatable Read

Lab time: It will take approximately 45 minutes to complete this lab.

Exam Objectives

Two exam objectives are covered in this lab:

• Demonstrate how the isolation level Read Uncommitted causes dirty reads when two sessions are executed simultaneously where the first one updates a column in a table and the second session reads the same before the first transaction is rolled back
• Demonstrate how the isolation level Repeatable Read impacts concurrency
• When select statements are executed by two simultaneous sessions
• When a select statement and an insert statement are executed by two simultaneous sessions
• When a select statement and an update statement are executed by two simultaneous sessions

Exercise 1 - Managing Concurrency with READ UNCOMMITTED

An isolation level protects a specified transaction from other transactions to prevent problems that can arise due to concurrent access of data.

A transaction in SQL SERVER ensures that group of operations is processed as a unit. For example, you can define a banking transaction and perform data modification to credit one account and debit another as a single unit of work. If the transaction succeeds, all the modifications that you made in the transaction are committed in the database. In the case of the failure of your transaction that must be roll backed, all your changes to the data are erased.

Each Transact-SQL statement such as INSERT, UPDATE, or DELETE is executed as a transaction and is an implicit transaction. In user-defined or explicit transactions, you need to group the statements of the transaction between a BEGIN TRANSACTION and a COMMIT TRANSACTION clause. In case if the transaction fails, you can provide the ROLLBACK TRANSACTION statement to erase all data modifications made from the beginning of the transaction.

You can set the transaction isolation level for all transactions in a session. Setting the transaction isolation level helps you to specify the default locking behavior for all statements in a session. In other words, you can use locks to prevent update conflicts. When you are in the process of changing data, you can prevent other users from reading or modifying the data. For example, consider you are calculating the average of a set of value in a group and you want to ensure that other transactions do not modify the set of data in the group. In such cases, you can request the SQL Server to hold locks on the data.

The common types of locks include:

• Shared: In general, read operations acquire shared lock. When a transaction acquires shared lock on a resource, another transaction can also acquire a shared lock, even though the first transaction has not completed
• Exclusive: In general, write operations acquire an exclusive lock. Data modification statements such as INSERT, UPDATE, and DELETE use the exclusive lock
• Update: The SQL Server applies update locks on the rows when they are selected for update

When the transaction isolation level is set to the highest, the locks are held longer and are more restrictive. The different transaction isolation levels include:

• READ COMMITTED: This option specifies that statements cannot read data that has been modified but not committed by other transactions. Setting this level directs SQL Server to use shared locks while reading if READ_COMMITTED_SNAPSHOT is set to off
• READ UNCOMMITTED: This option specifies that statements can read data that has been modified and are not committed by other transactions. This is the least isolation level. This is similar to setting NO LOCK on all tables in all SELECT statements in a transaction
• REPEATABLE READ: This option specifies that statements cannot read data that has been modified but not committed by other transactions and other transactions cannot modify data that has been read by the current transaction until the current transaction completes. Setting this level directs the SQL Server to use shared locks until the end of the transaction
• SNAPSHOT: This option specifies that only the data modifications that are made by other transactions before the beginning of the current transaction are visible to the statements in the current transaction. However, the data modifications made by other transactions after the beginning of the current transaction are not visible to the statements in the current transaction. Setting this option does not request locks when reading data except during recovery of the database
• SERIALIZABLE: This option prevents other users from updating or inserting rows that match the condition in the WHERE clause of the transaction.

In this exercise, you will demonstrate transactions by setting isolation level to READ UNCOMMITTED.

Exercise 2 - Managing Concurrency with Repeatable Read

In this exercise, you will manage concurrency using Repeatable Read isolation level.