a case of fixing SQL deadlocks without any code/schema changes

I work on high volume message processing app which is built on .NET, C#, MS SQL server and IBM WebSphere MQ. The application process messages to the tune of 200 messages a second. During LnP testing, there were deadlocks happening in few scenarios and we fixed all of them.

Recently there was a need to load data from the same MS SQL database tables, which are being updated from the HIGH VOLUME message flow 24X7. The new data load tool should run daily to pick the changes happened over last business day based on the message was last modified and send it another interfacing system.

The new tool developed to be a .NET Console app and scheduled to run every day using Windows Task Scheduler.

The tool ran for some days and we were monitoring logs. Unfortunately, the deadlocks issues were back again at the forefront. When the tool ran, the deadlocks were seen only with new tool DA layer logic but not with the original application which updates the same tables. This is because it's easy to ROLLBACK a SELECT only SQL TRANSACTION for SQL SERVER than to rolling back an Insert/Update/Delete SQL TRANSACTION. So the SELECT only queries used by the new tool were chosen as DEADLOCK VICTIM all the time.

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

With the above introduction (actually a bit long), let's get start to justify the blog post title.

After analyzing the logs which the tool produced we learned below:-

• Deadlocks were happening while loading the records which were being updated by the main process app at the same time.
• Analyzing the SELECT only queries which are used to load data in batches, we found no easy ways to optimize the queries to prevent deadlocks from happening. The queries were already optimized. No low hanging fruits.
• The tool was running to select changes which were happened over 24 hours basing its logic on the record last modified time.
• After doing analysis of record's last modified date-time we found that:-
  1. Most the records were heavily updated in the first hour they got created.
  2. Almost all the records were being modified only during the first 5-7 hours from their created time. This was an important clue to fix the deadlocks issue. we were facing.
• So instead of selecting the records modified in the last 24 hours, we selected new 24 hours date time range such that whose MAX date time is 8 hours less than current date time. In this new date range, there were only a few records were getting updated. Post this change in selection date-time range, the SELECT only queries were getting started to succeeded ALL THE TIME without being a victim of deadlocks.

Deadlock issues were resolved without any code or database schema changes. These Complex deadlocks issues were fixed by employing altogether a different approach to look at the problem.

so, THINK OUTSIDE THE BOX!!!

References

stackoverflow QNA on cause-of-a-process-being-a-deadlock-victim

MSDN article on Detecting and Ending Deadlocks

beware of static members while disposing the class object

When static members will be used in class design

Static members in a class are used when there is need to maintain data common to all instance objects of the class. We all aware of this.

Let's understand the problem

If such static members implement IDisposable interface, then you need to pay special attention while disposing the objects of the class. If you dispose static members just similar to instance class members as part the Dispose() method implementation, you will end up in inconsistent behavior.

As static members doesn’t belong to individual class objects alone, we shouldn’t be disposing static members while disposing off the class instance members.

Consider the below a sample class, which is holding static IDisposable interface.

public class SampleClass:IDisposable
{
  public static IDisposable serviceProvider = null;
  public void Process()
  {
  //Initialize serviceProvider when 
  // required based on the bussiness logic condition
  serviceProvider = new  ConcreteServiceProvider();
  }
    
  public void  Dispose()
  {
   serviceProvider.Dispose(); // WRONG

   // Do not dispose serviceProvider as part of 
   // the SampleClass dispose.
   // When you have got mutiple SampleClass 
   // instances in application,
   // one instance got diposed off
   // then it will dispose the serviceProvider 
   // static variable. 
   // Later other instances expecting valid value
   // on serviceProvider static variable
   // will behave incorrectly.
   }
}

Why must not dispose static member is Dispose method

In the above sample class, the Dispose() method is disposing off the static member of the class. That means, whenever you create new instance of the class again, you will end up creating new instance of the static member, but that isn't a desired behavior expected. Hence pay a special attention in your Dispose() method implementation not to dispose off the static members.

Then where and when to dispose off the static members of the class?

The best place to dispose off static members is your application Exit() method. Post that you will not be needing the static members to be available for use.

Difference between tight coupling and loose coupling

If you are new to software design and if you are wondering when people say:

• “this is a good loosely coupled design, good work. Keep it up.”

  OR

• “this is tightly coupled design, it's very rigid, not flexible enough, change it”

Then you have landed in the right place to know more on tight and loosely coupled design. In this post let’s talk about “what is tight coupling”, “what is loose coupling” in software design and discuss the difference between them.

Let’s consider you are working on a developing Graphical design application (similar to Photoshop, Corel Draw). As a graphics design app, you are asked to provide the below capabilities.

• Allows user to choose shapes.
• Allows you to print your drawing.
• Allows you to save your changes into database

To understand coupling between classes, let's consider an example and implement the 3rd point, “Saving into database”.

1. To do this let’s create repository class to save into SQL SERVER database.
   public class Repository 
   {
     public void Save(Drawing drawing)
      {
        using (SqlConnection connection = 
               new SqlConnection(ConnectionString))
         {
              connection.Open();
              using (SqlCommand command = 
                       connection.CreateCommand())
            {
               command.CommandText = "INSERT INTO TABLE...";
                //Add parameters
               command.ExecuteNonQuery();
            }
          }
       }
   }
2. Your app will use the Repository to save the drawing.
   Repository repository= new Repository ();
   repository.Save(drawing);

With the above implementation everything is working as expected when using SQL SERVER as a database. Your boss is happy and hence you are happy!

One fine day, one of your potential customer willing to buy your graphics design application. But the customer doesn’t have SQL Server license instead has the ORACLE database. But your application design is supporting only SQL Server database and your gut feeling is you can easily add support for Oracle DB.

Then you will start looking into the code to realize that it’s an uphill task to add any new database support. Entire application has been hardcoded to work just with SQL Server!.

1. It means your application has been hardcoded to work only with SQL Server as DB.
2. This called DIRECT COUPING. There is direct coupling your application and SQL SERVER DB.
3. With this approach to design you cannot easily replace components your application is using

Now having understand the problems of direct coupling let’s talk about how to eliminate direct coupling.

1. Let’s design the class as below:
   public interface IRepository
   {
    void Save(Drawing drawing);
   }
   
   public class SQLRespository:IRepository
    {
      void Save(Drawing drawing)
       {
         using (SqlConnection connection = 
             new SqlConnection(ConnectionString))
           {
             // Logic to save into SQL SERVER Database
           }
       }
    }
   
   public class OracleRespository:IRepository
    {
      void Save(Drawing drawing)
       {
         using (OracleConnection connection = new 
   
   OracleConnection(ConnectionString))
        {
          // Logic to save into ORACLE Database
        }
       }
    }
2. Let’s design your application to use IRepository instead of directly knowing the existence of either of SQLRepository and OracleRepository.
   public class YourApp
    {
      IRepository _repository = null;
   
      public YourApp(IRepository repository)
       {
         _repository = repository;
       }
      public void Save(Drawing drawing)
       {
         _repository.Save(drawing);
       }
    }
3. Your application isn’t hardcoded to use SQLRepository anymore. Now your design flexible to any kind of database repository which implements the IRepository interface. This means your application loosely coupled with database interaction via the interface.

4. Advantage of loosely coupled design

   1. If in future you want to add support for new database, say MySQL database, then you just create MySQLRepository class implementing the interface.
   2. This doesn’t need changes to your existing application code logic, so you save on regression effort required to support new databases.
   3. This means that just add a new repository and test that alone and use it with the application. It’s just as simple as that. With this loosely coupled design approach, your application development will be faster.

With loosely coupled design your application has dependency on a class implementing IRepository. This can be solved by using dependency injection.