SQL Server 的事务和锁（二）-Range S-S锁

素颜马尾好姑娘i 2022-05-28 12:15 98阅读 0赞

**G:锁定范围，保证不产生幻读～～～**

在这篇随笔中，我们的主要关注点在 Key-Range Lock。Key-Range Lock有 S-S、S-U、I-N、X-X几种情况。我们一个一个来说，力求明白。遗憾的是，这里可能会比较冗长，那么死锁分析只好依次顺延了。

# Range S-S锁的获取规则 #

MSDN 对 Range 锁的规则有部分描述，但是言简意赅，以下我们会将各种情况分解开来，理清MSDN中涉及的或者未涉及的规则，这些规则适用于SQL Server 2000/2005/2008/2008 R2。关于MSDN的描述，请参见：http://technet.microsoft.com/zh-cn/library/ms191272(en-us,SQL.110).aspx。

在描述规则之前需要声明的是，我们的聚集索引就建立在 WHERE 字句之上，这很重要，否则是不会获得 Range 锁的，也就达不到 SERIALIZABLE 的要求了；另外，为了讨论简便，以下的 SQL 全部省略 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 的声明。

我们假设有以下的表：

<table style="border:1px solid #C0C0C0;width:1088.8px;margin:0px;padding:0px;background:none;float:none;height:auto;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:12px;min-height:auto;"> 
 <tbody style="margin:0px;padding:0px;background:none;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;min-height:auto;"> 
  <tr style="margin:0px;padding:0px;background:none;border-top:0px;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;min-height:auto;"> 
   <td style="padding:3px;border-color:#C0C0C0;border-collapse:collapse;margin:0px;background:none;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:35px;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:12px;min-height:auto;color:rgb(175,175,175);"> 
    <div style="margin:0px;padding:0px .5em;background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      1 
    </div> 
    <div style="margin:0px;padding:0px .5em;background-image:none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      2 
    </div> 
    <div style="margin:0px;padding:0px .5em;background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      3 
    </div> 
    <div style="margin:0px;padding:0px .5em;background-image:none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      4 
    </div> 
    <div style="margin:0px;padding:0px .5em;background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      5 
    </div> 
    <div style="margin:0px;padding:0px .5em;background-image:none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      6 
    </div> 
    <div style="margin:0px;padding:0px .5em;background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      7 
    </div> 
    <div style="margin:0px;padding:0px .5em;background-image:none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      8 
    </div> 
    <div style="margin:0px;padding:0px .5em;background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      9 
    </div> 
    <div style="margin:0px;padding:0px .5em;background-image:none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      10 
    </div> 
    <div style="margin:0px;padding:0px .5em;background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      11 
    </div> 
    <div style="margin:0px;padding:0px .5em;background-image:none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      12 
    </div> 
    <div style="margin:0px;padding:0px .5em;background:rgb(244,244,244) none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      13 
    </div> 
    <div style="margin:0px;padding:0px .5em;background-image:none;border-width:0px 2px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);float:none;height:auto;line-height:1.8em;text-align:right;vertical-align:baseline;width:auto;min-height:auto;">
      14 
    </div></td> 
   <td style="padding:3px;border-color:#C0C0C0;border-collapse:collapse;margin:0px;background:none;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:12px;min-height:auto;"> 
    <div style="margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.1em;vertical-align:baseline;width:auto;min-height:auto;"> 
     <div style="margin:0px;padding:0px 1em;background:rgb(244,244,244) none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">CREATE</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">TABLE</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[dbo].[MyTable](</code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[id] [</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">int</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">] IDENTITY(1,1) </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#808080;">NOT</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#808080;">NULL</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">,</code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background:rgb(244,244,244) none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[index_column] [</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">int</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">] </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#808080;">NOT</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#808080;">NULL</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">,</code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[data] [</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">int</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">] </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#808080;">NOT</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:#808080;">NULL</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">,</code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background:rgb(244,244,244) none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">CONSTRAINT</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[PK_MyTable] </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">PRIMARY</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">KEY</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">NONCLUSTERED </code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">(</code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background:rgb(244,244,244) none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[id] </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">ASC</code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">)</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">WITH</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">(PAD_INDEX&nbsp; = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">OFF</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">, STATISTICS_NORECOMPUTE&nbsp; = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">OFF</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">, IGNORE_DUP_KEY = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">OFF</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">, ALLOW_ROW_LOCKS&nbsp; = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">ON</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">, ALLOW_PAGE_LOCKS&nbsp; = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">ON</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">) </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">ON</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">PRIMARY</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">]</code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background:rgb(244,244,244) none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">) </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">ON</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">PRIMARY</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">]</code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;">
       &nbsp; 
     </div> 
     <div style="margin:0px;padding:0px 1em;background:rgb(244,244,244) none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">CREATE</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">UNIQUE</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">CLUSTERED </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">INDEX</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[IX_MyTable] </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">ON</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[dbo].[MyTable] </code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">(</code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background:rgb(244,244,244) none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;">&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[index_column] </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">ASC</code> 
     </div> 
     <div style="margin:0px;padding:0px 1em;background-image:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;min-height:auto;"> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">)</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">WITH</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">(PAD_INDEX&nbsp; = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">OFF</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">, STATISTICS_NORECOMPUTE&nbsp; = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">OFF</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">, SORT_IN_TEMPDB = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">OFF</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">, IGNORE_DUP_KEY = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">OFF</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">, DROP_EXISTING = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">OFF</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">, ONLINE = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">OFF</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">, ALLOW_ROW_LOCKS&nbsp; = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">ON</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">, ALLOW_PAGE_LOCKS&nbsp; = </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">ON</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">) </code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">ON</code>&nbsp; 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">[</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,255);">PRIMARY</code> 
      <code style="white-space:pre-wrap;margin:0px;padding:0px;background:none;border:0px;float:none;height:auto;line-height:1.8em;vertical-align:baseline;width:auto;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;min-height:auto;color:rgb(0,0,0);">]</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

并假设我们有如下的数据：

那么这张表看起来应该是这样的（我另外还将Index的Hash值以及row所在的数据页Dump出来了，以便咱们做实验）。

<table style="color:rgb(0,0,0);margin:0px;padding:0px;border:1px solid #C0C0C0;width:802px;"> 
 <tbody style="margin:0px;padding:0px;"> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">id</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">index_column</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">data</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">index hash</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">row page</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">1</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">1</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">1</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">(8194443284a0)</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">78</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">2</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">2</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">2</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">(61a06abd401c)</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">78</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">3</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">3</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">3</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">(98ec012aa510)</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">78</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">4</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">4</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">4</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">(a0c936a3c965)</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">78</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">5</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">5</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">5</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">(59855d342c69)</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">78</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">6</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">15</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">6</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">(f1de2a205d4a)</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">78</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">7</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">16</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">7</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">(f07ed88b2b23)</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">78</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">8</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">18</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">8</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">(e9069d930a93)</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">78</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">9</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">25</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">9</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">(b81181109ebc)</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">78</td> 
  </tr> 
  <tr style="margin:0px;padding:0px;"> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">10</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">30</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">10</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">(8034b699f2c9)</td> 
   <td style="margin:0px;padding:3px;border-color:#C0C0C0;border-collapse:collapse;">78</td> 
  </tr> 
 </tbody> 
</table>

## 对于WHERE子句中的条件命中现有记录的情况 ##

规则一：如果 WHERE 子句使用的是“相等”条件，例如“WHERE \[index\_column\]=6”，并且该索引是唯一索引，则该索引不会获得Key-Range S-S锁，仅仅是Key上获得普通S锁；

假设我们执行

SELECT \[data\] FROM \[MyTable\] WHERE \[index\_column\]=1

那么我们使用 sp\_lock 得到锁的情况：

[![image][]][image 1]

可以发现第一个索引上获得了S锁，但并不是 Range S-S 锁。

规则二：如果 WHERE 子句使用的是“范围”条件，例如“>、<、BETWEEN、IN”等。不论该索引是否唯一，WHERE子句规定都会成为 Range S-S 锁作用的范围，除此之外，在索引排序规则之下，这个作用范围的“下一个”索引项也会获得Range S-S锁。

我们必须首先解释一下“下一个”是怎么一回事，“下一个”索引项有两种情况：

第一：如果在索引排序规则下，作用范围之外按照数据排布的方向能够找到一个存在的，或者是“残存的”索引项（已经提交删除，数据库中再也看不到了，但是还没有从B树数据页中删除），那么这个索引项就是“下一个”索引项；

第二：如果在索引排序规则下，作用范围之外按照数据排布的方向找不到任何残存的索引项，那么无限远（Resource Hash为0xffffffff）的索引项就是“下一个”索引项。

我们结合规则二进行说明，例如我们执行

SELECT \[data\] FROM \[MyTable\] WHERE \[index\_column\]>=1 AND \[index\_column\]<=4

那么 index\_column 中的值为 1、2、3、4的索引会获得 Range S-S 锁，除此以外，4之后的下一个索引值，也就是5对应的索引会获得 Range S-S锁。这和我们的实验结果刚好一致。

[![image][image 2]][image_image 2]

我们再来看着一个，例如我们执行：

SELECT \[data\] FROM \[MyTable\] WHERE \[index\_column\]>=20 AND \[index\_column\]<=40

那么 index\_column 为 25、30的索引会获得 Range S-S 锁，除此以外，30之后的下一个索引值，也就是“无限远”会获得 Range S-S 锁，请看实际Dump的锁的使用情况：

[![image][image 3]][image_image 3]

我们最后练一个稍稍复杂点儿情况：

SELECT \[data\] FROM \[MyTable\]

WHERE (\[index\_column\]>=2 AND \[index\_column\]<=4) OR (\[index\_column\]>=10 AND \[index\_column\]<=16) OR (\[index\_column\]>=30 AND \[index\_column\]<=40)

这里想说明的问题是，我们的“范围”是指一个个的闭合的范围，要一个个套用规则进行分析，我们现在有3块儿闭合的范围，分别是 \[2,4\]、\[10,16\]、\[30,40\]。我们一个个的来，对于\[2,4\]，在这个范围内2，3，4，5获得 Range S-S锁；

对于\[10,16\]范围，15，16，18获得 Range S-S锁；对于\[30,40\]范围，30，无限远获得 Range S-S锁，一共9个。

[![image][image 4]][image_image 4]

规则一补充：如果 WHERE 子句使用的是“相等”条件，但是该索引不是唯一索引，那么除了WHERE命中的索引获得 Range S-S锁之外，“下一个”索引也会获得 Range S-S锁。

我今天仔细的做了关于这个规则的验证。另外查阅了 SQL Server 2000 - 2008 Internals 的图书中关于这个问题的记载。在不是唯一索引的情况下，没有以上这种固定的选择规则。以上规则只有在一些特定情况下才出现。而其他规则是没有问题的。

## 对于WHERE子句中的条件不能命中任何记录的情况 ##

规则三：如果 WHERE 子句使用的是“相等”条件，不论索引是否为唯一索引，若不能够命中任何记录，除该 WHERE 子句规定的那个不存在的记录作为 Range S-S的一部分之外，该记录的“下一个”索引值也将会获得 Range S-S 锁。

例如，我们执行

SELECT \[data\] FROM \[MyTable\] WHERE \[index\_column\]=6

那么下一条索引记录为15所对应的索引，因此这个索引将会获得 Range S-S 锁。

[![image][image 5]][image_image 5]

又例如，我们执行

SELECT \[data\] FROM \[MyTable\] WHERE \[index\_column\]=31

那么下一索引记录应该是“无限远”对应的索引，则这个索引将会获得 Range S-S 锁。

[![image][image 6]][image_image 6]

规则四：如果WHERE子句中使用“范围”条件，不论索引是否为唯一索引，若不能够命中任何记录，除该 WHERE 子句规定的那个不存在的范围作为 Range S-S的一部分外，该范围的“下一个”索引值也将会获得 Range S-S锁。

例如，我们执行

SELECT \[data\] FROM \[MyTable\] WHERE \[index\_column\]>=6 AND \[index\_column\]<=10

我实在是写不动了，请各位开动脑筋吧，这里直接给结果：

[![image][image 7]][image_image 7]

再来一个例子吧，我们执行

SELECT \[data\] FROM \[MyTable\] WHERE \[index\_column\]>30 AND \[index\_column\]<40

结果是：

[![image][image 8]][image_image 8]

好了，这一篇终于搞定了。下一篇我们到了 Range S-U 以及 Range I-N 这下会死锁了，有好戏看了。

分类:  [Concurrent][], [Database][]

[image]: /images/20220528/426ce024b56f41f8ad4220fa0fc0501f.png
[image 1]: http://images.cnblogs.com/cnblogs_com/lxconan/201110/201110210145575790.png
[image 2]: https://images.cnblogs.com/cnblogs_com/lxconan/201110/201110210145589477.png
[image_image 2]: /images/20220528/89b9bd0472114c4992efad3bf200d80f.png
[image 3]: https://images.cnblogs.com/cnblogs_com/lxconan/201110/201110210145591495.png
[image_image 3]: /images/20220528/8a373d8149ba4f1eb004056f50b88480.png
[image 4]: https://images.cnblogs.com/cnblogs_com/lxconan/201110/201110210145598529.png
[image_image 4]: /images/20220528/e7ba67f2b61246159a9b0dfbde83f627.png
[image 5]: https://images.cnblogs.com/cnblogs_com/lxconan/201110/201110210146002499.png
[image_image 5]: /images/20220528/eb866b045e294489a88d97a6802f802d.png
[image 6]: https://images.cnblogs.com/cnblogs_com/lxconan/201110/201110210146019741.png
[image_image 6]: /images/20220528/656304310d034a75af2b91f1df59c752.png
[image 7]: https://images.cnblogs.com/cnblogs_com/lxconan/201110/201110210146019251.png
[image_image 7]: /images/20220528/b109a34887e14b90b5cb1066ef423806.png
[image 8]: https://images.cnblogs.com/cnblogs_com/lxconan/201110/201110210146023221.png
[image_image 8]: /images/20220528/e0ac1ede30d641ee9d318cc45ffe511f.png
[Concurrent]: http://www.cnblogs.com/lxconan/category/429872.html
[Database]: http://www.cnblogs.com/lxconan/category/429874.html