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.