SQL Server locks explained

10
votes

Below is a list of locks that SQL Server 2000 is meant to support. I am a bit confused as to what the "intent" locks actually mean. I've looked around on the Web and the answers seem to be a bit cryptic.

Further to getting an answer to my specific question, I am hoping to use this question as a Wiki for what each lock means and under what circumstances that type of lock will be acquired.

  • 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)
3
sql-serverlocking
Shouldn't this type of question be community owned? I mean, because it looks like a lot of work to answer. But it is just a suggestion. - Sergio Acosta
agree with Sergio, though if enough rep-heeavy member edit it it will be come a community post. more folks will be able to contribute if the OP makes it community-based. - cori

3 Answers

12
votes

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.

6
votes

The intent locks are placed on the table level and indicate that a transaction will place appropriate locks on some of the rows in the table.

This speeds up conflict checking for transactions that need to place locks on the table level. For example a transaction needing an exclusive lock on a table can detect the conflict at the table level (the "intent shared" lock will be there), instead of having to examine all of the rows (or pages) for shared locks.

1
votes

Another important feature of the Intent locks is you don't place them from the code explicitly, they are requested implicitly when you place a non-intent lock.