Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Dec 24, 2003

Understanding SQL Server 2000 Locking

By Alexander Chigrik

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.

  S U X IS IX SIX Sch-M Sch-S BU RangeS_S RangeS_U RangeI_N RangeX_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

» See All Articles by Columnist Alexander Chigrik



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date