Serializable and Snapshot

Introduction

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

• Working with serializable isolation level
• Working with snapshot isolation level

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

Exam Objectives

Two exam objectives are covered in this lab.

• Demonstrate how the isolation level Serializable impacts concurrency:
• When a select statement and an insert statement are executed by two simultaneous sessions on table without index
• When a select statement and an insert statement are executed by two simultaneous sessions on an indexed column
• Demonstrate how the isolation level Snapshot recognizes only the modifications that were made before the beginning of the transaction

Exercise 1 - Working with Serializable Isolation Level

The system of locking that works between Transact-SQL transactions in the database is controlled or determined by isolation levels. There are various isolation levels in SQL Server. Apart from serializable and snapshot, the other levels are as follows:

• Read Uncommitted: This is the lowest isolation level. In this level, you can modify the data when the transaction is running.
• Repeatable Read: This is similar to Read Committed level. However, if you execute two SELECT statements in a single transaction, the result will be the same of both the statements.
• Read Committed: This is the default isolation level in SQL Server. The SELECT statements return only committed data from the database.

In this exercise, you will work with serializable isolation level. This level is an extension of Repeatable Read. The database engine will wait till all the transactions are completed and then only an UPDATE or INSERT transaction will execute on the data.

Exercise 2 - Working with Snapshot Isolation Level

In the snapshot isolation level, other queries can interact with the snapshot based transactions. However, if the other queries are modifying the data, the old version of the data is stored in the tempdb database. Thus, the data gets modified in the database when the snapshot transactions are running, but the changes to the data are stored in SQL Server resources.

In this exercise, you will work with snapshot isolation level.