sql server 锁定_如何使用SQL Server 2014托管锁定优先级控制在线索引重建锁定

sql server 锁定

When you perform a SQL Server Online Index Rebuild operation, introduced for the first time in SQL Server 2005, the index will not be taken down. But at a specific point, in which the new index new is built and switched from the old structure of the index, a special kind of lock, Schema Modification (SCH-M), will be granted. This lock may cause blocking if your database server is busy.

当您执行SQL Server 2005中首次引入SQL Server联机索引重建操作时，将不会删除索引。 但是在特定的时候，将建立新的索引new并从索引的旧结构切换为新的索引，将授予一种特殊的锁，即模式修改（SCH-M） 。 如果您的数据库服务器正忙，此锁定可能会导致阻塞。

The SCH-M lock is used for table structure change DDL operations. In these cases, SQL Server will wait until the index rebuild process release that lock, as all operations have the same priority, which may take a long time for large and/or high throughput tables.

SCH-M锁用于表结构更改DDL操作。 在这些情况下，SQL Server将等待直到索引重建过程释放该锁定为止，因为所有操作都具有相同的优先级，这对于大型和/或高吞吐量表可能会花费很长时间。

On the other hand, another type of locking, called the Schema Stability Lock (SCH-S), is used by any DML T-SQL query that reads or modifies table data to make sure that the structure of the table that the query is using will not be changed until the query is finished and the SCH-S lock is released. In other words, you cannot perform an online index rebuild on a table with queries running on it that have a SCH-S lock granted on the table, which effectively prevents you from changing the table’s schema mid-query. Also, you cannot change or read data from a table with SCH-M lock type during an online index rebuild operation.

另一方面，任何读取或修改表数据的DML T-SQL查询都使用另一种类型的锁，称为架构稳定性锁（SCH-S） ，以确保查询所使用的表的结构在查询完成并释放SCH-S锁之前，将不会更改。 换句话说，如果表上运行的查询具有授予该表SCH-S锁的查询，则无法在该表上执行在线索引重建，这实际上会阻止您更改表的架构中间查询。 此外，在联机索引重建操作期间，您无法从具有SCH-M锁定类型的表中更改或读取数据。

Prior to SQL Server 2014, there were two main queues that were maintained to manage locks; Grant and Wait queues. When a lock is requested and can be handled, it will be allocated into the grant queue then executed, otherwise, it will wait in the wait queue with all other processes in that queue with the same priority. In SQL Server 2014, new functionality was introduced that allows you to control how the blocking mechanism, that is required by the online index rebuild operation, is handled. The new functionality is called Managed Lock Priority. This functionality benefits from the newly defined Low-Priority queue that contains the processes with priorities lower than the ones waiting in the wait queue, giving the database administrator the ability to manage the waiting priorities.

在SQL Server 2014之前，维护了两个主要队列来管理锁。 授予和等待队列。 当请求并可以处理锁时，它将被分配到授予队列中然后执行，否则，它将与该队列中具有相同优先级的所有其他进程一起在等待队列中等待。 在SQL Server 2014中，引入了新功能，使您可以控制如何处理在线索引重建操作所需的阻止机制。 新功能称为“ 托管锁定优先级” 。 此功能得益于新定义的低优先级队列，该队列包含优先级低于等待队列中的进程的进程，从而使数据库管理员能够管理等待的优先级。

To use the low priority lock, you need to implement it in ALTER INDEX and ALTER TABLE statements used for the online index rebuild and partition switch operations.

要使用低优先级锁定，您需要在用于联机索引重建和分区切换操作的ALTER INDEX和ALTER TABLE语句中实现它。

The definition of the low priority wait consists of three parts;

低优先级等待的定义包括三个部分：

• WAIT_AT_LOW_PRIORITY option keyword, WAIT_AT_LOW_PRIORITY选项关键字，
• MAX_DURATION property that defines how long, in minutes, the command will wait MAX_DURATION属性，该属性定义命令将等待多长时间（以分钟为单位）
• ABORT_AFTER_WAIT property that specifies the session that will be rolled back after the wait time expired.  ABORT_AFTER_WAIT属性，该属性指定在等待时间到期后将回滚的会话。

The ABORT_AFTER_WAIT parameter comes with three options:

ABORT_AFTER_WAIT参数带有三个选项：

• NONE: The online index rebuild operation will be returned to the wait queue after the wait time is expired, changing the low priority wait into normal wait priority. This will work same as previous SQL Server versions. This option helps in giving other queries time, equal toNONE ：等待时间到期后，在线索引重建操作将返回到等待队列，将低优先级等待更改为正常等待优先级。 这将与以前SQL Server版本相同。 此选项有助于使其他查询时间等于 MAX_DURATION, to proceed without any blocking. MAX_DURATION，继续进行而没有任何阻塞。
• SELF: The online index rebuild operation will be rolled back if it is not completed after specific time equal toSELF ：如果在线索引重建操作在特定时间等于 MAX_DURATION, giving the priority to other user actions instead of the online index rebuild operation. MAX_DURATION，优先于其他用户操作，而不是在线索引重建操作。
• BLOCKERS: All transactions that are blocking the online index rebuild operation will be killed if the blocking still occurring after expiring the MAX_DURATION time. So that you can easily rebuild the index. BLOCKERS ：如果在MAX_DURATION时间到期后仍在发生阻塞，则所有阻塞在线索引重建操作的事务都将被杀死。 这样您就可以轻松地重建索引。

If you do not specify the WAIT_AT_LOW_PRIORITY option keyword in the online index rebuild statement, SQL Server will set the MAX_DURATION property to 0 and the ABORT_AFTER_WAIT property to NONE, working same as the previous SQL Server versions. Take into consideration that the WAIT_AT_LOW_PRIORITY option specifies what will happen only when a blocking situation is occur. If you look into the sys.dm_os_wait_stats DMV, you will find 21 new wait types related to the SQL Server 2014 Lock Priorities. The below T-SQL statement can be used to retrieve these wait types:

如果您在联机索引重建语句中未指定WAIT_AT_LOW_PRIORITY选项关键字，则SQL Server会将MAX_DURATION属性设置为0，将ABORT_AFTER_WAIT属性设置为NONE，与以前SQL Server版本相同。 考虑到WAIT_AT_LOW_PRIORITY选项指定仅在发生阻塞情况时将发生的情况。 如果查看sys.dm_os_wait_stats DMV，将发现21种与SQL Server 2014锁定优先级相关的新等待类型。 下面的T-SQL语句可用于检索这些等待类型：

 
SELECT wait_type  FROM sys.dm_os_wait_stats  WHERE wait_type LIKE '%_LOW_PRIORITY'
 

The Lock Priority related (LCK_M) wait types will be as shown below:

与锁定优先级相关的（LCK_M）等待类型如下所示：

Let us see how we can use the new Lock Priority feature to control the locking process. We will start by creating a simple table with clustered index on the Primary Key and one non-clustered index using the T-SQL script below:

让我们看看如何使用新的“锁定优先级”功能来控制锁定过程。 我们将首先使用以下T-SQL脚本创建一个简单的表，该表具有在主键上的聚集索引和一个非聚集索引：

 
USE SQLShackDemo 
GO
CREATE TABLE LockPriorityTest
( ID INT IDENTITY (1,1) PRIMARY KEY,
  EmpName NVARCHAR (50),
  EmpPhone NVARCHAR (50),
  EmpAddress NVARCHAR (MAX)
  )
  GO
CREATE NONCLUSTERED INDEX IX_LockPriorityTest_EmpName ON [LockPriorityTest] (EmpName,EmpPhone)
 

After creating the table, we will fill that table with 1 million records using ApexSQL Generate tool, a SQL Server test data generator:

创建表后，我们将使用ApexSQL Generate工具（SQL Server测试数据生成器）用一百万条记录填充该表：

Now the table is created and filled with the testing data. We will run two queries, the first one is a SELECT query from the created table executed within a transaction and locking the table for one minute and half then it will be rolled back. This is achieved by running the below T-SQL script under session number 54:

现在，表已创建并填充了测试数据。 我们将运行两个查询，第一个查询是在事务中执行的已创建表的SELECT查询，并将该表锁定一分半钟，然后将其回滚。 这是通过在会话号54下运行以下T-SQL脚本来实现的：

At the same time, we will perform an online index rebuild operation on the same table to rebuild the non-clustered index after inserting the large batch of records. This is also achieved using the below normal ALTER INDEX query with no extra new options running under session number 57:

同时，我们将在插入大量记录后对同一表执行联机索引重建操作，以重建非聚集索引。 这也可以使用下面的正常ALTER INDEX查询来实现，而没有在会话号57下运行额外的新选项：

You can see clearly that the ALTER INDEX statement is blocked until the previous SELECT statement that is locking the table is finished. Let us take a chance of this blocking happening, while the two queries are running, to check the wait status of these two queries using the SP_WHOISACTIVE statement. The result will show us that the ALTER INDEX statement running under session 57 is blocked by the SELECT statement running under session 54 as shown below:

您可以清楚地看到，直到锁定表的上一个SELECT语句完成为止，ALTER INDEX语句才被阻塞。 让我们趁两个查询正在运行时发生这种阻塞的机会，使用SP_WHOISACTIVE语句检查这两个查询的等待状态。 结果将向我们显示在会话57下运行的ALTER INDEX语句被在会话54下运行的SELECT语句阻止，如下所示：

What is happening internally is that, the first SELECT query is running against the table, with a SCH-S lock granted to it on that testing table. When the online ALTER INDEX T-SQL command is issued, it asks for SCH-M lock on the same table that is already locked with the SCH-S lock granted to session number 54. So, the SCH-M lock request will get into the wait queue until the SCH-S lock is released.

内部发生的事情是，第一个SELECT查询针对该表运行，并且在该测试表上授予了SCH-S锁。 发出联机ALTER INDEX T-SQL命令时，它会在已用授予会话号54的SCH-S锁锁定的同一表上请求SCH-M锁。因此，SCH-M锁请求将进入等待队列，直到SCH-S锁被释放。

Any SELECT or UPDATE query issued on that table after that will ask for the SCH-S lock and get into the wait queue too waiting its turn. When the SCH-S lock that is granted to the first SELECT query on the table is released, the SCH-M lock will be granted to the online ALTER INDEX query on the same table, so that the online index operation will be processed. After completing the online index rebuild operation, all queries in the wait list will be processed after being granted the SCH-S lock on the table, continuing the normal operations on the table. This is the ideal scenario, but you can imagine a situation with queries taking a long time running against a table or rebuilding the indexes for huge tables.

此后在该表上发出的任何SELECT或UPDATE查询都将请求SCH-S锁定，并进入等待队列，也等待轮到它。 释放授予表上第一个SELECT查询的SCH-S锁时，将SCH-M锁授予同一表上的联机ALTER INDEX查询，以便处理联机索引操作。 完成联机索引重建操作后，将在等待表中的所有查询被授予表上的SCH-S锁之后，将继续处理表上的正常操作。 这是理想的情况，但是您可以想象这样的情况：查询需要花费很长时间在表上运行或为大型表重建索引。

Let us see how this situation will differ in the context that includes the SQL Server 2014 Low Priority option. To be able to use the low priority option, the WAIT_AT_LOW_PRIORITY keyword will be added and provided with two parameters, MAX_DURATION and ABORT_AFTER_WAIT, as mentioned previously. We will study how we can manage the lock priority using the different ABORT_AFTER_WAIT options.

让我们看看这种情况在包括SQL Server 2014低优先级选项的上下文中将有何不同。 为了能够使用低优先级选项，将添加WAIT_AT_LOW_PRIORITY关键字并为其提供两个参数，MAX_DURATION和ABORT_AFTER_WAIT，如前所述。 我们将研究如何使用不同的ABORT_AFTER_WAIT选项来管理锁定优先级。

ABORT_AFTER_WAIT = NONE (ABORT_AFTER_WAIT = NONE)

When we set the ABORT_AFTER_WAIT parameter of the WAIT_AT_LOW_PRIORITY ALTER INDEX option to NONE, using the ALTER INDEX T-SQL statement shown below:

当我们使用如下所示的ALTER INDEX T-SQL语句将WAIT_AT_LOW_PRIORITY ALTER INDEX选项的ABORT_AFTER_WAIT参数设置为NONE时：

 
USE SQLShackDemo 
GO
ALTER INDEX IX_LockPriorityTest_EmpName 
ON dbo.[LockPriorityTest]
REBUILD WITH (FILLFACTOR = 90, ONLINE = ON ( WAIT_AT_LOW_PRIORITY
   ( MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE ) ));
 

The result will show that SQL Server will act the same way we saw in the previous example and the SQL Server versions prior to SQL Server 2014 after the MAX_DURATION time is expired, where the online index rebuild operation will be returned to the wait queue after the wait time elapsed. With the MAX_DURATION parameter value provided equal to zero, the online index rebuild will wait in the wait queue with no change from the normal online index rebuild operation, as shown below:

结果将显示，在MAX_DURATION时间到期后，SQL Server的行为将与上一个示例以及SQL Server 2014之前SQL Server版本相同，在此操作之后，在线索引重建操作将返回到等待队列。等待时间已过。 在提供的MAX_DURATION参数值为零的情况下，联机索引重建将在等待队列中等待，而与正常的联机索引重建操作没有任何变化，如下所示：

ABORT_AFTER_WAIT =自 (ABORT_AFTER_WAIT = SELF)

Setting the ABORT_AFTER_WAIT parameter of the WAIT_AT_LOW_PRIORITY ALTER INDEX option to SELF, using the following ALTER INDEX T-SQL statement:

使用以下ALTER INDEX T-SQL语句将WAIT_AT_LOW_PRIORITY ALTER INDEX选项的ABORT_AFTER_WAIT参数设置为SELF：

 
USE SQLShackDemo 
GO
ALTER INDEX IX_LockPriorityTest_EmpName 
ON dbo.[LockPriorityTest]
REBUILD WITH (FILLFACTOR = 90, ONLINE = ON( WAIT_AT_LOW_PRIORITY
    ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF) ));
 

The result shows that the ALTER statement is blocked by session 54 again and waiting to be granted a new lock type, the LCK_M_XX_OW_PRIORITY lock, in the Low Priority queue, as shown below:

结果显示，ALTER语句再次被会话54阻塞，并等待被授予低优先级队列中的新锁类型LCK_M_XX_OW_PRIORITY锁，如下所示：

It will wait for the MAX_DURATION time to be expired, which is 1 minute in our example. If the blocking query is not finished, the ALTER INDEX statement will terminate itself and rolled back, leaving the priority to the other queries to be executed, generating the “Lock request time out period exceeded” error shown below:

它将等待MAX_DURATION时间到期，在我们的示例中为1分钟。 如果阻塞查询尚未完成，则ALTER INDEX语句将自行终止并回滚，将优先级留给其他要执行的查询，从而产生“超出锁定请求超时时间”错误，如下所示：

ABORT_AFTER_WAIT =阻止者 (ABORT_AFTER_WAIT = BLOCKERS)

The last scenario when we set the ABORT_AFTER_WAIT parameter of the WAIT_AT_LOW_PRIORITY ALTER INDEX option to BLOCKERS, using the following ALTER INDEX T-SQL statement:

我们使用以下ALTER INDEX T-SQL语句将WAIT_AT_LOW_PRIORITY ALTER INDEX选项的ABORT_AFTER_WAIT参数设置为BLOCKERS的最后一种情况：

 
USE SQLShackDemo 
GO
ALTER INDEX IX_LockPriorityTest_EmpName 
ON dbo.[LockPriorityTest]
REBUILD WITH (FILLFACTOR = 90, ONLINE = ON( WAIT_AT_LOW_PRIORITY
   ( MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = BLOCKERS) ));
 

The result shows that the ALTER statement that is blocked by session number 54 is waiting to be granted the LCK_M_XX_OW_PRIORITY lock, in the Low Priority queue, as shown below:

结果显示，被会话号54阻止的ALTER语句正在等待被授予低优先级队列中的LCK_M_XX_OW_PRIORITY锁，如下所示：

In addition, it will wait for the MAX_DURATION time to be expired, which is 1 minute as specified in the query. If the blocking query is not finished in one minute, the ALTER INDEX statement will kill all blocking transactions, which is session number 54 in our example, showing that the session is killed due to a high priority DDL operation, as you can see from the below error message:

此外，它将等待MAX_DURATION时间到期，该时间为查询中指定的1分钟。 如果阻塞查询在一分钟内未完成，则ALTER INDEX语句将杀死所有阻塞事务，在我们的示例中，会话数为54，这表明该会话由于高优先级DDL操作而被杀死，如您所见。下面的错误信息：

On the other side, the online index rebuild operation will be completed successfully in short time as all blockers are killed:

另一方面，由于杀死了所有阻止程序，在线索引重建操作将在短时间内成功完成：

结论 (Conclusion)

SQL Server 2014 comes with a new lock queue that helps in managing the locking process more efficiently. The Low Priority option allows us to control the blocking that is required for the online index rebuild operation. Within this article, we described the different options for the low priority option and how the online index rebuild operation behaves with these options.

SQL Server 2014附带了一个新的锁定队列，可帮助更有效地管理锁定过程。 低优先级选项使我们可以控制在线索引重建操作所需的阻塞。 在本文中，我们介绍了低优先级选项的不同选项，以及这些选项对联机索引重建操作的行为。

Each option described fits a specific environment in which you decide to give priority to the online index rebuild operation DDL statement over the other queries running against a database table or give it the chance to run within a specified time then stopped to give the chance to the other DML queries.

所描述的每个选项都适合特定的环境，在该环境中，您决定优先于在线索引重建操作DDL语句，而不是针对数据库表运行的其他查询，或者赋予它在指定时间内运行然后停止的机会，以使其他DML查询。

翻译自: https://www.sqlshack.com/control-online-index-rebuild-locking-using-sql-server-2014-managed-lock-priority/

sql server 锁定