Deadlocks in MSSQL 2005 Explained

30 Apr

Have you ever seen the below message during your load testing assignment working in the Microsoft .Net world involving ASP.NET and MSSQL Server,

Msg 1205, Level 13, State 51, Line 1

Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

If yes, then probably you need to read on this complete post because either you are testing with limited set of data or you are really seeing locking issues in your database,so I am hoping  that you will find it interesting to correlate deadlocks or locking contention issues with system performance or response time of the applications,

So let’s start with Microsoft definition of deadlocks which is excellent material to refer,

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. The following graph presents a high level view of a deadlock state where:

  • Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).
  • Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).

Diagram showing tasks in a deadlock state

Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.

The SQL Server Database Engine automatically detects deadlock cycles within SQL Server. The Database Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock.

Deadlocks often add to the wait time stats and most of the time becomes a silent performance bottlenecks which are hard to identify and troubleshoot, I call them as silent bottlenecks because you never know from the front end or from your load testing scripts as why a simple form insert or search is taking so much time to retrieve a data in spite of giving sufficient filter parameters in the search.

There also exist performance counters which can help to identify whether deadlocks are happening during the test run assuming that you see slowness but not any kind of errors during your test execution.

Below are some of the counters I often see in case its required to monitor for locks,

image

  • Lock wait time (ms) – Total time spend to acquire a lock for all DB Transactions.
  • Lock waits/sec – Number of times user has to wait to acquire a lock. Ideally value of 0 is expected here.
  • Average Wait Time (ms) – Wait time each lock request as to wait. More than 500ms often indicates blocking issues.
  • Lock requests/sec – The number of lock request per sec. High value often indicates that lot of rows are being accessed. So this indicates the application is heavily dependent on DB health. There exists some optimization scope to reduce DB calls.
  • Lock Timeout/sec – Time out that occurs due to locking issues. Sometimes these timeouts manifests as application errors. If you see frequent timeouts in applications during load tests, then probably you need to monitor this counter.
  • Number of deadlocks/Sec: These are pure play deadlocks where in process with low priority is rolled back. Error message shown earlier belongs to this category of locks. If your application handles all types of exceptions, then probably you will not see the above message other than some custom error message saying something went wrong with the application.

The above counters are extremely helpful in identifying the performance issues with regard to Database. In case if you have access to application database, then probably you can also go ahead and fire the below queries in SSMS,

DBCC TRACESTATUS (1222,-1);

GO

and

DBCC TRACESTATUS (1204,-1);
GO

When you execute the above queries in SSMS, it will give you status whether trace 1204/1222 trace flags are on for your database, these are 2 trace flags which needs to turned on for identifying and fixing the root cause of deadlocks.

In order to enable these flags, we can fire the below queries in SSMS,

DBCC TRACEON (1204, -1);
GO

and

DBCC TRACEON (1222, -1);
GO

By turning on both the trace flags ,we get valuable information with regard to resources/spid which are locked and reasons/queries which are locking or creating deadlocks.

As per Microsoft SQL Team,the importance and difference between these flags are when deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server 2005 error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.

In addition to above methods, I feel SQL Profiler also contains events that can be used to identify deadlocks quite easily and in fact does show right away the sql queries involved in deadlocks.I vividly remember that SQL Server 2000 profiler had this capability to detect deadlocks.

Now the big question,are deadlocks high priority performance defects, I personally feel that deadlocks are not high priority defects unless they are not memory locking contention issues or thread contention issues on the smaller scale application.However they can have big impact if your application has large user base and your application makes lot of DB Calls.Deadlocks are often associated with high wait times which directly contributes to the high latency on the front end side of the application.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: