Locks Unlocked

I’m likely not the first person clever enough to use this title, but as requested, this post will delve into Locks and try and explain them in a simple fashion.

Simply put, you Lock data in a database to keep multiple operations from occurring on the same data at the same time – for example, if two Update operations are trying to modify the same record in the database, there will be an issue and the data will likely be incorrect when they are done.  So, each operation will lock the resource (record, page table, etc.), and release the lock when it is finished with the data.  Too many locks on a database will hinder performance, so it is important to only use the type of Lock that is necessary for the operation.  The big trade-off here is performance vs. data integrity – too few locks, and your data may become corrupt, while too many will bog down your servers.  You will have to determine what is best for your environment based on a number of factors, including number of concurrent users/applications, hardware resources, necessity of data integrity, etc.

In SQL Server, there are seven types of Locks modes available.  I will go over each one briefly, but you should also refer to the Books Online (BOL) documentation for a full discussion of these Lock types if necessary.  Here are the 7 types of Lock modes available in SQL Server:

Shared:   This type of lock is used in Read operations (typically a SELECT statement), the resource only needs to be locked to make sure the data is not modified during the Read.  As the data will not be modified by a simple Read operation, nothing further needs to be done.

Update:   This lock type is used when a resource needs to be updated – the lock needs to be put on the data before the transaction starts to ensure that another operation is not reading data that is going to be modified before the operation is complete.  This Lock will prevent deadlocks from Shared locks simultaneously trying to promote to an Exclusive lock on the same piece of data.

Exclusive:   This Lock is used when any of the data modification commands are used, such as UPDATE or DELETE.  No other lock is allowed on the data while an Exclusive lock is in place.

Intent: These locks are basically “pre-locks” set at table level to signal that another type of Lock will be placed on a resource in that Table.  There are three types of these,

Schema: These are used by the database engine to make sure no data is updated in a table while adding a column or doing some other operation involving the database structure itself.

Bulk Update: Used for doing bulk updates – this will lock the entire table so that multiple threads can execute the bulk update concurrently and not be interrupted or cause corruption with other operations.

Key-range: This type of Lock is placed on an Index, effectively locking off a range of data from Reads and other operations.

I hope this has given you a basic idea of the concept of locking, why it is done, and what the limitations and performance implications are.

Comments

• Anonymous
  September 07, 2011
  Excellent blog!!

• Anonymous
  June 29, 2012
  The comment has been removed