Introduction to Locking in SQL Server

Locking is a major part of every RDBMS and is important to know about. It is a database functionality which without a multi-user environment could not work. The main problem of locking is that in an essence it’s a logical and not physical problem. This means that no amount of hardware will help you in the end. Yes you might cut execution times but this is only a virtual fix. In a heavy multi-user environment any logical problems will appear sooner or later.

Lock modes
Shared locks (S)
Shared locks are held on data being read under the pessimistic concurrency model. While a shared lock is being held other transactions can read but can’t modify locked data. After the locked data has been read the shared lock is released, unless the transaction is being run with the locking hint (READCOMMITTED, READCOMMITTEDLOCK) or under the isolation level equal or more restrictive than Repeatable Read. In the example you can’t see the shared locks because they’re taken for the duration of the select statement and are already released when we would select data from sys.dm_tran_locks. That is why an addition of WITH (HOLDLOCK) is needed to see the locks.
Update locks (U) Update locks are a mix of shared and exclusive locks. When a DML statement is executed SQL Server has to find the data it wants to modify first, so to avoid lock conversion deadlocks an update lock is used. Only one update lock can be held on the data at one time, similar to an exclusive lock. But the difference here is that the update lock itself can’t modify the underlying data. It has to be converted to an exclusive lock before the modification takes place. You can also force an update lock with the UPDLOCK hint.
Exclusive locks (X) Exclusive locks are used to lock data being modified by one transaction thus preventing modifications by other concurrent transactions. You can read data held by exclusive lock only by specifying a NOLOCK hint or using a read uncommitted isolation level. Because DML statements first need to read the data they want to modify you’ll always find Exclusive locks accompanied by shared locks on that same data.
Intent locks (I) Intent locks are a means in which a transaction notifies other transaction that it is intending to lock the data. Thus the name. Their purpose is to assure proper data modification by preventing other transactions to acquire a lock on the object higher in lock hierarchy. What this means is that before you obtain a lock on the page or the row level an intent lock is set on the table. This prevents other transactions from putting exclusive locks on the table that would try to cancel the row/page lock. In the example we can see the intent exclusive locks being placed on the page and the table where the key is to protect the data from being locked by other transactions.
Schema locks (Sch) There are two types of schema locks:
                – Schema stability lock (Sch-S): Used while generating execution plans. These locks don’t block access to the object data.
                – Schema modification lock (Sch-M): Used while executing a DDL statement. Blocks access to the object data since its structure is being changed.
Bulk Update locks (BU) Bulk Update locks are used by bulk operations when TABLOCK hint is used by the import. This allows for multiple fast concurrent inserts by disallowing data reading to other transactions.
Conversion locks are locks resulting from converting one type of lock to another. There are 3 types of conversion locks:
            – Shared with Intent Exclusive (SIX). A transaction that holds a Shared lock also has some pages/rows locked with an Exclusive lock
            – Shared with Intent Update (SIU). A transaction that holds a Shared lock also has some pages/rows locked with an Update lock.
            – Update with Intent Exclusive (UIX). A transaction that holds an Update lock also has some pages/rows locked with an Exclusive lock.
Key – Range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while using the serializable transaction isolation level. Key-range locking prevents phantom reads. By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction. In the example we can see that there are two types of key-range locks taken:
            – RangeX-X – exclusive lock on the interval between the keys and exclusive lock on the last key in the range
            – RangeS-U – shared lock on the interval between the keys and update lock on the last key in the range

Lock Granularity
Lock granularity consists of TABLE, PAGE and ROW locks. If you have a clustered index on the table then instead of a ROW lock you have a KEY lock. Locking on the lower level increases concurrency, but if a lot of locks are taken consumes more memory and vice versa for the higher levels. So granularity simply means the level at which the SQL Server locks data. Also note that the more restricted isolation level we choose, the higher the locking level to keep data in correct state. You can override the locking level by using ROWLOCK, PAGLOCK or TABLOCK hints but the use of these hints is discouraged since SQL Server know what are the appropriate locks to take for each scenario. If you must use them you should be aware of the concurrency and data consistency issues you might cause.

Spinlocks are a light-weight lock mechanism that doesn’t lock data but it waits for a short period of time for a lock to be free if a lock already exists on the data a transaction is trying to lock. It’s a mutual exclusion mechanism to reduce context switching between threads in SQL Server.

(sursa si exemple: )


Information about currently active lock manager resources

sys.dm_tran_locks (Transact-SQL): Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.


Lock Modes:

The following query will display lock information. The value for should be replaced with the database_id from sys.databases.
SELECT FROM  sys.databases OR if you already know the Id you can use SELECT db_name(<dbid>)

SELECT resource_type,request_mode,request_lifetime,resource_associated_entity_id,
(SELECT [name] FROM sys.objects WHERE object_id=(SELECT object_id from sys.partitions
WHERE hobt_id = resource_associated_entity_id)),
(SELECT [name] FROM sys.objects WHERE object_id= resource_associated_entity_id)
FROM sys.dm_tran_locks
WHERE resource_database_id= <bdid>

The following query returns object information by using resource_associated_entity_id from the previous query. This query must be executed while you are connected to the database that contains the object.

SELECT object_name(object_id), *
FROM sys.partitions
WHERE  hobt_id= <resource_associated_entity_id>

The following query will also show blocking information.
SELECT t1.resource_type,
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address

Managed, Unmanaged, Native code

Managed Code is what Visual Basic .NET and C# compilers create. It compiles to Intermediate Language (IL), not to machine code that could run directly on your computer. The IL is kept in a file called an assembly, along with metadata that describes the classes, methods, and attributes (such as security requirements) of the code you’ve created. This assembly is the one-stop-shopping unit of deployment in the .NET world. You copy it to another server to deploy the assembly there—and often that copying is the only step required in the deployment.
Managed code runs in the Common Language Runtime. The runtime offers a wide variety of services to your running code. In the usual course of events, it first loads and verifies the assembly to make sure the IL is okay. Then, just in time, as methods are called, the runtime arranges for them to be compiled to machine code suitable for the machine the assembly is running on, and caches this machine code to be used the next time the method is called. (This is called Just In Time, or JIT compiling, or often just Jitting.)
As the assembly runs, the runtime continues to provide services such as security, memory management, threading, and the like. The application is managed by the runtime.
Visual Basic .NET and C# can produce only managed code. If you’re working with those applications, you are making managed code. Visual C++ .NET can produce managed code if you like: When you create a project, select one of the application types whose name starts with .Managed., such as .Managed C++ application..

Unmanaged code is what you use to make before Visual Studio .NET 2002 was released. Visual Basic 6, Visual C++ 6, heck, even that 15-year old C compiler you may still have kicking around on your hard drive all produced unmanaged code. It compiled directly to machine code that ran on the machine where you compiled it—and on other machines as long as they had the same chip, or nearly the same. It didn’t get services such as security or memory management from an invisible runtime; it got them from the operating system. And importantly, it got them from the operating system explicitly, by asking for them, usually by calling an API provided in the Windows SDK. More recent unmanaged applications got operating system services through COM calls.
Unlike the other Microsoft languages in Visual Studio, Visual C++ can create unmanaged applications. When you create a project and select an application type whose name starts with MFC, ATL, or Win32, you’re creating an unmanaged application.
This can lead to some confusion: When you create a .Managed C++ application., the build product is an assembly of IL with an .exe extension. When you create an MFC application, the build product is a Windows executable file of native code, also with an .exe extension. The internal layout of the two files is utterly different. You can use the Intermediate Language Disassembler, ildasm, to look inside an assembly and see the metadata and IL. Try pointing ildasm at an unmanaged exe and you’ll be told it has no valid CLR (Common Language Runtime) header and can’t be disassembled—Same extension, completely different files.

The phrase native code is used in two contexts. Many people use it as a synonym for unmanaged code: code built with an older tool, or deliberately chosen in Visual C++, that does not run in the runtime, but instead runs natively on the machine. This might be a complete application, or it might be a COM component or DLL that is being called from managed code using COM Interop or PInvoke, two powerful tools that make sure you can use your old code when you move to the new world. I prefer to say .unmanaged code. for this meaning, because it emphasizes that the code does not get the services of the runtime. For example, Code Access Security in managed code prevents code loaded from another server from performing certain destructive actions. If your application calls out to unmanaged code loaded from another server, you won’t get that protection.
The other use of the phrase native code is to describe the output of the JIT compiler, the machine code that actually runs in the runtime. It’s managed, but it’s not IL, it’s machine code. As a result, don’t just assume that native = unmanaged.


sp_configure – Displays or changes global configuration settings for the current server

Limit SQL Server memory usage: Use the sp_configure system stored procedure with the max server memory option to limit the amount of memory in the buffer pool used by an instance of SQL Server or MSDE. This will prevent SQL Server from using more than the specified amount of memory, thus leaving remaining memory available to start other applications quickly. You cannot set max server memory to a value less than 4 MB. 16 MB or more is recommended especially if you are using replication. max server memory is an advanced option. You need to enable advanced options before you can use it.

Enable advanced options:
EXEC sp_configure ‘show advanced options’, 1

Set the maximum amount of memory to 64 MB:
EXEC sp_configure ‘max server memory (MB)’, 64

Display the newly set configuration:
EXEC sp_configure ‘max server memory (MB)’

Set ‘show advanced options’ back to default:
EXEC sp_configure ‘show advanced options’, 0

The configuration options table can be found at: