MSSQLCity.Com - All about MS SQL
     
About Us  
SSWUG Articles  
Articles  
Administering  
Comparison  
General  
Know How  
Replication  
Tuning  
Undocumented  
UDF  
SQL 6.5  
FAQ  
Scripts  
Tips  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 

Understanding SQL Server 2000 Locking

Alexander Chigrik
chigrik@mssqlcity.com


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 used, 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 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 set the shared lock on this resource and after that tried 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 tried 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 convert the update lock to exclusive lock and release 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. During the 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 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 Exclisive (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.

      S U X IS IX SIX Sch-M Sch-S BU Range S_S Range S_U Range I_N Range X_X
    S Yes Yes No Yes No No No Yes No Yes Yes Yes No
    U Yes No No Yes No No No Yes No Yes No Yes No
    X No No No No No No No Yes No No No Yes No
    IS Yes Yes No Yes Yes Yes No Yes No Yes Yes Yes No
    IX No No No Yes Yes No No Yes No No No Yes No
    SIX No No No Yes No No No Yes No No No Yes No
    Sch-M No No No No No No No No No No No No No
    Sch-S Yes Yes Yes Yes Yes Yes No Yes Yes Yes Yes Yes Yes
    BU No No No No No No No Yes Yes No No No No
    RangeS_S Yes Yes No Yes No No No Yes No Yes Yes No No
    RangeS_U Yes No No Yes No No No Yes No Yes No No No
    RangeI_N Yes Yes Yes Yes Yes Yes No Yes No No No Yes No
    RangeX_X No No No No No No No Yes No No No No No


     

     
    Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

    (c) 1997, 2010 Bits on the Wire, Inc