SQL Server locks explained

Question

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)

Answer 1

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.

Answer 2

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.

Answer 3

To get more information about Isolation levels in SQL Server including Locks and its impact on database in detail with examples, refer the below link: http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/