Understanding SQL Server 2000 Locking

Introduction
In this article, I want to tell you about SQL Server 2000 lock modes. SQL Server 2000 supports the following lock modes:

Shared (S)
Update (U)
Exclusive (X)
Intent
intent shared (IS)
intent exclusive (IX)
shared with intent exclusive (SIX)
intent update (IU)
update intent exclusive (UIX)
shared intent update (SIU)
Schema
schema modification (Sch-M)
schema stability (Sch-S)
Bulk Update (BU)
Key-Range
Shared Key-Range and Shared Resource lock (RangeS_S)
Shared Key-Range and Update Resource lock (RangeS_U)
Insert Key-Range and Null Resource lock (RangeI_N)
Exclusive Key-Range and Exclusive Resource lock (RangeX_X)
Conversion Locks (RangeI_S, RangeI_U, RangeI_X, RangeX_S, RangeX_U)
Shared locks
Shared (S) locks are used for operations that read data, such as a SELECT statement. During Shared (S) locks use, concurrent transactions can read (SELECT) a resource, but cannot modify the data while Shared (S) locks exist on the resource. If you do not use the HOLDLOCK locking hint and your transaction isolation level is not set to REPEATABLE READ or SERIALIZABLE, the Shared (S) locks on a resource are released as soon as the data has been read. If you use the HOLDLOCK locking hint or your transaction isolation level is set to REPEATABLE READ or SERIALIZABLE, the Shared (S) locks on a resource will be held until the end of the transaction.

By the way, when you select a database in the Enterprise Manager and then click Tables, the Shared (S) lock will be placed on this database, but you can insert/delete/update rows in the tables in this database.

Update locks
Update (U) locks are used when SQL Server intends to modify a row or page, and later promotes the update page lock to an exclusive lock before actually making the changes. The Update (U) locks are used to prevent a deadlock. For example, if two transactions intend to update the same row, each of these transactions will set the shared lock on this resource and then try to set the exclusive lock. Without Update (U) locks, each transaction will wait for the other transaction to release its shared-mode lock, and a deadlock will occur.

To prevent a potential deadlock, the first transaction that tries to update the row will set the Update (U) lock on this row. Because only one transaction can obtain an Update (U) lock to a resource at a time, the second transaction will wait until the first transaction converts the update lock to an exclusive lock and releases the locked resource.

Exclusive locks
Exclusive (X) locks are used for data modification operations, such as UPDATE, INSERT, or DELETE.

Other transactions cannot read or modify data locked with an Exclusive (X) lock. If a Shared (S) exists, other transactions cannot acquire an Exclusive (X) lock.

Intent locks
Intent locks are used when SQL Server wants to acquire a shared lock or exclusive lock on some of the resources lower down in the hierarchy.

Intent locks include:
intent shared (IS)
intent exclusive (IX)
shared with intent exclusive (SIX)
intent update (IU)
update intent exclusive (UIX)
shared intent update (SIU)
Intent shared (IS) locks are used to indicate the intention of a transaction to read some resources lower in the hierarchy by placing Shared (S) locks on those individual resources.

Intent exclusive (IX) locks are used to indicate the intention of a transaction to modify some resources lower in the hierarchy by placing Exclusive (X) locks on those individual resources.

Shared with intent exclusive (SIX) locks are used to indicate the intention of the transaction to read all of the resources lower in the hierarchy and modify some resources lower in the hierarchy by placing Intent exclusive (IX) locks on those individual resources.

Intent update (IU) locks are used to indicate the intention to place Update (U) locks on some subordinate resource in the lock hierarchy.

Update intent exclusive (UIX) locks are used to indicate an Update (U) lock hold on a resource with the intent of acquiring Exclusive (X) locks on subordinate resources in the lock hierarchy.

Shared intent update (SIU) locks are used to indicate shared access to a resource with the intent of acquiring Update (U) locks on subordinate resources in the lock hierarchy.

Schema locks
Schema locks are used when an operation dependent on the schema of a table is executing.

Schema locks include:
schema modification (Sch-M)
schema stability (Sch-S)
Schema modification (Sch-M) locks are used when a table data definition language (DDL) operation is being performed.

Schema stability (Sch-S) locks are used when compiling queries. This lock does not block any transactional locks, but when the Schema stability (Sch-S) lock is used, the DDL operations cannot be performed on the table.

Bulk Update locks
Bulk Update (BU) locks are used during bulk copying of data into a table when one of the following conditions exist:

TABLOCK hint is specified
table lock on bulk load table option is set using sp_tableoption
The bulk update table-level lock allows processes to bulk copy data concurrently into the same table while preventing other processes that are not bulk copying data from accessing the table.

Key-Range locks
Key-Range locks are used by SQL Server to prevent phantom insertions or deletions into a set of records accessed by a transaction. Key-Range locks are used on behalf of transactions operating at the serializable isolation level.

Shared Key-Range and Shared Resource (RangeS_S) locks are used to indicate a serializable range scan.

Shared Key-Range and Update Resource (RangeS_U) locks are used to indicate a serializable update scan.

Insert Key-Range and Null Resource (RangeI_N) locks are used to test ranges before inserting a new key into an index.

Exclusive Key-Range and Exclusive Resource (RangeX_X) locks are used when updating a key in a range.

There are also Key-Range conversion locks. Key-Range conversion locks include:

RangeI_S
RangeI_U
RangeI_X
RangeX_S
RangeX_U
Key-Range conversion locks are created when a Key-Range lock overlaps another lock.

RangeI_S locks are used when RangeI_N lock overlap Shared (S) lock.
RangeI_U locks are used when RangeI_N lock overlap Update (U) lock.
RangeI_X locks are used when RangeI_N lock overlap Exclusive (X) lock.
RangeX_S locks are used when RangeI_N lock overlap RangeS_S lock.
RangeX_U locks are used when RangeI_N lock overlap RangeS_U lock.

Key-Range conversion locks are rarely used and can be observed for a short period of time under complex circumstances.

Lock Modes Compatibility
Because IU, UIX and SIU are undocumented Intent locks and Key-Range conversion locks are rarely used and can be observed for a short period of time under complex circumstances, the Lock Modes Compatibility table does not contain these lock modes.