SQL 2014新功能介绍系列2 - 管理联机操作的锁优先级(Managed Lock Priority)

在日常工作中，我们可能会碰到这种情况，在一个非常繁忙的环境中，尝试进行联机索引操作(Online Index Rebuild)。由于Online Index Rebuild在操作的时候，会申请Sch-M锁来对表的MetaData进行修改。并且，Sch-M锁和很多其他锁不兼容，就有可能会出现阻塞。

比如以下场景，

我们有5个连接，4个Select，1个Alter database rebuild index。51到55依次进入SQL Server执行，下图左边代表了5个连接，右边是SQL内部维护的Lock Queue（这里只是一个很简单的示例，实际的实现会更为复杂）。这里SQL Server维护了两个Queue，Grant Queue存放了所有被赋予了相关Lock的连接。Wait Queue存放了那些等待相关Lock的连接。

首先，51被执行，由于当前表上没有不兼容的锁，所以51获得了所需要的S锁，并且进入了Grant Queue。

接着，在51还在运行的时候，52进来准备运行Rebuild Index Online，但由于Sch-M和S不兼容，所以52会进入Wait Queue等待51完成。

然后，53，54，55依次进入系统执行。由于它们也是Select语句，理论上，和51没有冲突。所以，如果能并行执行的话，可以获得更好的性能。但实际上，在之前版本里，它确是以另一种方式在执行，如下图所示，53，54，55被阻塞在了Wait Queue中。这是因为在之前的实现里，Wait Queue是一个FIFO的实现。当Queue中的第一个语句在等待资源的时候，即使之后的连接所申请的Lock不存在冲突，它也必须等待，直到之前的连接获得所需的资源。

所以，从之前的图示我们可以看到，如果没有52这个连接，51，53，54，55就可以同时执行。而现在由于存在52，导致后进来的3个连接必须在Grant Queue中等待。这个是一种效率低下的实现。

现在，我们来看看SQL 2014中提供的新特性——Managed Lock Priority（MLP）。在MLP中，我们提供了3种不同的选择，如下图所示：

 

a. Kill all blockers

当执行DDL的时候，如果发现之前有Blocking，DDL会将之前的Blocker终止，然后继续执行。比如，以之前的为例，52会将51终止，然后继续执行。在这里，我们可以通过定义Max_Duration参数来选择等待多少分钟再终止Blocker。

b. Switch to normal queue

这里和上面的区别在于， 在等待Max_Duration之后，如果还没有获得所需要的Lock，它切回到正常的Queue，也就是说，会回到之前的Behavior。

c. Exit DDL after wait

我们也称为“自杀”模式，当Max_Duration到了之后，如果还没有获得所需要的Lock，它会自动退出。

所以，这里我们可以看出，MLP给用户提供了更灵活的选择。如果用户认为Workload更重要，他可以选择“Exit DDL after wait”模式。这样，可以隔一段时间再执行。如果用户认为DDL更重要，可以使用“Kill all blockers”模式。

现在我们来看下在引入MLP后，会发生怎样的变化。这里，我们增加了一个新Queue——Low Priority Queue。

首先，51被执行，由于当前表上没有不兼容的锁，所以51获得了所需要的S锁，并且进入了Grant Queue。

接着，在51还在运行的时候，52进来准备运行Rebuild Index Online，但由于Sch-M和S不兼容，所以52会进入Low Priority Wait Queue等待51完成。

接下来，53，54，55被执行。但现在，它们不会被阻塞在Wait Queue中，所以，它们可以和51被一起执行。等51，53，54，55执行完毕，52被继续执行。这里，我们可以看到这个新的功能可以使得SQL Server在这种情况下获得更好的性能。

接下来，我们来看下，为了实现MLP，SQL语句上增加的新语法。我们增加了两个新的参数（“WAIT_AT_LOW_PRIORITY”和“ABORT_AFTER_WAIT”）来实现对执行的控制。

Alter Table：

<low_priority_lock_wait>::=

{

    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ], ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )

}

 

Alter Index：

<low_priority_lock_wait>::=

{

     WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] , 

                            ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )

 }

最后，需要注意的是，Low Priority Queue只能处理由Partition Switch和Online Rebuild Index所产生的Sch-M lock的请求。对于其他产生的Lock请求，都还会进入正常的Wait Queue中。

这就是今天的分享，更多SQL 2014新功能介绍请持续关注本博客。 下周我们将会介绍SQL 2014备份还原的新特性。