Follow-up on My Locking Issues
Posted On Wednesday, August 22, 2007 at at Wednesday, August 22, 2007 by Ben HI 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.