Identifying and Analyzing Locking Issues

Introduction

The Identifying and Analyzing Locking Issues module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Working with SQL Server locks
• Managing deadlocks

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

Exam Objectives

Three exam objectives are covered in this lab.

• Demonstrate how to identify the queries which are holding locks
• Demonstrate how to identify the objects on which most locks are held
• Demonstrate how to create a deadlock graph using SQL Server Profiler to identify and analyze the reasons for a deadlock

Exercise 1 - Working with SQL Server Locks

The feature of locking in SQL Server databases enables you as database administrators to lock certain resources such as tables, rows, or pages to block other transactions from accessing the resources. For example, if a transaction is in progress on a table, you can set a lock on the table to block the resource. The locked resource becomes unavailable in the database till the lock is released.

SQL Server database engine locks the resources using various types of lock modes. These lock modes help to identify the ways to access the database resources in case of concurrent sessions. Some of the lock modes are Exclusive (X), Shared (S), Update (U), Intent Update (IU), Intent Exclusive (IX), Intent Shared (IS), Schema (Sch-M), Bulk Update (BU), and so on.

In this exercise, you will work with SQL Server locks.

Exercise 2 - Managing Deadlocks

Deadlock is a situation in SQL Server database when two or more sessions are waiting for each other to complete the transaction. Ultimately, none of them can complete unless the database engine solves the deadlock scenario. For example, consider a simple situation where there are two transactions; A and B. Transaction A has applied a lock mode on row one in table X. Transaction B has applied a lock mode on row two in table X. Now, if transaction A wants to apply another lock mode on row two, it cannot do so because of the already existing lock on row two. Similarly, this is the case with transaction B also. If transaction B wants to apply another lock mode on row one, it cannot do so. Thus, a deadlock is generated and both A and B transactions will have to wait forever to end.

To prevent such dependencies, SQL Server has a deadlock monitor that periodically reviews deadlock scenarios. The deadlock monitor will select one of the transactions as a deadlock victim and terminate the transaction with an error. This will free the other transaction to complete its operation. The deadlock victim is selected based on the setting of DEADLOCK_PRIORITY. The lock monitor will select the deadlock victim that has a lower value of DEADLOCK_PRIORITY.

Deadlocks are also created when concurrent users try to hit the same database resource with multiple transactions.

You can generate deadlock graphs using SQL Server Profiler. The deadlock graphs will portray the sessions or transactions and resources that are included in the deadlock situation.

In this exercise, you will manage deadlocks.