Troubleshooting Table Locks

I recently worked on a case where a significant number of deadlocks were being generated for concurrent UPDATE operations. I looked at sys.dm_tran_locks and saw a significant number of table lock requests (with WAIT and CONVERT request_status) for the table in question. I used trace flag 1222 to look at the full deadlocking chain details, and found the associated UPDATE statements involved in both the winner and victim deadlock processes.

I examined the UPDATE statement, and I saw that the WHERE clause was very selective. In other words, the predicate in question should only have affected a single row at a time. The column was indexed, and the statistics were up to date – so nothing was out of the ordinary there. The query was not using locking hints and the isolation level for each session in the deadlock chain was READ COMMITTED.

I turned on profiler and looked for lock escalations during the frequent deadlock events. There were none happening. This told me that this was not a lock escalation issue (row converted to table, page converted to table), but rather, the initial lock granularity of the query was a table lock.

I looked at the execution plan for the UPDATE and saw that a single UPDATE impacted five different indexes (clustered index, and four non-clustered indexes). For kicks, I added a ROWLOCK hint to the UPDATE against a dummy record, to see what kind of initial locking behavior would be generated. When I did this, I received the following error message:

Msg 651, Level 16, State 1, Line 1

Cannot use the ROW granularity hint on the table "XYZTable" because locking at the specified granularity is inhibited.

That was an immediate prompt for me to check sys.indexes to determine if any indexes for the table had row and page locks disabled (if both are disabled, the only choice is a table lock):

SELECT name indexname

FROM sys.indexes

WHERE object_id = object_id('XYZTable') AND

               allow_row_locks = 0 AND allow_page_locks = 0

So sure enough, there was a single nonclustered index with both of these options disabled. Since the UPDATE required access to this particular index, an initial table-lock was being selected by SQL Server. There was no other choice since row and page locks were both disabled.

There may be situations where you wish to disable either row locks or page locks – however in my experience these situations are infrequent. If concurrency and throughput is important to your application, these index options should usually be left ON. If you are seeing unexpected table locks from queries that are highly selective, be sure to check these options via sys.indexes.