Locking should be simple, right? You need to access a row or table and you get a lock. Oh yeah, blocking is bad.
It’s not that simple, and I find this is an area that many people do not understand that well. Joe does a nice job of presenting this from a beginning level and going into more details on locking using lots of demos.
Blocking is the result of contention, but that is good. It helps enforce the consistency between resources. Joe shows how SQL Server tries to balance the concurrency and consistency requirements, showing escalation and how to query the sys.dm_tran_locks table and understand what is being locked and why.
When does SQL Server escalate? The default is 5000 locks but it is not that simple. There are other factors that come into play.
This was an area I studied a lot for the MCM and I learned a lot about the details of locking behavior. Joe does a nice job of simplifying locking for the DBA or developer looking to better understand why locks and blocks occur on your server.
Has anyone modified the parallelism lock threshold? No one in the audience and I would be interested in hearing if anyone reading this has.
Controlling locking is a bit of an art. There are hints you can use, like NOLOCK or READPAST. These can help, but this should not be a standard in all your queues. It’s like duct tape. It can “appear” to fix a problem but it’s really just alleviating some pain. A tool but not necessarily the first one you want to use.
Keep in mind that your hints are “requests”, not mandates. SQL Server many not honor them.
By default, SQL Server will wait indefinitely for a lock to clear. Deadlocks are an exception, where one process will have it’s locks clears when it is killed. You can set a lock timeout but if you do this, the application need to handle the 1222 error and then retry.
This is a good session for you to attend if you are new to locking and want to learn more about how this works. As with many things in SQL Served, there are no hard and fast rules to apply but you will learn some tricks to try and where to look to get more information.
There are also isolation level settings you can change. These are a little more drastic and you really need to understand the impact of them, especially snapshot isolation.