Introduction to Locking in SQL Server

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

Lock modes

All examples is run under the default READ commited isolation level. Taken locks differ between isolation levels, however these examples is just to demonstrate the lock mode with an example. Here's a little explanation of the three columns from sys.dm_tran_locks used in the examples:

resource_type	This tells us what resource in the database the Locks is being taken on. It can be one of these values:database, FILE, OBJECT, PAGE, KEY, EXTENT, RID, Application, METADATA, HOBT, Allocation_uni T.
request_mode	This tells us the mode of our lock.
resource_description	This shows a brief description of the resource. Usually holds the-ID of the page, object, file, row, etc. It isn ' t populated for every type of lock

The filter on Resource_type <> ' DATABASE ' just means that we don ' t want to see general gkfx locks taken on Databas Es. These is always present. All shown outputs is from the sys.dm_tran_locks dynamic management view. In some examples it's truncated to display only locks relevant for the example. For full output, you can run these yourself.

Shared Locks (S)

Shared locks is held on data being read under the pessimistic concurrency model. While a GKFX lock is being held other transactions can read but can ' t modify locked data. After the locked data have been read the shared lock is released, unless the transaction was 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 is Alrea Dy released when we would select data from Sys.dm_tran_locks. That's why a addition of with (HOLDLOCK) was needed to see the locks.

BEGIN tranuse Adventureworksselect * from Person.Address with (HOLDLOCK) WHERE addressid = 2SELECT Resource_type, request_m Ode, Resource_descriptionfrom   sys.dm_tran_lockswhere  resource_type <> ' DATABASE ' ROLLBACK

Update Locks (U)

Update Locks is a mix of shared and exclusive locks. When a DML statement is executed SQL Server have to find the data it wants to modify first, so to avoid lock conversion DEA Dlocks An update lock is used. Only one update lock can is held on the data at one time, similar to an exclusive lock. But the difference, the update lock itself can ' t modify the underlying data. It has been converted to a exclusive lock before the modification takes place. You can also force a update lock with the UPDLOCK hint:

BEGIN tranuse Adventureworksselect * from Person.Address with (UPDLOCK) WHERE Addressid < 2SELECT resource_type, request _mode, Resource_descriptionfrom   sys.dm_tran_lockswhere  resource_type <> ' DATABASE ' ROLLBACK

Exclusive Locks (X)

Exclusive locks is used to lock data being modified by one transaction thus preventing modifications by other concurrent Transactions. You can read the 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 "ll always find Exclusive locks accompanied by s Hared locks on that same data.

BEGIN tranuse adventureworksupdate person.address SET AddressLine2 = ' Test Address 2 ' WHERE addressid = 5SELECT resource_ty PE, Request_mode, Resource_descriptionfrom   sys.dm_tran_lockswhere  resource_type <> ' DATABASE ' ROLLBACK

Intent Locks (I)

Intent locks is a means in which a transaction notifies other transaction that it's 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 Highe R in lock hierarchy. What's this means are before you obtain a lock on the page or the row level an intent lock is set on the table. This prevents and transactions from putting exclusive locks on the table, 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 was to protect th E data from being locked to other transactions.

BEGIN tranuse adventureworksupdate TOP (5) person.address SET AddressLine2 = ' Test Address 2 ' WHERE PostalCode = ' 98011 ' Sele CT Resource_type, Request_mode, Resource_descriptionfrom   sys.dm_tran_lockswhere  resource_type <> ' DATABASE ' ROLLBACK

Schema Locks (Sch)

There is 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 it structure is being changed.

In the example we can see the sch-s and sch-m locks being taken on the system tables and the TestTable plus a lot of other Locks on the system tables.

BEGIN tranuse adventureworkscreate TABLE testtable (testcolumn INT) SELECT resource_type, Request_mode, Resource_ Descriptionfrom   sys.dm_tran_lockswhere  resource_type <> ' DATABASE ' ROLLBACK

Bulk Update Locks (BU)

Bulk Update locks was used by Bulk operations when TABLOCK hint was used by the import. This allows is multiple fast concurrent inserts by disallowing data reading to other transactions.

Conversion Locks

Conversion locks is locks resulting from converting one type of lock to another. There is 3 types of conversion locks:

• Shared with Intent Exclusive (SIX). A transaction that holds a Shared lock also have some pages/rows locked with an Exclusive lock
• Shared with Intent Update (SIU). A transaction that holds a Shared lock also have some pages/rows locked with an Update lock.
• Update with Intent Exclusive (UIX). A transaction that holds an Update lock also have some pages/rows locked with an Exclusive lock.

In the example can see the UIX conversion lock being taken on the page:

BEGIN tranuse adventureworksupdate TOP (5) person.address SET AddressLine2 = ' Test Address 2 ' WHERE PostalCode = ' 98011 ' Sele CT Resource_type, Request_mode, Resource_descriptionfrom   sys.dm_tran_lockswhere  resource_type <> ' DATABASE ' ROLLBACK

Key-range Locks

Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQL statement while U Sing 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 is both 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

SET TRANSACTION Isolation Level SERIALIZABLE;  BEGIN tranuse adventureworksupdate person.address SET AddressLine2 = ' Test Address 2 ' WHERE AddressLine1 like ' 987% ' SELECT Resource_type, Request_mode, Resource_descriptionfrom   sys.dm_tran_lockswhere  resource_type <> ' DATABASE ' ROLLBACK

Lock Granularity

Lock granularity consists of TABLE, PAGE and ROW locks. If you had a clustered index on the table then instead of a ROW lock you had a KEY lock. Locking on the lower level increases concurrency, but if a lot of locks is 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 of the locking level to keep data in correct state. You can override the locking level by using Rowlock, paglock or TABLOCK hints, and the use of these hints is discouraged SI NCE SQL Server know what is the appropriate locks to take for each scenario. If You must use them you should is aware of the concurrency and data consistency issues you might cause.

Spinlocks

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

Lock compatibility Matrix

This was taken from http://msdn2.microsoft.com/En-US/library/ms186396.aspx. Also a good resource to has is a Lock compatibility Matrix which tells what each lock plays nice with other lock modes . It's one of those things you don't think you need up until the moment it.

Conclusion

Hopefully this article have shed some light on what SQL Server operates with locks and why are locking of such importance to Proper application and database design and operation. Remember that locking problems is of logical and not physical nature so they has to is well thought out. Locking goes hand in hand with transaction isolation levels so is familiar with those too. In the next article I'll show some ways to resolve locking problems.

From:http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

Introduction to Locking in SQL Server