Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 230

First, know that there are varying levels of granularity for locks. They range from row-level locking all
the way up to database-level locking. Also, know that SQL Server takes a dynamic approach to determine
a locking strategy, which is based on the most cost-effective (that is, least amount of resources) plan.

Granularity
Granularity refers to the resource that a lock applies to. A partial list of the resources that can be locked
includes:

Row

❑

Page

❑

Table

❑

Database

❑

LockModes
The type of lock applied to the resources listed above is known as the lock mode. SQL Server provides
several modes for locks. Following is a list of the lock modes:

Shared: These are used for read operations.

❑

Update: Used on updatable resources.

❑

❑ Exclusive: These are used for write operations (insert, update, delete).

❑ Intent: These are used to establish a lock ''hierarchy.''

❑ Schema: These are used when an operation that is dependent on a table's schema is operating.

❑ Bulk Update: These are used when bulk copying data is occurring, and the TABLOCK hint is

enabled.

Key-range: These are used to protect a range of rows read by a query that is in a serializable
transaction.

❑

LockCompatibility
Sometimes a transaction will attempt to lock a resource that is already locked. If the modes of the locks are
compatible then the new lock can be granted without interference. However, if the modes are not com-
patible then the transaction requesting the new lock will be forced to wait until the first lock is released.
Obviously exclusive locks are not compatible with any other lock modes. However, many lock modes
are compatible. For a complete listing of compatible lock modes see BOL.

LockEscalation
Whenever possible, SQL Server will convert fine-grain locks into coarse-grain locks. This process is
known as lock escalation. Note that when SQL Server escalates locks it does so by promoting both
row and page level locks to the table level. It does not promote row-level locks to page-level. When
the database engine acquires a lock on a resource it will place an intent lock on that resource's parent
object. For example, when a row lock is placed on a data row, that row's page receives an intent lock.

When successful, all lower-level locks can be released, thereby reducing overhead for the lock manager.