Database Record Blocking Eye Opener - How Indexing can affect SQL-Server Blocking

Provided your system has a good volume of simultaneous users sooner or later a conversation concerning database record blocking will come up.  This article demonstrates a situation you may not think would cause a blocking event but does.  For me it was a real eye opener to blocking events in our system and how potentially to resolve them.

First is something we should all understand and that is what a simple database blocking event is.  A simple example is if two user's sessions attempt to update the same database record at the same time.  The first session updates the record while the second session has to wait until the first session commits it changes.  In X++ this is with a ttsCommit.   The second session is said to be blocked while it is waiting for the first session to complete. 

Now for the eye opening event.  In our system we encounter blocking even when users are not updating the same records.  At first we suspected locks getting escalated but this does not seem to be the case.  Next we thought that indexes were not being used but again not the case.  Let me demonstrate with an example.

First I create a table with users in it as seen below:

Next I create two jobs that I can run at the same time which update different records in the table.  The first job updates a record and then sleeps for 10 seconds to simulate other database actions that take 10 seconds to perform:

The second job updates a different record without any sleep commands:

Now I run the first job followed seconds later by the second job.  Looking at the resulting info logs we see that the second job had to wait for the first job to complete:

Below shows the index that the update is using which we verified by looking at the SQL plan.  It seems SQL is locking as many records as what matches the index.  In this case all records that matched on the LastName of 'Norman' get locked.  Note that dataArea and Partition are also involved but not shown by the AOT.

Now altering the index to include first name and running the jobs again the issue is no more:

What SQL-Server does here I am not sure but it was a real eye opener.  Now numerous blocking events we witness make sense.  Once this was clear our success rate at avoiding these types of events increased.  I was under the impression that SQL-Server supported record level locking but in this case it seems to be index level locking.  The finer the index the finer the locking.  I can share the xpo with anyone that wants to try this themselves.