Creating Deadlock Resistent Apps

I wrote this for SQLServerCentral.com, and since they do not ask to own the content of their authors, I figured I'd cross-post it on my blog:

In my experience, one of the biggest problems in building applications and databses is dealing with deadlocks, and other resource contention issues. A dilemma in the process of building a database application is the lack of load that may exist in the development environment; in the real world, there's plenty of built-in load that comes with multiple users using the database. It's not as easy in a simulated environment.

Sure, there are applications you can build to simulate the sort of activity that may exist in a customer's environment, but since everyone seems to be cash-strapped right now, I thought I would share with you one way I've come up with to test how deadlock and timeout resistent an application is.

The first step, in this method, is to find out which tables in the database are most commonly used. Assuming that the way the application has been used in your simulated environment is close to the way it is used in production environments, the below query (>SQL 2005) should suffice as a way to determine this:

   DECLARE @YourDatabaseName varchar(125)
   SET @YourDatabaseName='LC_OTORL'

   exec('USE ' + @YourDatabaseName)
   SELECT
   DB_NAME(ius.database_id) AS DBName,
   OBJECT_NAME(ius.object_id) AS TableName,
   SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TimesAccessed
   FROM sys.indexes i
   INNER JOIN sys.dm_db_index_usage_stats ius
   ON ius.object_id = i.object_id
   AND ius.index_id = i.index_id
   WHERE
   ius.database_id = DB_ID()
   AND DB_NAME(ius.database_id)=@YourDatabaseName
   GROUP BY
   DB_NAME(ius.database_id),
   OBJECT_NAME(ius.object_id)
   ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC



If your simulated environment does not provide accurate data about the most-used tables in a production environment, this method may include running the above query against one of your customers' databases.

In either case, assuming you've got accurate data from the above query about what your most frequently accessed tables are, you can build a simple TSQL-based job that leverages this information to create simulated contention in your database. This job can run every 30 seconds, minute, or several minutes. It depends on what makes sense in your test environment.

For a methodology one could use to create contention, the easiest way to do so is to disregard the long-held convention that one should abstain from holding a transaction open for too long. In other words, creating an unnecessarily long and ineffective transaction, and running them over-and-over again will wreak havoc on how your application interacts with the database, thus giving you a clear answer as to whether or not your application is deadlock resistent, or at the very least, can recover from a query timeout.

To create such an evil-looking script, I'll use a cursor to build it. Below is the concoction I've created:

   DECLARE @TableName varchar(125)
   DECLARE @NumberOfTimesAccessed int
   DECLARE @YourDatabaseName varchar(125)
   DECLARE @ThreshholdNumberOfTimesAccessed int
   DECLARE @UpdateQueryText varchar(255)
   DECLARE @TargetedColumnName varchar(255)

   /*
   *
   * Use this variable to prevent locking on
   * a table if it hasn't been accessed enough
   * In this example, locking will not be
   * simulated if the table has not been
   * accessed at least 5 times
   * This is a good candidate variable for parameterization,
   * If you choose to make this script into a stored procedure
   *
   */
   SET @ThreshholdNumberOfTimesAccessed=5

   SET @YourDatabaseName='ADT'
   exec('USE ' + @YourDatabaseName)
   BEGIN TRANSACTION
   DECLARE MostUsedTables_cur CURSOR
   FOR
      /*
      * This query gets the most recently used tables in the database.
      * As time goes on, this script becomes more targeted, as the
      * most frequently used tables in the database get used more and more
      */
      SELECT
      OBJECT_NAME(ius.object_id) AS TableName,
      SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TimesAccessed
      FROM sys.indexes i
      INNER JOIN sys.dm_db_index_usage_stats ius
      ON ius.object_id = i.object_id
      AND ius.index_id = i.index_id
      WHERE
      ius.database_id = DB_ID()
      AND DB_NAME(ius.database_id)=@YourDatabaseName
      GROUP BY
      DB_NAME(ius.database_id),
      OBJECT_NAME(ius.object_id)
      ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

   Open MostUsedTables_cur
      Fetch NEXT FROM MostUsedTables_cur INTO @TableName, @NumberOfTimesAccessed
      While (@@FETCH_STATUS = 0)
      BEGIN
      IF @NumberOfTimesAccessed >= @ThreshholdNumberOfTimesAccessed
      BEGIN
      /*
      * The below query grabs an arbitrary, non-primary key column
      * in the current table, and assembles an update statement
      * to set the column value equal to itself, so this behavior
      * should be relatively low-risk
      */
      SET @TargetedColumnName = ( select top 1 co.[name] as ColumnName
      FROM sys.indexes i
      JOIN sys.objects o on i.object_id = o.object_id
      JOIN sys.index_columns ic on ic.object_id = i.object_id
      AND ic.index_id = i.index_id
      JOIN sys.columns co on co.object_id = i.object_id
      AND co.column_id = ic.column_id
      WHERE i.is_primary_key = 0
      AND o.[type] = 'U'
      AND o.name = @TableName )
      SET @UpdateQueryText = 'UPDATE ' + @TableName + ' SET ' + @TargetedColumnName + ' = ' + @TargetedColumnName

      /*
      * Commenting the below Print line out, but if you wish to debug
      * this script, simply Uncomment the Print line, and
   *comment the exec() line
      */
      -- PRINT @UpdateQueryText


      exec(@UpdateQueryText)

      /*
      * The below command waits 5 seconds. You can parameterize this to
      * make it more configurable on-the-fly
      * The effect is that the transaction stays open for
      * a long time
      */
      WAITFOR DELAY '00:00:05'
      END
      Fetch NEXT FROM MostUsedTables_cur INTO @TableName, @NumberOfTimesAccessed
      END

   CLOSE MostUsedTables_cur
   DEALLOCATE MostUsedTables_cur
   --Commit the transaction here, thus ending the long-held open transaction
   COMMIT



The above script, when run as a job, and possibly parameterized into a stored procedure, can really wreak havoc on a database, particularly if your database is large. The considerations to keep in mind will revolve around the following:
1. Setting the @ThreshholdNumberOfTimesAccessed value correctly. If your database is highly used, the value of this variable should be high
2. Setting the WAITFOR DELAY value appropriately. Setting it too long will create a very long script, and if this script is being run in the context of a SQL Agent job, the script may not be done running before the next iteration begins
3. Frequency of running the script. If running as a SQL Agent job, make sure to couple the frequency of the job with how the system is being tested.

If used correctly, the above script can become another tool for appropriately testing your system, and could perhaps be a shell of your stress test methodology. Obviously, this script is not the be-all-end-all for all testing, but I believe that it is a high value tool that can definitely add value in diagnosing how well your application can recover from a bad database environment.

The Observer Design Pattern

I've mentioned before that I don't get too wrapped up in design patterns because of the tendency to overuse them, or to use the wrong one, resulting in overly-complicated code. There are, however, times when a design pattern is the exact solution for a given problem.

As an academic experiment, I recently looked into the "Observer" design pattern, which is probably one of the coolest of the design patterns (IMHO). The Observer design pattern is also called a "Publish-Subscribe" design pattern, because it creates a platform by which some entity can maintain a list of publishers and subscribers, and map them together. Then publishers and subscribers can send and receive messages, and have some level of confidence that the messages get to where they need to go.

The implementation of the Observer pattern I used was such that there was a managing "Engine" that kept track of all the subscriptions, and when it was notified of an event, it would notify all subscribers of that particular event. It was then the responsibility of the subscriber to either accept or disregard a message, based on the subject of the message.

The Observer pattern is not needed by all conceivable applications; in fact, most applications would not need it at all. So, one of the things I wanted to do was to think of a fun example of how one could use the Observer pattern. My solution: a Television analogy. A television has commercials. A television has people who watch it. Some of those people are shopping for the products that the commercials contain. For instance, some people are in the market to purchase furniture. Some people are in the market to purchase a car; therefore, the television is a medium for makers of those products to put commercials in them for watchers to watch. If a viewer of the television is not currently shopping for it, then they simply disregard the commercial.

One of the downfalls of the Observer pattern is that, because it is the role of the subscriber to either pay attention to or disregard a message, excess traffic gets generated. In my implementation of the Observer pattern, it is scalable to deal with this issue fairly easily, by switching this responsibility from the potential subscriber to the engine that manages the list.

Below are the list of classes I created, along with their definition:

Subject - An abstract class that other classes will implement. A subject, in this example, is the TV commercial subject. For instance, a Car Commercial would be of type CarCommercial, which inherits from Subject.


public abstract class Subject
{
   protected List<Observer> _listOfObservers;
   public string SubjectName;

   protected SubscriptionEngine _engine;

   public Subject(SubscriptionEngine connectingEngine)
   {
      _engine = connectingEngine;
   }

   public abstract void Attach(Observer o);

   public abstract void Detach(Observer o);

   public virtual void Notify()
   {
      foreach (Observer obs in _listOfObservers)
      {
         _engine.Notify(this);
      }
   }

   public abstract string State
   {
      get;
      set;
   }
      
}


CarCommercial - Concrete class that inherits from Subject, and represents one of the Subjects that the engine will know about.

public class CarCommercial : Subject
{
   private string _state;

   public CarCommercial()
   : base(null)
   {
      this.SubjectName = "Car Commercial";
   }

   public CarCommercial(SubscriptionEngine engine) : base(engine)
   {
      this.SubjectName = "Car Commercial";
   }

   public override string State
   {
      get { return _state; }
      set { _state = "Hello"; }
   }
   public override void Attach(Observer o)
   {
      _engine.Register(this, o);
   }

   public override void Detach(Observer o)
   {
      _engine.Unregister(this, o);
   }
}

FurnitureCommercial - Concrete class that inherits from Subject, and represents one of the Subjects that the engine will know about.

public class FurnitureCommercial : Subject
{
   private string _state;

   public FurnitureCommercial()
   : base(null)
   {
      this.SubjectName = "Furniture Commercial";
   }

   public FurnitureCommercial(SubscriptionEngine engine)
   : base(engine)
   {
      this.SubjectName = "Furniture Commercial";
   }

   public override string State
   {
      get { return _state; }
      set { _state = value; }
   }

   public override void Attach(Observer o)
   {
      _engine.Register(this, o);
   }

   public override void Detach(Observer o)
   {
      _engine.Unregister(this, o);
   }
}

Observer - An abstract class that represents a television viewer, in this example. An observer, in the code created, can be either a CarShopper or a FurnitureShopper

public abstract class Observer
{
   protected SubscriptionEngine _engine;

   public string Name;

   public List<Subject> ListOfSubjectsSubscribedTo;

   public Observer(SubscriptionEngine engine)
   {
      ListOfSubjectsSubscribedTo = new List<Subject>();
      _engine = engine;
   }

   public abstract bool Update(Subject s);
}

CarShopper - Concrete class that implements Observer. Represents a TV Viewer who cares about CarCommercials

public class CarShopper : Observer
{
   public CarShopper(SubscriptionEngine e) : base(e)
   {
      ListOfSubjectsSubscribedTo.Add(new CarCommercial());
      this.Name = "Car Shopper";
      _engine.Register(ListOfSubjectsSubscribedTo[0], this);
   }

   public override bool Update(Subject s)
   {
      foreach (Subject subject in this.ListOfSubjectsSubscribedTo)
      {
         if (subject.SubjectName == s.SubjectName)
         {
            return true;
         }
      }
      return false;
   }
}

FurnitureShopper - Concrete class that implements Observer. Represents a TV Viewer who cares about FurnitureCommercials.

public class FurnitureShopper : Observer
{
   public FurnitureShopper(SubscriptionEngine e) : base(e)
   {
      this.Name = "Furniture Shopper";
      ListOfSubjectsSubscribedTo.Add(new FurnitureCommercial());
      _engine.Register(ListOfSubjectsSubscribedTo[0], this);
   }

   public override bool Update(Subject s)
   {
      foreach (Subject subject in this.ListOfSubjectsSubscribedTo)
      {
         if (subject.SubjectName == s.SubjectName)
         {
            return true;
         }
      }
      return false;
   }
}

Subscription - Class (could have easily been a Struct) that represents a mapping between an Observer (TV Watcher) and Subject (TV Commercial)

public class Subscription
{
   private Subject _subject;
   private Observer _observer;

   public Subscription(Subject s, Observer o)
   {
      _subject = s;
      _observer = o;
   }

   public Subject Subject_
   {
      get { return _subject; }
   }

   public Observer Observer_
   {
      get { return _observer; }
   }
}

SubscriptionEngine - The engine that maintains a list of Subscriptions, and handles the actual notification, in the event that a Subject is sent. The way this is implemented is in it's public method Notify(). The parameter in Notify is a Subject. So therefore, the SubscriptionEngine would go through its list of subscribers, and send to all Observers. It would be the Observer's responsibility of whether or not they care.

public class SubscriptionEngine
{
   List<Subscription> _listOfSubscriptions;

   public SubscriptionEngine()
   {
      _listOfSubscriptions = new List<Subscription>();
   }

   public bool Contains(Subscription item)
   {
      foreach (Subscription subscription in _listOfSubscriptions)
      {
         if (subscription.Observer_.Name == item.Observer_.Name
         && subscription.Subject_.SubjectName == item.Subject_.SubjectName)
            return true;
      }
      return false;
   }

   public void Register(Subject s, Observer o)
   {
      Subscription item = new Subscription(s, o);
      if (!this.Contains(item))
      _listOfSubscriptions.Add(new Subscription(s, o));
   }

   public void Unregister(Subject s, Observer o)
   {
      for (int i = 0; i < _listOfSubscriptions.Count; i++)
      {
         if (_listOfSubscriptions[i].Observer_ == o && _listOfSubscriptions[i].Subject_ == s)
            _listOfSubscriptions.RemoveAt(i);
      }
   }

   public void Notify(Subject s)
   {
      foreach (Subscription subscription in _listOfSubscriptions)
      {
         if (subscription.Observer_.Update(s))
            MessageBox.Show(s.SubjectName + " has sent a message to " + subscription.Observer_.Name);
      }
   }
}

Television - A simple Form, which acts as an intermediary between the SubscriptionEngine and the Subjects and Observers.

public partial class Television : Form
{
   SubscriptionEngine _engine;

   public Television()
   {
      _engine = new SubscriptionEngine();
      InitializeComponent();
   }

   private void onTelevisionLoad(object sender, EventArgs e)
   {
      this.showCommercials();
   }

   private void showCommercials()
   {
      FurnitureCommercial furnitureCommercial = new FurnitureCommercial(_engine);
      CarCommercial carCommercial = new CarCommercial(_engine);

      FurnitureShopper furnitureShopper = new FurnitureShopper(_engine);
      CarShopper carShopper = new CarShopper(_engine);

      furnitureCommercial.Attach(furnitureShopper);
      carCommercial.Attach(carShopper);

      _engine.Notify(furnitureCommercial);
      _engine.Notify(carCommercial);
   }
}

The way I see the Observer pattern working is a little bit different that it was laid out by the Gang Of Four, or other authors. In the GOF's implementation, the Subject has more "smarts" than I think it needs. The way I see this pattern, the Subject ought to be rather "dumb," and the responsibility for doing stuff should lie with the Observer and the SubscriptionEngine.

For more info about the Observer pattern, check out http://dofactory.com/Patterns/PatternObserver.aspx

Inheriting from .Net base controls

.Net Controls (and their Win32 ancestors), such as TextBox, ComboBox, DataGridView, etc, were built to be decoupled from any one application; they have to be.  These controls need to be used by tens or hundreds of thousands of developers building any number of types of applications.

The trouble with generic, decoupled code (and APIs) is that it is so loosely coupled from the rest of the application, that it takes finangling to get the loosely coupled code to work with the rest of your tightly coupled code.

This is a case study in inheritance, and my guess is that most software engineers use inheritance to build custom controls (or build their own controls, or purchase them from external vendors, such as SyncFusion).  For the context of this blog, I'll simply address using inheritance to extend base .Net classes for your own purposes.

When building a class (in this case, a Control), there are 3 strategies one can use to look at the class:
1.  Set all attributes, and interact with class behavior from outside of the class
2.  Set all attributes, and interact with class behavior from inside of the class
3.  Do some combination of 1 and 2.

Most code falls into category #3, as is the appropriate thing to do.  In the case of Controls (and many other code constructs, as well), the difference between tightly coupled code and loosely coupled code is what percentage of behavior is set from outside of the class, versus what percentage is set from within the class.  Another component of the tightly versus loosely coupled code is how much the code knows about the data structures and code that you've built into your application.

In the case of .Net controls, the controls don't, by default, know anything at all about the classes in your application; that is the trade-off you make with extending base control behavior:  the custom controls know about your code, but those controls are probably going to be meaningless outside of your code base because they have become so tightly coupled to your architecture.

So, on to an example.  Consider a DataGridView control.  Typically, if using the base .Net DataGridView, you instantiated it, put it on a control, and set the data source.  The setting of the data source is the "doing work from outside of the class."  Suppose you want to transfer that responsibility to the class.  You could extend the class as follows:


class CustomDataGridView : System.Windows.Forms.DataGridView
{
   public CustomDataGridView(DataTable dataSource)
    {

       ///Set the base data source to the passed
       //DataTable parameter

         base.DataSource = dataSource;

   }
}


The above is a simple example, but suppose you wanted multiple controls to behave in a similar way.  Since all controls have different behavior, and since a class (in .Net) cannot inherit from more than one class (outside of an inheritance chain), the solution is to create an Interface.  Consider the following example:


Interface ISaveableControl
{
  void Save();
}



Then I could have a couple controls implement it:

class CustomTextBox : System.Windows.Forms.TextBox, ISaveableControl
{
  public CustomTextBox(string defaultValue)
  {
     base.Text = defaultValue;
  }

  public void Save()
  {
     ///Go to the database and update the associated record with the associated value
  }
}

class CustomComboBox : System.Windows.Forms.ComboBox, ISaveableControl
{
  public CustomTextBox(string defaultValue)
  {
     base.Items.Add(defaultValue);
     base.Text = defaultValue;
  }

  public void Save()
  {
     ///Go to the database and update the associated record with the associated value
  }
}


If I were to add the above controls to a basic System.Windows.Form, I could do something after a button was clicked (or some other event was triggered):


foreach(Control ctrl in this.Controls)
{
  if(ctrl is ISaveableControl)
  {
     ((ISaveableControl)ctrl).Save();
  }
}


And Voila, extending base controls to be more tightly coupled with your app.  There are decisions that need to be made to determine if this is a strategy your software should contain, because even though there is a lot of value in tightly coupled code (especially controls), there is also some value in keeping things loosely coupled.

Collection Classes

In his book Prefactoring, Ken Pugh talks about collections of objects. Pugh's assertion is that: "If it has collection operations, make it a collection."

The above statement has been one of the most helpful axioms I have found in building object oriented applications. Before I was armed with the tool of the "Collection" class, I used some bastardization of arrays or lists, and did all their operations within the parent class. This made for pretty sloppy code. For instance, consider the following:

class ParentClass
{
   private List _childClassList;

   public ParentClass()
   {
      ///Create the new list object in constructor
      _childClassList = new List();
   }

   /*******************************
    * Here are all the behaviors
    * of the ParentClass object
    *******************************/

   public int GetChildClassCount()
   {
      return _childClassList.Count;
   }

   public void AddToChildList(string val)
   {
      _childClassList.Add(val);
   }

   public string GetLongestStringInChildClassList()
   {
      string longestString = String.Empty;

      foreach(string member in _childClassList)
      {
         if(member.Length > longestString.Length)
            longestString = member;
      }

      return longestString.ToUpper();
   }
}

The above code works, but the methods GetChildClassCount(), AddToChildList(), and GetLongestStringInChildClassList() don't belong in ParentClass. They belong in a separate class, and deserve their own separate implementations.

Before going further, bear in mind that as .Net progresses, many of these functionalities are becoming available in the "primitive types" that .Net offers. For instance, as of .Net 3.5, the List<> variable has a GetAverage() function that does this for you...it also has a lot of those other features; however, I believe that adding a layer of abstraction can be a powerful tool for both consistency of code in your application, and for coding standards, if you're working with multiple developers.

So, refactoring the above code, I would probably do something like this:

class ParentClass
{
   ///Notice here I substitute the List for a
   ///ChildClassCollection
   private ChildClassCollection _childClassList;   

   public ParentClass()
   {
      ///Create the new collection object in constructor
      _childClassList = new ChildClassCollection ();
   }

   /*******************************
    * Here are all the behaviors
    * of the ParentClass object
    *******************************/

   public void DoStuff(string input)
   {
       if( input.ToUpper() == _childClassList.GetLongestStringInChildClassList() )
          ///Do stuff here
   }

}

class ChildClassCollection
{
   private List _childClassList;

   public int GetChildClassCount()
   {
      return _childClassList.Count;
   }

   public void AddToChildList(string val)
   {
      _childClassList.Add(val);
   }

   public string GetLongestStringInChildClassList()
   {
      string longestString = String.Empty;

      foreach(string member in _childClassList)
      {
         if(member.Length > longestString.Length)
            longestString = member;
      }

      return longestString.ToUpper();
   }
}

In my mind, the above example of factoring out the first code example's behavior into 2 classes really looks better, and gets at something that I think is really important in building object oriented systems: class relationships.

Understanding relationships between different "things" in your code, and knowing how to represent them in a solid class relationship is key to building high quality code.