主页 / dba / 问题 / 345997
Accepted
SE1986
Asked: 2025-04-02 20:07:34 +0800 CST

为什么更新父表中的非键列需要锁定子表

  • 772

假设以下数据库中有两个表，它们之间存在外键关系：

CREATE DATABASE FKLocksTest
GO
ALTER DATABASE FKLocksTest SET READ_COMMITTED_SNAPSHOT OFF

USE FkLocksTest
GO

CREATE TABLE dbo.Department
(
    DeptID INT PRIMARY KEY,
    Deptname NVARCHAR(10)
)
INSERT INTO dbo.Department VALUES (1,'IT'),(2,'HR')


CREATE TABLE dbo.Person
(
    PersonID INT PRIMARY KEY,
    PersonName NVARCHAR(10),
    DepartmentId INT FOREIGN KEY REFERENCES dbo.Department(DeptId)
)
INSERT INTO dbo.Person VALUES (1,'JohnSmith',2)

如果我运行一些更新，我可以使用跟踪标志 1200 来验证哪些锁被占用。首先，我们可以验证对象 ID：

SELECT  OBJECT_ID('dbo.Department') AS Department,
        OBJECT_ID('dbo.Person') AS Person

在此处输入图片描述

现在我启用 TF1200 并运行一些更新，我可以在消息选项卡中看到已获取的锁。我还会确保使用已提交读：

DBCC TRACEON(1200,-1)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

并运行我的第一次更新

UPDATE dbo.Person SET PersonName = 'Jim'

该计划没有提及部门，并且 Traceflag 输出没有提及对象 ID 581577110，这是有道理的

接下来，如果我跑

UPDATE dbo.Person SET DepartmentId = 1 WHERE PersonID = 1

在这种情况下，执行计划引用了部门表，并且 Traceflag 输出显示在 581577110 上进行了锁定。这也是有道理的，因为 SQL Server 必须检查我们设置的 DepartmentId 是否存在于 Department 表中

这次，如果我更新父表并更新主键列

UPDATE dbo.Department SET DeptID = 10 WHERE DeptID = 1

执行计划显示了对 Person 表的引用，并且我可以看到在 613577224 上被锁定。这是合理的，因为 SQL Server 需要检查是否存在与我们正在更新的旧 DepartmentID 关联的 Person 记录。我还遇到了错误，因为有一个 Person 的 DeptId 为 1。

最后，如果我跑

UPDATE dbo.Department SET Deptname = 'aaa' WHERE DeptID = 1

我可以在 Traceflag 输出中看到对 person 表 (613577224) 进行了锁定，但计划中没有对 person 表的引用

TF1200 的简要输出显示了 Person 表上的锁

    ......

Process 59 acquiring Sch-S lock on OBJECT: 9:613577224:0  (class bit0 ref1) result: OK

......

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

......

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring X lock on OBJECT: 9:613577224:0 [UPDSTATS] (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 releasing lock on OBJECT: 9:613577224:0 [UPDSTATS]

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

......

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock reference on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

Process 59 releasing lock reference on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 1), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 1), lockPartitionId = 0

......

Process 59 releasing lock on OBJECT: 9:613577224:0 

......

Process 59 acquiring IS lock on OBJECT: 9:613577224:0  (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 acquiring Sch-S lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0 (class bit0 ref1) result: OK

Process 59 releasing lock on METADATA: database_id = 9 INDEXSTATS(object_id = 613577224, index_id or stats_id = 2), lockPartitionId = 0

Process 59 releasing lock on METADATA: database_id = 9 STATS(object_id = 613577224, stats_id = 2), lockPartitionId = 0

......

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "FK__Person__Departme__267ABA7A". The conflict occurred in database "FKLocksTest", table "dbo.Person", column 'DepartmentId'.
The statement has been terminated.

Person 表上的锁可能看起来是编译过程的一部分，如果我多次运行 UPDATE，我最终会得到一个更短的锁列表（下面是完整的），尽管这个计划很简单

Process 59 acquiring IX lock on OBJECT: 9:581577110:0  (class bit2000000 ref1) result: OK

Process 59 acquiring IX lock on PAGE: 9:1:280  (class bit2000000 ref1) result: OK

Process 59 acquiring X lock on KEY: 9:72057594043170816 (8194443284a0) (class bit2000000 ref1) result: OK

Process 59 releasing lock reference on KEY: 9:72057594043170816 (8194443284a0)

Process 59 releasing lock reference on PAGE: 9:1:280

我的问题是，当我们更新 Department 表中的非关键列时：

  • 为什么需要对 Person 表进行锁定
  • 为什么只在编译时才锁定
sql-server

1 个回答

  1. Best Answer

    为什么SCH-S在编制UPDATE计划时需要对 Person 表进行锁定？

    我并不完全确定这是否有必要。

    我认为 SQL Server 可能会查看要更新的列并交叉引用它们与其中的信息sys.foreign_key_columns以确定 FK 是否相关。

    对于相反的方向，只有在需要时才需要锁定。

    如果我锁定，SCH-M我dbo.Department仍然可以使用以下内容编制计划而不会被阻止。

    UPDATE dbo.Person SET PersonName = 'Jim' OPTION (RECOMPILE)

    然而，此变体将被阻止

    SET LOCK_TIMEOUT 100

UPDATE dbo.Person SET DepartmentId = 1 WHERE PersonID = 1 OPTION (RECOMPILE)

    发生超时错误时的调用堆栈如下所示

    MDL::LockObjectLocal
CMEDCatalogObject::GetCachedObjectById
CMEDProxyDatabase::GetObjectByObjectId
QOMetadataLoader::NewTableForReferentialConstraint
CUpdUtil::BuildFor
CUpdUtil::LoadConstraints
CLogOp_Update::PcnstrDerive
CLogOpArg::PcnstrDeriveHandler
CLogOpArg::DeriveGroupProperties
COpArg::DeriveNormalizedGroupProperties
COptExpr::DeriveGroupProperties
PqoBuild
CStmtQuery::InitQuery
CStmtDML::InitNormal
CStmtDML::Init
CCompPlan::FCompileStep
CSQLSource::FCompile

    当锁上SCH-M并dbo.Person运行时

     UPDATE dbo.Department SET Deptname = 'aaa' WHERE DeptID = 1 OPTION (RECOMPILE)

    调用堆栈如下所示

    MDL::LockObjectLocal
CMEDCatalogObject::GetCachedObjectById
CMEDProxyDatabase::GetObjectByObjectId
CMEDProxyRelation::GetRelChildObj
CMEDProxyRelation::GetNthReferencedKey
`anonymous namespace'::GetCascadedTriggers
CAlgStmt::GetTriggersHelper
CAlgStmt::GetTriggers
CStmtDML::InitNormal
CStmtDML::Init
CCompPlan::FCompileStep
CSQLSource::FCompile
    • 3

相关问题