SQL Server锁说明 [英] SQL Server locks explained

问题描述

下面是SQL Server 2000本应支持的锁的列表.我对意图"锁的实际含义有些困惑.我在网上四处张望，答案似乎有些晦涩难懂.

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.

在得到我的特定问题的答案之前，我希望将此问题用作Wiki，以了解每个锁的含义以及在何种情况下将获得该类型的锁.

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.

• 共享(S)
  • 更新(U)
  • 独家(X)
  • 意图
    • 意图共享(IS)
    • 意图排他(IX)
    • 与意图排他性(SIX)共享
    • 意图更新(IU)
    • 更新意图专用(UIX)
    • 共享意图更新(SIU)
    • 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)
        • 模式修改(Sch-M)
        • 模式稳定性(Sch-S)
        • 共享密钥范围和共享资源锁(RangeS_S)
        • 共享密钥范围和更新资源锁(RangeS_U)
        • 插入键范围和空资源锁(RangeI_N)
        • 排他键范围和排他资源锁(RangeX_X)
        • 转换锁(RangeI_S，RangeI_U，RangeI_X，RangeX_S，RangeX_U)

        推荐答案

        意图锁表示SQL Server希望在层次结构中较低的某些资源上获得共享(S)锁或排他(X)锁.例如，在表级别放置共享意图锁意味着事务打算在该表内的页面或行上放置共享(S)锁.在表级别设置意图锁可防止另一个事务随后在包含该页面的表上获取排他(X)锁.意图锁可提高性能，因为SQL Server仅在表级别检查意图锁，以确定事务是否可以安全地获取该表上的锁.这样就无需检查表上的每一行或页锁定，以确定事务是否可以锁定整个表.

        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.

        这篇关于SQL Server锁说明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！