sql-server - 如何在测试期间强制升级锁(以消除死锁问题)？

Question

在此处发布问题和一个答案。也许有人有更好的答案...

如果开发人员不小心打开了与数据库的第二个连接而不是重用现有的连接(可能已经打开了一个交易)。某些 O/RM 和 LINQ 框架很容易犯这个错误。

这是纯 SQL 术语中的场景:

--Setup:
CREATE TABLE Vendors(VendorID int NOT NULL, VendorName nvarchar(100) NOT NULL, ExtraColumn int not null, CONSTRAINT PK_Vendors PRIMARY KEY CLUSTERED(VendorID))
GO
INSERT INTO Vendors(VendorID,VendorName,ExtraColumn) VALUES (1, 'Microsoft', 12345)
INSERT INTO Vendors(VendorID,VendorName,ExtraColumn) VALUES (2, 'Oracle', 12345)

--Connection 1:
BEGIN TRANSACTION
UPDATE Vendors SET ExtraColumn = 222 WHERE VendorID = 2

--Connection 2:
BEGIN TRANSACTION
SELECT VendorName FROM Vendors WHERE VendorID = 1

此代码(以及 c#/java/ORM/LINQ/生成它的任何代码)在数据较小时可能在开发/测试中运行良好，但在生产中数据/内存配置文件发生变化且锁升级时突然死锁从行到页到表。

那么我如何才能在我的测试环境中强制将锁升级到表级别，以便我可以清除任何这样的错误(代码打开第二个连接的地方)？

锁升级完全由 SQL Server 数据库引擎控制，无法预测何时从行锁升级到表锁。我不会重复您可以找到的所有细节 here ，但要特别注意一个:“当……数据库引擎实例中的锁数超过内存或配置阈值时触发锁升级。”这意味着它可能完全不相关到我精心设计的代码和完美选择的索引。

Answer 1

一种方法是:

1. 在测试环境中，运行如下脚本来禁用所有索引的行和页锁定(使其直接进入表锁定)
2. 运行测试
3. 再次运行脚本，将其设置回正常锁定状态

--
-- Script to disable/enable row & page locking on dev/test environment to flush out deadlock issues
-- executes statement like this for each table in the database:
-- ALTER INDEX indexname ON tablename SET ( ALLOW_ROW_LOCKS  = OFF, ALLOW_PAGE_LOCKS  = OFF )
--
-- DO NOT RUN ON A PRODUCTION DATABASE!!!!
--
set nocount on
declare @newoption varchar(3)
--------------------------------------------------------------------
-- Change variable below to 'ON' or 'OFF' --------------------------
--   'OFF' means row & page locking is disabled and everything 
--      triggers a table lock
--   'ON' means row & page locking is enabled and the server chooses
--     how to escalate the locks (this is the default setting)
set @newoption = 'OFF'
--------------------------------------------------------------------

DECLARE    @TableName varchar(300)
DECLARE    @IndexName varchar(300)
DECLARE @sql varchar(max)

DECLARE inds CURSOR FAST_FORWARD FOR
SELECT tablename, indname
FROM (
    select top 100 percent
    so.name as tablename
         , si.indid
         , si.name as indname
         , INDEXPROPERTY( si.id, si.name, 'IsPageLockDisallowed') as IsPageLockDisallowed
         , INDEXPROPERTY( si.id, si.name, 'IsRowLockDisallowed') as IsRowLockDisallowed
    from   sysindexes si
    join sysobjects so on si.id = so.id
    where  si.status & 64 = 0
      and  objectproperty(so.id, 'IsMSShipped') = 0
      and si.name is not null
      and so.name not like 'aspnet%'
      and so.name not like 'auditLog%'
    order by so.name, si.indid
) t

OPEN inds
FETCH NEXT FROM inds INTO @TableName, @IndexName

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @sql = 'ALTER INDEX [' + @IndexName + '] ON [dbo].[' + @TableName + '] SET ( ALLOW_ROW_LOCKS  = ' + @newoption + ', ALLOW_PAGE_LOCKS  = ' + @newoption +' )'
    PRINT @sql
    EXEC(@sql)

    FETCH NEXT FROM inds INTO @TableName, @IndexName
END

CLOSE inds
DEALLOCATE inds


PRINT 'Done'

其他说明:

• 我从别人的文章中得到了上述脚本的核心，但我早就忘记了在哪里。为缺乏署名表示歉意。
• 请注意，上述脚本将覆盖您的表上任何现有的自定义锁定升级设置。您可以通过首先运行内部选择(“SELECT TOP 100 PERCENT so.name...”)来查看现有设置来检查这些设置。