70-432 : Lock Events, SQL Server Profiler & Deadlock examples

This blog article is aimed at people preparing for 70-432 exam and is an overview of lock event category with sample examples of how and when to use it

The 70-432 exams requires you are comfortable with the terminology around "lock events", a good place to start is simply referring to msdn documentation:

Locks Event Category

Use the event classes in the Locks event category to monitor locking activity in an instance of the Microsoft SQL Server Database Engine. These event classes can help you investigate locking problems caused by multiple users reading and modifying data concurrently.

Because the Database Engine often processes many locks, capturing the Locks event classes during a trace can incur significant overhead and result in large trace files or tables.

In This Section

Topic	Description
Deadlock Graph Event Class	Provides an XML description of a deadlock.
Lock:Acquired Event Class	Indicates that a lock has been acquired on a resource, such as a row in a table.
Lock:Cancel Event Class	Tracks requests for locks that were canceled before the lock was acquired (for example, to prevent a deadlock).
Lock:Deadlock Chain Event Class	Monitors when deadlock conditions occur and which objects are involved.
Lock:Deadlock Event Class	Tracks when a transaction has requested a lock on a resource already locked by another transaction, resulting in a deadlock.
Lock:Escalation Event Class	Indicates that a finer-grained lock has been converted to a coarser-grained lock.
Lock:Released Event Class	Tracks when a lock is released.
Lock:Timeout (timeout > 0) Event Class	Tracks when lock requests cannot be completed because another transaction has a blocking lock on the requested resource. This event occurs only in situations where the lock time-out value is greater than zero.
Lock:Timeout Event Class

http://msdn.microsoft.com/en-us/library/ms177493.aspx

Next I want to mention a very interesting presentation I went to last year, I did blog about this last year:

tech: Deadlocks due collisions in "SQL Server's hashing algorithm" (June 2009 SQL Server UserGroup)

At the SQLServer usergroup there was a brief overview of a very technical problem - there appears to be a bug in SQLServer's "hashing algorithm" which under certain circumstances is triggering "deadlocks" to occur.

James' detailed blog:

http://blogs.conchango.com/jamesrowlandjones/archive/2009/05/28/the-curious-case-of-the-dubious-deadlock-and-the-not-so-logical-lock.aspx?CommentPosted=true#commentmessage

gives plenty of technical details behind this intriguing problem, and also the extensive collaborative investigation it took to unearth and document this bug.

I have gone through this great blog piece, highlighting what I see as the key concepts and steps. I have add some further references to key background concepts. This shows the process I went through to understand this issue, hopefully you find my comments a helpful introduction into understand this complex topics.

http://davetravelogue.blogspot.com/2009/06/tech-deadlocks-due-collisions-in-sql.html

This is a very interesting bug (complex and very rare) in SQL Server, the presentation at the London SQL Server UserGroup (www.sqlserverfaq.com) was very good and afterwards, I went though the details of the Conchango / EMC blog post go through the article. In my blog post ()

Don't want worry if you are struggling to follow this - this is very advanced/senior DBA material. However I would draw you're attention to the deadlock graph event, which can represented graphically or in XML format (see graphic at top of this page). This sort of information is similar to the data held within the Oracle dba_blockers view.

For a simpler / clean example of deadlocks Peter Wardy has written a good blog article which is easy to understand and recreate:

Creating a Deadlock

Below are the steps to generate a deadlock so that the behaviour of a deadlock can be illustrated:

-- 1) Create Objects for Deadlock Example
USE TEMPDB

CREATE TABLE dbo.foo (col1 INT)
INSERT dbo.foo SELECT 1

CREATE TABLE dbo.bar (col1 INT)
INSERT dbo.bar SELECT 1

-- 2) Run in first connection
BEGIN TRAN
UPDATE tempdb.dbo.foo SET col1 = 1

-- 3) Run in second connection
BEGIN TRAN
UPDATE tempdb.dbo.bar SET col1 = 1
UPDATE tempdb.dbo.foo SET col1 = 1

-- 4) Run in first connection
UPDATE tempdb.dbo.bar SET col1 = 1

Connection two will be chosen as the deadlock victim

ie.

Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Published Monday, December 12, 2005 11:00 AM by peter@wardyit.com

http://wardyit.com/blog//blog/archive/2005/12/12/65.aspx

Lastly I also found a very well written blog post by Brad McGehee

Brad is an Industry speaker, writer, and consultant on Microsoft SQL Server, specializing in SQL Server performance tuning, clustering, and high availability. He is the founder of www.SQL-Server-Performance.Com, and oversaw its growth to 350,000 visits each month. He is now Director of DBA Education at Red Gate Software. Brad is a frequent speaker at SQL PASS, SQL Connections, SQL Server user groups, and other industry seminars, and he is the author or co-author of more than 12 technical books and over 100 published articles. He spends what time he has left with his family in Hawaii. He is a Microsoft SQL Server MVP, MCSE+I, MCSD, MCT See also ..

The whole article is well worth reading but the following section is probably particularly relevant for the 70-432 exam:

To capture a SQL Server trace using SQL Server Profiler, you need to create a trace, which includes several basic steps:

1) You first need to select the events you want to collect. Events are an occurrence of some activity inside SQL Server that Profiler can track, such as a deadlock or the execution of a Transact-SQL statement.

2) Once you have selected the events you want to capture, the next step is to select which data columns you want to return. Each event has multiple data columns that can return data about the event. To minimize the impact of running Profiler against a production server, it is always a good idea to minimize the number of data columns returned.

3) Because most SQL Servers have many different users running many different applications hitting many different databases on the same SQL Server instance, filters can be added to a trace to reduce the amount of trace data returned. For example, if you are only interested in finding deadlocks in one particular database, you can set a filter so that only deadlock events from that database are returned.

4) If you like, you can choose to order the data columns you are returning, and you can even group or aggregate events to make it easier to analyze your trace results. While I do this for many of my traces, I usually don’t bother with this step when tracking down deadlock events.

5) Once you have created the trace using the above steps, you are ready to run it. If you are using the SQL Server Profiler GUI, trace results are displayed in the GUI as they are captured. In addition, you can save the events you collect for later analysis.

Now that you know the basics, let’s begin creating a trace that will enable us to collect and analyze deadlocks.

Selecting Events

While there is only one event required to diagnose most deadlock problems, I like to include additional context events in my trace so that I have a better understanding of what is happening with the code. Context events are events that help put other events into perspective. The events I suggest you collect include:

· Deadlock graph

· Lock: Deadlock

· Lock: Deadlock Chain

· RPC:Completed

· SP:StmtCompleted

· SQL:BatchCompleted

· SQL:BatchStarting

http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/