July 21, 2016 at 7:36 am
The DBA team inherited a database that was heavily modified by the developers.
There exists a 7MB/45k-row queue-type table with a clustered index. It consists of mostly small varchar/nvarchars, and datetimes. No blobs. Max data size is around 223 bytes, not counting internal overhead. It has: Lock escalation disabled, and ALLOW_PAGE_LOCKS=off, ALLOW_ROW_LOCKS=on, for both the sole NC index, and the cluster.
Supposedly, and we have observed, one row in this table gets updated at the start of a business process, and is committed. Work is done elsewhere, and at the end of the BP, the table is updated again, to show that the queue item is complete.
The issue is, a few times a week the table gets locked throughout the "work", and no other threads can update the queue table.
Are there any gotchas that I'm missing here? Can a COMMIT get delayed? Even if they aren't committing the queue update, why is the entire table getting locked, with lock escalation disabled?
Thanks!
July 21, 2016 at 8:05 am
I don't really have an answer for you but BOL says
DISABLE
Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, Database Engine must take a table lock to protect data integrity.
..which implies there are other circumstances where SQL will take table locks.
Can you investigate what's happening in your application when the locks escalate?
July 21, 2016 at 8:09 am
Before asking how the entire table gets locked, check and confirm what the exact locks involved are. Next time it happens, look in sys.dm_tran_locks and see what locks exist for that table.
And yes, table locks are still allowed with those settings, SQL won't escalate from row to table but there's nothing stopping it starting with a table lock.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2016 at 8:33 am
Thank you, guys....Very helpful. I'll post back after observations....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply