How to resolve deadlocks in SQL Server 2005 and 2008?

---

Goto blog home  |  Visit my NEW BLOG dedicated to Embedded Programming & IOT

---

Many of my friends and well wishers have asked me to start writing again. So here is the first one after a long gap of four long years of fire fighting with complex and critical problems for many of my global clients.

I have been pulled into many database deadlock situations in the past and today I decided to write about a generic approach using which one can easily identify and resolve deadlocks in SQL Server databases.

So, what would you do if your customer complaints about severe deadlock issues originating from the SQL server database? First, you will have to setup the SQL Profiler tool in order to capture deadlock event traces (Deadlock Graphs) followed by analyzing the Deadlock Graphs and then implementing an appropriate solution. Remember, the rule of thumb is to minimize the number of table rows being locked exclusively and for the minimum amount of time. The sequence of steps to be taken in order to resolved deadlocks in SQL Server database are listed below:

1. Identify deadlock traces (aka. Deadlock Graphs) using SQL Profiler
2. Analyze the Deadlock Graphs to identify root causes
3. Devise and implement an appropriate solution

In this section we will dissect each one of the above three listed steps and see how to leverage them to resolve the deadlock problems in SQL Server databases in general.

Step-1: How to identify the deadlock traces?

Follow the steps listed below in order to setup SQL Server Profiler to listen for each deadlock occurence and dump the details of the contending processes in an xml file. By analyzing the XML file contents you should be able to determine the root cause and fix the deadlocks.

1. Launch your SQL Server Management Studio
2. Then navigate to the Tools > SQL Server Profiler
3. In the SQL Server Profiler screen, click File > New Trace
4. You will be prompted to login to a SQL Server Instance
5. After logging in to the database to be profiled, in the Trace Properties screen, goto the Events Selection tab.
6. In the Events Selection tab, select the "Deadlock Graph", "Lock:Deadlock" and "Lock:Deadlock Chain".
7. Then goto the Events Extraction Settings tab and select the option to "Save Deadlock XML events separately".
8. That's it. Now start the Profiling session.
9. Then start running the problematic area of the application that is prone to generating deadlocks.
10. And then check the output folder where the deadlock graphs (.xdl files) would get generated by the profiler.

Step-2: Analyzing the deadlock graphs (.XDL files)

Each deadlock graph, the .xdl files can be double clicked to open them in a SQL Server Management window. Once I got used to it, I found it very comfortable opening them with Internet Explorer and simply perusing through the XML content without the visual representation. However, to begin with let us now see how the deadlock graph looks like and how to interpret the contents. A sample deadlock graph is displayed below.

Figure 1 - A sample deadlock graph file

The visual graph above shows two contenders (a victim process and a winner process). The process marked with a blue cross is the victim process that was killed by SQL Server to end the deadlock situation. Whereas the other process is the winner process as it was not killed by SQL Server. Think of each process as a SQL statement that tries to perform some DML on a table row or set of rows. For example, when both processes try to update the same set of rows, usually a deadlock situation arises.

As a next step closely inspect the SQL statements in the victim and the winning processes. Most often the answer is very obvious. Either a missing index needs to be created or the DML statements have to be re-written based on defensive programming principles.

Step-3: Creating an appropriate solution

Some of the time tested techniques of reducing the deadlocks are listed below.

1. Apply Missing Indexes
2. Decommission Unnecessary Transactions and Highly Restrictive Isolation Levels
3. Implement DML Statements with Minimal Locking using defensive programming

Where to applying missing indexes?

Let us consider the below scenario as an example. There are two SQL statements trying to update the Employee table records.

Figure 2 - Contending DML Statements

The first update statement tries to update all employee records belonging to DeptId=1 and whose JoiningDate='02-Aug-2015'. Whereas, the second update statement tries to update all employee records belonging to DeptId=2 and whose JoiningDate='03-Aug-2015'.

Let us say there are a total of 1000 records in the Employee table out of which 200 employees have DeptId=1 and 300 employees have DeptId=2.

For the purpose of this explanation, let us assume that the column 'DeptId' does not have an index defined on it. Therefore when the above SQL statements execute sinultaneously, both of them will try to acquire locks on all the 1000 employee records (irrespective of DeptId is 1 or 2). Well that is bad, as this situation may eventually lead to a deadlock between the two DML statements.

In order to reduce the scope of locking and resolve the deadlock the rule of thumb is to remember that row locking uses the indexes of the columns in the WHERE clause, we must add an Index on the "DeptId" column. Once the index is added, all the 1000 records will no longer get locked, instead only subsets of the rows based on their DeptId will get locked

A simple Chat application based on .NET Remoting

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------
It is good to be back writing again! This time on a simple chat application (windows console based). So here goes the C# code, just one single file named Server.cs, that is all you will need. Note that there are no validations done. Just take the exe and run from two client machines and enjoy chatting. Hope you will find it exciting. (Disclaimer: Tested over a LAN with host computers in the same domain. Not tested over the open Internet.)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Remoting;
using System.Runtime.Remoting.Channels;
using System.Runtime.Remoting.Channels.Http;
using System.Net;

namespace KChat
{
public class Server : MarshalByRefObject
{
private static Server _objRecipientServer = null;
private static Server _objCallingServer = null;
private static string _strCallingClientChatName = string.Empty;
private static string _strCallingClientIPAddress = string.Empty;
private static string _strCommandText = string.Empty;
private static string _strRecipientIPAddress = string.Empty;
private static string _strSenderChatName = string.Empty;
private static string _strSenderIPAddress = string.Empty;

static void Main(string[] args)
{
Server _objServer = new Server();
_objServer.StartSelfAsServer();
_strCommandText = Console.ReadLine();
bool _bContinue = true;

while (_bContinue)
{
if (_strCommandText.ToUpper() == "QUIT")
{
_bContinue = false;
break;
}
else if (_strCommandText.ToUpper() == "CONNECT")
{
Console.WriteLine("--------------------");
Console.Write("Enter your chat name: ");
_strSenderChatName = Console.ReadLine();
Console.Write("Enter your IP Address: ");
_strSenderIPAddress = Console.ReadLine();
Console.Write("Enter recipient IP Address: ");
_strRecipientIPAddress = Console.ReadLine();
Console.WriteLine("Chat Agent>> Initializing connection...");
_objServer.InitializeRecipient(_strRecipientIPAddress, _strSenderChatName, _strSenderIPAddress);
Console.WriteLine("Chat Agent>> Connected to " + _strRecipientIPAddress + "...");
}
else
{
if (_strSenderChatName.Length == 0)
{
Console.Write("Enter your chat name: ");
_strSenderChatName = Console.ReadLine();
}
Console.WriteLine("Chat Agent>> Sending...");
_objServer.DispatchMessage(_strCommandText);
Console.WriteLine("Chat Agent>> Message sent...");
}
_strCommandText = Console.ReadLine();
}
}

public void StartSelfAsServer()
{
HttpChannel _objChannel = new HttpChannel(1095);
ChannelServices.RegisterChannel(_objChannel, false);
RemotingConfiguration.RegisterWellKnownServiceType(typeof(KChat.Server), "Server", WellKnownObjectMode.Singleton);
Console.WriteLine("Chat Agent>> Chat server started.");
}

public void Listener(string strMessage)
{
Console.WriteLine(strMessage);
}

public void InitializeRecipient(string strRecipientIPAddress, string strCallingClientChatName, string strCallingClientIPAddress)
{
if (_objRecipientServer == null)
{
_objRecipientServer = (Server)Activator.GetObject(typeof(KChat.Server), "http://" + strRecipientIPAddress + ":1095/Server");
_objRecipientServer.RegisterCallingClient(strCallingClientChatName, strCallingClientIPAddress);
}
}

public void RegisterCallingClient(string strCallingClientChatName, string strCallingClientIPAddress)
{
Console.WriteLine("Chat Agent>> Incoming connection from person named '" + strCallingClientChatName + "' on client IP=" + strCallingClientIPAddress + " waiting for your acceptance...");
Console.WriteLine("You can start replying below...");
_strCallingClientChatName = strCallingClientChatName;
_strCallingClientIPAddress = strCallingClientIPAddress;

if (_objCallingServer == null)
{
_objCallingServer = (Server)Activator.GetObject(typeof(KChat.Server), "http://" + strCallingClientIPAddress + ":1095/Server");
}
}

public void DispatchMessage(string strMessage)
{
_objRecipientServer.Listener(_strSenderChatName + ">>" + strMessage);
}
}
}

Authenticating Windows NT Credentials via Form Input

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------
This week has been a busy one with little time for research. But then this topic turned out to be an exciting one. There will be a standard windows form (the web/asp version might need adjustments) with three textboxes: (1) txtUserName, (2) txtPassword and (3) txtDomain. The end user will enter any windows credentials into this form; and the code logic will validate the entered information is true or false - and you shall have an enterprise class login interface that you will often find in off the shelf enterprise products.

The C#.NET code lising is provided below (it can be done in other languages and platforms also):
------------------------------
using System;
using System.Windows.Forms;
using System.Runtime.InteropServices;

namespace WindowsAuthenticationDialog
{
public partial class Login : Form
{
[DllImport("ADVAPI32.dll", EntryPoint = "LogonUserW", SetLastError = true, CharSet = CharSet.Auto)]
public static extern bool LogonUser(string lpszUsername, string lpszDomain, string lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken);

public Login()
{
InitializeComponent();
}

private void btnLogin_Click(object sender, EventArgs e)
{
IntPtr _ptr = IntPtr.Zero;
bool _result = LogonUser(txtUserName.Text, txtDomain.Text, txtPassword.Text, 2, 0, ref _ptr);

if (_result)
{
MessageBox.Show("Access granted!");
}
else
{
MessageBox.Show("Incorrect credentials!");
}
}
}
}


The above code is actually calling a windows API (which is unmanaged) by importing the API DLL and declaring the LogonUser() function and passing appropriate arguments.

For detailed literature on using windows APIs or interoperability with unmanaged libraries you might want to consider the following references:

(1) http://msdn.microsoft.com/en-us/library/aa984739(VS.71).aspx
(2) http://www.pinvoke.net

Design Precaution with .NET Connection Pooling and IIS Virtual Directories

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------
A few months earlier, I was setting up a web service library which would contain multiple versions of the same web service. The business behind hosting multiple versions of the same service was that if any changes were made to the web service to alter the input or output then a new version would have to be hosted which would be used as the active version of the service. This strategy took care of backward compatibility in systems that were already consuming the sevice and saved significant cost of quality and testing.

The web services would look something like below in the IIS (real application names have been masked).

IIS Root
|__AppName.ModuleName.WebServices
|__v1.0
|__ServiceName.asmx
|__v1.1
|__ServiceName.asmx
|__v1.2
|__ServiceName.asmx

-Each service version would contain 3 files:
(1) a DLL in the /bin folder
(2) an ASMX file and
(3) a WEB.CONFIG file.
-Each service version would be configured as an APPLICATION in IIS.
-All the services would connect to the same database (Oracle in this case).
-In the WEB.CONFIG file connection pooling would be true and pool size set to 25.
-The total number to allowed connections to the database was set to 300.

Now, there is a technical consideration to be made while this web hosting is done. Everytime a version of the service is hit for the first time, it will block 25 (pool size) connections. So if simultaneously two versions of the service hit the database then 50 connections will be blocked. Theoretically, 300 connections would be able to support only 300/25 = 12 versions of the service hitting simultaneously. This meant that if there is ever more than 12 versions of the same service hitting the database simultaneously, then the first 12 services will succeed. What happens to the 13th request - well I guess it will error - but I haven't tested it out. If you happen to read my article and test it please post the results.

In my case, a workaround was adopted - we simply decided to turn off connection pooling on the earlier versions and keep it active only in the most recent version.

Finding Oracle Tablespace Utilization (Free v/s Consumed space)

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------

Last week I was working on my project when we discovered that space monitoring was not active in our application's database. During the process of addressing this problem, I figured out how we can easily query the database to get a report on various tablespaces and their free space. The query I used is provided below (the results are in MBs), the Oracle version I used was 10g.

SELECT
tablespace_name,
SUM(space)/1024/1024 "Size MB",
SUM(free)/1024/1024 "Free MB",
(1-(sum(free)/1024/1024)/(sum(space)/1024/1024))*100 "% Used"
FROM
(
SELECT
tablespace_name,
sum(bytes) space,
0 free
FROM dba_data_files
GROUP BY tablespace_name
UNION
SELECT
tablespace_name,
0 space,
sum(bytes) free
FROM dba_free_space
GROUP BY tablespace_name
)
WHERE TABLESPACE_NAME like UPPER('%')
GROUP BY tablespace_name
order by (1-(sum(free)/1024/1024)/(sum(space)/1024/1024))*100;

Dynamically Parsing Fields/Columns From a DataReader

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------
Today I was faced with a situation where I had to write a data layer function using ADO.NET that would be able to read the resultset from a stored procedure irrespective of number of field/columns and their names. And eventually serialize the stored procedure output for consumption through a web service. Here is the code listing:
-------------------------------------------------------
public DataSet GetDataTableFromReader(string strDataSetName, string strDataTableName, OracleDataReader objReader)
{
DataSet _dsResult = new DataResult(strDataSetName);
DataTable _dtResult = new DataTable(strTableName);
DataRow _dtRow;

DataColumn _dc = new DataColumn();
for (int i = 0; i < objReader.FieldCount; i++)
{
_dc.ColumnName = objReader.GetName(i);
_dtResult.Columns.Add(_dc);
_dc = new DataColumn();
}
if (objReader.HasRows)
{
while (objReader.Read())
{
_dtRow = _dtResult.NewRow();
for (int i = 0; i < objReader.FieldCount; i++)
{
_dtRow[objReader.GetName(i)] = objReader[objReader.GetName(i)];
}
_dtResult.Rows.Add(_dtRow);
}
_dsResult.Tables.Add(_dtResult);
}
_dc.Dispose();
_dtRow = null;
_dtResult.Dispose();
return _dsResult.GetXML();
}

Sideways Sliding DIV (Animated Sideways Sliding Panel in Javascript)

-------------------------------------------------------
Goto blog home
Visit my new blog dedicated to Internet of Things, Embedded Programming & Automation
-------------------------------------------------------
Last week I was in need of a DIV that could slide sideways. My search on the internet led me to an useful article on up and down sliding DIV by Harry Maugans. But I could not find a sideways sliding DIV that could be readily used. So I decided to write my own. The JS code is listed below.

Start out by placing a hyperlink and a DIV on your web page, as shown below.

<a href="javascript:;" id="linkid" onmousedown="SlideToggle('mydiv', 20, 200);"></a><br /> <div id="mydiv" style="border-bottom-style: solid; border-left-style: solid; border-right-style: solid; border-top-style: solid; display: none; overflow: hidden;"><br /> Actions<br /> <hr/><br /> Add<br /> Edit<br /> Activate<br /> DeActivate</div>

Next, place the following javascript code within the head tag of your web page. Just remember to remove all the BR tags from the JS code below :)

<script type="text/javascript"><br /> var timer;<br /> <br /> function Init(linkID, divID)<br /> {<br /> document.getElementById(divID).style.display='block';<br /> document.getElementById(divID).style.width=200;<br /> document.getElementById(linkID).innerText = '<<'; } function SlideToggle(linkID, divID, stepSize, panelWidth) { var frequency = panelWidth/stepSize; //If width is not already zero, then prepare parameters to shrink the DIV if(parseInt(document.getElementById(divID).style.width) != 0 && document.getElementById(divID).style.width != '') { panelWidth = 0; stepSize = -stepSize; } else { if(document.getElementById(divID).style.width == '') { document.getElementById(divID).style.width = stepSize; } } timer = setInterval('Slide(\''+linkID+'\', \''+divID+'\', '+stepSize+', '+panelWidth+')', frequency);</P><p>}<br /> <br /> function Slide(linkID, divID, stepSize, panelWidth)<br /> {<br /> if(panelWidth != 0 && parseInt(document.getElementById(divID).style.width) < panelWidth)<br /> {<br /> document.getElementById(divID).style.width = parseInt(document.getElementById(divID).style.width) + stepSize;<br /> }<br /> else if(panelWidth == 0 && parseInt(document.getElementById(divID).style.width) > panelWidth)<br /> {<br /> document.getElementById(divID).style.width = parseInt(document.getElementById(divID).style.width) + stepSize;<br /> }<br /> else<br /> {<br /> clearInterval(timer);<br /> if(document.getElementById(linkID).innerText=='<<')<br /> {<br /> document.getElementById(linkID).innerText = '>>';<br /> }<br /> else<br /> {<br /> document.getElementById(linkID).innerText = '<<';<br /> }<br /> }<br /> }<br /> </script>

And of course don't forget to initialize the the DIV on page onload event by calling the Init function.

<body onload="Init('linkid', 'mydiv')">