Printed 2017/08/23 09:28PM

SQL Server Locking Overview

By Basit Aalishan Masood-Al-Farooq, 2012/10/21

Locking is a necessary part of transaction processing when working in a multi-user Online Transaction Processing (OLTP) environment. You use locks to prevent update conflicts. While one user is updating data, locks prevent other users from accessing the data being updated. Locks help to prevent:

You can serialize transactions with locks, meaning that only one person can change a data element, such as a specific row, at one time. SQL Server can issue a lock for:

SQL Server selects a locking level appropriate to the current data manipulation or definition action. For example, SQL Server uses a row lock when updating a single data row in a table. SQL Server uses dynamic lock management, which means that the locking level can be adjusted automatically as needed. You can use the sys.dm_tran_locks dynamic management view to obtain information about active locks.

Basic locks

SQL Server supports the following types of locks. Shared and exclusive locks are the basic locks supported by SQL Server.

Optimistic and pessimistic locking

Two terms are commonly used to describe locking methods:

Row versioning

Microsoft introduced row versioning as an alternative to shared locks in SQL Server 2005. With row versioning, rows are read into tempdb at the beginning of a transaction and the transaction uses that copy of those rows throughout the duration of the transaction. Row versioning protects the transaction from:

Even when row versioning is used, SQL Server still takes an exclusive lock on a row before updating it.

Row versioning allows for optimum concurrency (multiple users accessing data at the same time), while providing good protection. However, you must ensure that tempdb has sufficient disk space available. Using row versioning might also degrade performance because of the resources required to move data in and out of tempdb.

For more information, see Lock Modes.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.