I finally was able to fix the issue in my previous post. The problem was not the locking hint on the queries, but the fact that the column I was using in my where clause was not indexed. So, even though only the row was being locked the next update needed to do a complete table scan to find the next row to update. This table scan was blocked until the previous update finished. Since these updates were all happening in the same transaction, deadlock occurred. Once I indexed that column the issue went away.
Just a quick post about being on the bleeding edge. I’ve recently been working with Windows Workflow using the Service Broker to queue up my workflow requests asynchronously. This is a great concept, but I’ve been amazed at the lack of quality information on these two pieces of technology. Sure there is plenty of information about the theories of these technologies and how to get started. However, there is a huge void of information about how to debug and fix things when they break.
This is one of the problems with working with new technologies right out of the gates. Sure there will always be a learning curve when using technology that you are not familiar with. However, the magnitude of that curve is far greater when the technology itself is in it’s infancy. It takes much longer to get past an issue when you have to dig and use trial an error as opposed to searching the web and finding documentation, articles and\or blog posts on it.
This is the bane of my existence these days and I think there are many developers out there that would agree. As soon as I get over the learning curve for a piece of technology the new version of, or a replacement for, that technology is released and I have to go through it all over again. So, in essence I’m in a learning curve that never hits the down ward side of the curve!!!
In order to fulfill the demands of our clients we need to use the latest and greatest technologies (I’m getting pretty comfortable with all the Ajax stuff, now everyone wants to use Silverlight). Don’t get me wrong, I really like learning and using new technologies. Plus I take pride in being one of the first implementors of these new technologies. However, it would be nice to sit back and write ‘Hello World’ apps every once and awhile…
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:
UpdateInventory 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 * fromInventory 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.