During transactions it is important to understand the effects that the isolation level will have and the effects of this setting on concurrency. This is important because it will help to define the strategy which you might incorporate into your solution through locking hints. If the system is not designed properly, you will potentially see high memory consumption as the process of setting up locks and then dealing with escalating locks and blocking impedes the database (See Memory Manager Object).
For all of the skills under the Designing a Transaction and Concurrency sub-heading I would recommend Microsoft SQL Server 2008 Internals (Delaney et al) Chapter 10 – Transactions and Concurrency . Of course, I would recommend just buying and reading this book in general if you would like to know more about the database engine.
When attempting to understand how we might use locking hints, and how the locking granularity will potentially affect memory consumption, we first have to understand locking in the database engine including the locking modes and lock compatibilities between these modes. SQL Server has multi-granular locking which allows different types of resources to be locked by one or more transactions depending on these parameters (assuming that the locks are compatible). The lock manager will attempt to lock the resources at the appropriate level to avoid high overhead where it is not necessary. In order to accomplish this feat the lock manager will often have to place multiple locks of different types on the different levels of the locking hierarchy. However, there are times when the locking mechanism chosen will become too costly and lock escalation will occur. It should be noted that locking is greatly affected by the transaction isolation level of the session which we will talk about in more detail in an upcoming post.
Example of Locks
When an update is to be performed an exclusive lock must be received on the resource to be updated. In addition, the resources higher in the hierarchy must also be locked with an “intent” version of the lock. So, for a single record to be updated, the record itself (or key) will receive an exclusive lock and the page and table will receive intent exclusive locks.
System Locking Information
The dmv sys.dm_tran_locks is one of the better resources for obtaining information on the current status of locks within a a system. In the past, the stored procedure sp_lock was one of the main methods of tracking locks.
New with the advent of partitioned tables is the ability to set lock escalation on a table to escalate to a partition instead of to the entire table. Note that the lock will never escalate from a partition to a table with this setting enable.
Granularity Locking Hints
Granularity locking hints are a way to attempt to grant a finer level of control over the types of locks which are acquired through the lock manager. These hints are options which are passed as part of the SELECT, INSERT, UPDATE or DELETE statements using the WITH () syntax after the table name. Other locking hints can also be used to override the transaction isolation level, which we will discuss in a future post.
It should be noted that even though these hints are available, the query optimizer will generally choose the best types of locks for a given statement. As such, only use these hints when necessary.
PAGLOCK forces the lock manager to take locks of the appropriate mode at the page level instead of the row, key or table locks which might normally be acquired. This hint is ignored for SNAPSHOT isolation unless if other hints are declared along with it.
ROWLOCK forces the lock manager to take locks of the appropriate mode at the row level instead of the page or table locks which might normally be acquired. This hint is ignored for SNAPSHOT isolation unless if other hints are declared along with it.
TABLOCK forces the lock manager to take locks of the appropriate mode at the table level instead of the row, key or page locks which might normally be acquired. This hint should be considered for potential utilization with inserting into heaps and with bulk inserts using the OPENROWSET provider. See the BOL page for more information.
TABLOCK forces the lock manager to take an exclusive lock of the table instead of the appropriate type of lock on the row, key or page which might normally be acquired.
UPDLOCK forces the lock manager to take an update lock. This lock will be held until the transaction completes.
XLOCK forces the lock manager to take exclusive locks. These locks will be held until the transaction completes. This lock type can be specified with ROWLOCK, PAGLOCK or TABLOCK to exclusively lock at the given level.
READPAST tells the lock manager to skip past any rows or pages that are currently locked. This hint is primarily used for queuing systems and has many rules around when it is valid that should be considered before attempting to use it. Note that this is not technically a “granularity” hint.
NOWAIT returns a message as soon as a lock is encountered. Equivalent to specifying SET LOCK_TIMEOUT 0. Note that this is not technically a “granularity” hint.
Locks are memory structures controlled by the lock manager and are a part of the overall memory space allocated to SQL Server. As such, it is important to think of the locking strategy when you are designing your system as the amount of memory consumed by the locking system will impact the memory usage of several other areas of SQL Server.
Locks blocks are defined by the database, the type of lock and the lock resource (i.e. row, index key, page or table) to be affected by the lock. Each lock block costs 64 bytes of memory on a 32 bit system or 128 bytes of memory on a 64 bit system. Each lock block contains a pointer to the lock owner blocks though a list based on the current status of the lock owner block.
The total number of lock blocks available can be set by using sp_configure. Otherwise, the number is dynamic as lock blocks are created and destroyed by the lock manager.
Lock owners define the relationship between the lock and the holder of the lock as well as the status of the lock (either granted, waiting or waiting to convert). These lock owner blocks cost 32 bytes of memory on a 32 bit system and 64 bytes of memory on a 64 bit system.
Lock Hash Table
The lock manager uses a lock hash table which contains a hashed reference to the lock resources contained within a lock block. Lock blocks containing lock resources which have hashed to the same value are chained together. Each entry in the hash table contains a pointer to a list of lock blocks and a spinlock.
Locking escalation is a construct used to help mitigate the memory cost of the locking system. A lock will escalate when a single statement acquires 5,000 locks on a table (or partition) or index. A lock will also escalate when the number of locks in an instance of the database engine exceeds memory or configuration thresholds. Performing this escalation allows the lock manager to drop the lock blocks for all of the lower level resources, reducing the pressure for memory on the locking system. However, when these locks escalate, it reduces concurrency on the object. If a lock is not able to escalate because of other locks present in the system, it will attempt to escalate again for every 1250 new locks.
It is important to note that locks do not escalate from row -> page -> table (or partition). Instead, if a lock is set to escalate, it will escalate to table (or partition).