Locking

Locking is a normal and necessary part of a relation database system, ensuring the integrity of the data by not allowing concurrent updates to the same data. Locking can also prevent users from reading data while it is being updated. SQL Server 2005 manages locking dynamically; however it is still important to understand how Transact-SQL queries impact locking in SQL Server. Before proceeding to the recipe, I’ll describe SQL Server 2005 locking fundamentals briefly. Locks help prevent concurrency problems from occurring. Concurrency problems (discussed in detail in the next section on Transaction, Locking, and Concurrency) can occur when one user attempts to read data that another is modifying, modify data that another is reading, or modify data that another transaction is trying to modify. Locks are placed against SQL Server resources. How a resource is locked is called its lock mode.

SQL Server 2005 Lock Modes

Name	Description
Shared lock	Shared locks are issued during read, non-modifying queries. They allow data to be read, but not updated by other processes while being held.
Intent lock	Intent locks effectively create a lock queue, designating the order of connections and their associated right to update or read resources. SQL Server uses intent locks to show future intention of acquiring locks on a specific resource.
Update lock	Update locks are acquired prior to modifying the data. When the row is modified, this lock is escalated to an exclusive lock. If not modified, it is downgraded to a shared lock. This lock type prevents deadlocks if two connections hold a Shared (S) lock on a resource, and attempt to convert to an Exclusive (X) lock, but cannot because they are each waiting for the other transaction to release the Shared (S) lock.
Exclusive lock	Issues a lock on the resource that bars any kind of access (reads or writes). Issued during INSERT, UPDATE, or DELETE statements.
Schema modification	Issued when a DDL statement is executed.
Schema stability	Issued when a query is being compiled. Keeps DDL operations from being performed on the table.
Bulk update	This type of lock is issued during a bulk-copy operation. Performance is increased for the bulk copy operation, but table concurrency is reduced.
Key-range	Key-range locks protect a range of rows (based on the index key). For example,  protecting rows in an UPDATE statement with a range of dates from '1/1/2005' to '12/31/2005'. Protecting the range of data prevents row inserts into the date range that would be missed by the current data modification.

You can lock all manner of objects in SQL Server, from a single row in a database, to a table, to the database itself. Lockable resources vary in granularity, from small (at the row level) to large (the entire database). Small grain locks allow for greater database concurrency, because users can execute queries against specified unlocked rows. Each lock placed by SQL Server requires memory, however, so thousands of individual row locks can also affect SQL Server performance. Larger grained locks reduce concurrency, but take up fewer resources. Table 3-4 details the resources SQL Server 2005 can apply locks to:


SQL Server 2005 Lock Resources

Resource Name	Description
RID	Row Identifier, designating a single table row.
Key	Index row lock, helping prevent phantom reads. Also called Key-range lock, this lock type uses both a range and a row component. The range represents the range of index keys between two consecutive index keys. The row component represents the lock type on the index entry.
Page	Referring to an 8KB data or index page.
Extent	Allocation unit of eight 8KB data or index pages.
HOPT	A heap (table without a clustered index) or B-tree.
Allocation unit	A set of related pages grouped by data type, for example data rows, index rows, and large object data rows.
Table	Entire table, data, and indexes locked.
Object	A database object (for example a view, stored procedure, function).
File	The database file.
DB	Entire database lock.
Application	An application-specified resource.
Metadata	System metadata.

Not all lock types are compatible with each other. For example, no other locks can be placed on a resource that has already been locked by an Exclusive lock. The other transaction must wait or time out until the exclusive lock is released. A resource locked by an Update lock can only have a Shared lock placed on it by another transaction. A resource locked by a Shared lock can have other Shared or Update locks placed on it.
Locks are allocated and escalated automatically by SQL Server. Escalation means that finer grain locks (row or page locks) are converted into coarse-grain table locks. SQL Server will attempt to initialize escalation when a single Transact-SQL statement has more than 5,000 locks on a single table or index, or if the amount of locks on the SQL Server instance exceeds the available memory threshold. Locks take up system memory, so converting many locks into one larger lock can free up memory resources. The drawback to freeing up the memory resources, however, is reduced concurrency.