Understanding SQL Server 2000 Locking

最新推荐文章于 2020-07-27 04:48:29 发布

envykok

最新推荐文章于 2020-07-27 04:48:29 发布

阅读量261

点赞数

分类专栏： SQL BLOCK &amp; LOCK 文章标签： sql server locking transactions resources hierarchy table

SQL BLOCK & LOCK 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

http://www.databasejournal.com/features/mssql/article.php/3289661/Understanding-SQL-Server-2000-Locking.htm

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.

notes:

The SQL server MSDN page has a reasonable explanation:

An intent lock indicates that SQL Server wants to acquire a shared (S) lock or exclusive (X) lock on some of the resources lower down in the hierarchy. For example, a shared intent lock placed at the table level means that a transaction intends on placing shared (S) locks on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because SQL Server examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

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.