Ben H Blog - Keep it Simple, Then Refactor.

Recently my database has been experiencing some deadlock issues.  The issues are due to some automation we’ve created to update inventory levels.  This automation is close to real time.  Meaning the inventory table is constantly getting updates and inserts.  This caused all kinds of locks and blocking.  To start with check out this MSDN article on Locking Hints.

In my case, I started by using row locks when doing my updates.  Here is a sample of my changed SQL:

Update Inventory WITH (ROWLOCK) set CountOnHand = @CountCurrentOnHand, Price = @Price, InventoryDesc = @InventoryDesc where InvID = @InvID

That reduced the deadlocks, but did not solve them.  So, I also added the NOLOCK to the queries that access the inventory table on a regular basis.  Here is a sample of the SQL:

Select * from Inventory with (NoLock)

This can be risky because doing so is a ‘Dirty Read’.  This means I could read data that has not been committed and might possibly be rolled back. In my case the inventory levels are important, but they are also verified and corrected on checkout.  So, for the time being we are running in this manner and keeping an eye on things.  Any comments are welcomed.