SQL Server Locks – Back to Basics

loch_nessPrelude in SQL Minor

Back in late December of 2015, a challenge of sorts was issued by Tim Ford (twitter) to write a blog post each month on a SQL Server Basic. Some have hash-tagged this as #backtobasics. Here is the link to that challenge sent via tweet.

I did not officially accept the challenge. Was an official acceptance required? I don’t know. I do know that I think it is a good challenge and that I intend to participate in the challenge. I hope I can meet the requirements and keep the posts to “basics”. Let’s just call this first post in the challenge to be my official acceptance.

SQL Server Locks

A fundamental component of SQL Server is locking and locks. Locks within SQL Server are critical to the proper functioning of the database and the integrity of the data within the database. The presence of locks does not inherently mean there is a problem. In no way should locking within SQL Server be considered a monster, though locks may often times be misconstrued in that light.

So what is locking? What are locks? In SQL Server, locking is a pessimistic mechanism that limits, or synchronizes, access to resources between multiple different requests. A lock is the manifestation of that mechanism. Think of a lock within SQL Server as if it were a padlock that was temporarily securing a gate to a storage shed limiting access to the shed.

That is the overly simplistic illustration of a lock in SQL Server. Have you ever seen a gate with more than one lock on it? Well, just like that multi-lock gate, resources within SQL Server can have several different locks placed on them. This is where it starts to get complicated and tricky.

To help control the locks and direct access to resources within the database, SQL Server utilizes a service called the lock manager. When a query is executed, the Database Engine (more precisely the query processor) determines what resources are needed and what locks are needed for those resources. Then the query processor walks over to the lock manager and begs for the lock types needed. The lock manager takes a look at the locks in use on the resources and either grants the locks or tells the query processor to wait until the locks are available.

This process is not terribly different from a tenant requesting the use of a meeting room from the building manager (or property manager). If the meeting room is available, the building manager will grant access to the meeting room and place a hold on that room preventing other tenants from using it at the time you requested it. Well, not terribly different but on a much much larger scale.

Just as you may see in a building with multiple meeting spaces, offices, rooms, and equipment as resources, there are many types of resources within SQL Server. The lock manager has to coordinate different types of locks for the varying types of resources. Let’s take a look at some of the lock types and resource types.

Lock Types

First up is to explore the different types of locks in SQL Server. One can either explore the internet or query SQL Server direct to try and find what is available. I will show two ways to find the different types of locks available within SQL Server via the following queries:

Looking at the results from the first query, I will get results similar to the following:

name map_value
lock_mode NL
lock_mode SCH_S
lock_mode SCH_M
lock_mode S
lock_mode U
lock_mode X
lock_mode IS
lock_mode IU
lock_mode IX
lock_mode SIU
lock_mode SIX
lock_mode UIX
lock_mode BU
lock_mode RS_S
lock_mode RS_U
lock_mode RI_NL
lock_mode RI_S
lock_mode RI_U
lock_mode RI_X
lock_mode RX_S
lock_mode RX_U
lock_mode LAST_MODE

The last lock type in this list is kind of an interesting one. I believe this maps to the RangeX-X lock type seen in the documentation and in the second of the two queries provided. What do these letters mean? It looks like a bunch of alphabet soup right? Well, there is a little more info to be found once turning to the documentation (check the image by the heading of “key”). Here’s a little table to decrypt it as well:

name map_value Decrypted
lock_mode NL No Lock
lock_mode SCH_S Schema Stability Locks
lock_mode SCH_M Schema Modification Locks
lock_mode S Shared
lock_mode U Update
lock_mode X Exclusive
lock_mode IS Intent Shared
lock_mode IU Intent Update
lock_mode IX Intent Exclusive
lock_mode SIU Shared with Intent Update
lock_mode SIX Shared with Intent Exclusive
lock_mode UIX Update with Intent Exclusive
lock_mode BU Bulk Update
lock_mode RS_S Shared Range-Shared
lock_mode RS_U Shared Range-Update
lock_mode RI_NL Insert Range-Null
lock_mode RI_S Insert Range-Shared
lock_mode RI_U Insert Range-Update
lock_mode RI_X Insert Range-Exclusive
lock_mode RX_S Exclusive Range-Shared
lock_mode RX_U Exclusive Range-Update
lock_mode LAST_MODE Exclusive Range-Exclusive

I want to just cover some quick highlights about a few lock types:

Lock mode Description
Shared (S) Used for Read operations that do not change data.
Update (U) Used on resources that can be updated. Helps to reduce some deadlocks.
Exclusive (X) Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
Intent Used to establish imminent need for a lock.
Schema Used when a schema dependent operation is executing.
Bulk Update (BU) Used when bulk copying data into a table and the TABLOCK hint is specified.
Key-range Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Lock Resources

Lock resources are the things upon which a lock is held. This is the lock granularity and hierarchy. It is this hierarchy that helps to reduce the overall cost of locking in SQL Server (again banishing the notion of it being a monster). The hierarchy of locks can be explored via queries in SQL Server (and it can be found – to a degree – in the documentation). Here are a couple of queries to help explore the locks hierarchy:

The results of the first of these queries are:

name map_value
lock_resource_type UNKNOWN_LOCK_RESOURCE
lock_resource_type NULL_RESOURCE
lock_resource_type DATABASE
lock_resource_type FILE
lock_resource_type UNUSED1
lock_resource_type OBJECT
lock_resource_type PAGE
lock_resource_type KEY
lock_resource_type EXTENT
lock_resource_type RID
lock_resource_type APPLICATION
lock_resource_type METADATA
lock_resource_type HOBT
lock_resource_type ALLOCATION_UNIT
lock_resource_type OIB
lock_resource_type ROWGROUP
lock_resource_type LAST_RESOURCE

There are a few differences between this list and what is found in the documentation. There are also a few differences between the results of the first and second queries. This should give a pretty good idea about the level at which locks could be held from a key all the way up to database.

I have shared these because the lock resources may come in handy when troubleshooting or even designing queries and database schemas.

Lock Owners

The last bit about locks for this time is the lock owner. Another way of thinking about this one is what is happening while the lock is being held. Is the query a part of a transaction that is holding the lock? Maybe the query is a cursor and the cursor is holding locks for the queries within the construct of the cursor. The following query that shows what the possible owners are can be a little helpful in understanding what an owner is.

name map_value
lock_owner_type Transaction
lock_owner_type Cursor
lock_owner_type Session
lock_owner_type SharedXactWorkspace
lock_owner_type ExclusiveXactWorkspace
lock_owner_type LockConflictNotificationObject
lock_owner_type LockTableIterator
lock_owner_type LastLockInfoOwner

This is another one of those items to explore which could help understand the nature of the locks if you happen to be troubleshooting or investigating a certain locking issue within your environment.

Recap

 

Locking is a fundamental concept in SQL Server. Locking is the mechanism that coordinates access to resources while maintaining data integrity. Locks are the tool used in locking to ensure access is only granted to the correct requestor and preventing access to the resource to outside sessions.

This was a basic introductory article on the topic. Stay tuned for a more in-depth look at a specific scenario involving locking and deadlocking.

page 1 of 1








Calendar
August 2017
M T W T F S S
« Jun    
 123456
78910111213
14151617181920
21222324252627
28293031  
Content
SQLHelp

SQLHelp


Welcome , today is Thursday, August 24, 2017