Início / dba / Perguntas / 345997
Accepted
SE1986
Asked: 2025-04-02 20:07:34 +0800 CST

Por que a atualização de uma coluna não-chave na tabela pai bloqueia a tabela filho?

  • 772

Dado o seguinte banco de dados com duas tabelas tendo relacionamento de chave estrangeira entre elas:

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)

Se eu executar algumas atualizações, posso verificar quais bloqueios são tomados com o traceflag 1200. Primeiro, podemos verificar os IDs dos objetos:

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

insira a descrição da imagem aqui

Agora que habilitei o TF1200 e executei algumas atualizações, posso ver os bloqueios realizados na aba de mensagens. Também vou garantir que estou usando a leitura confirmada:

DBCC TRACEON(1200,-1)

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

e executar minha primeira atualização

UPDATE dbo.Person SET PersonName = 'Jim'

O Plano não faz referência ao Departamento e a saída do Traceflag não faz referência ao ID do objeto 581577110, o que faz sentido

A seguir, se eu correr

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

Neste caso, o plano de execução faz referência à tabela de departamentos e a saída do Traceflag mostra os bloqueios realizados em 581577110. Isso também faz sentido, pois o SQL Server precisa verificar se o DepartmentId que estamos definindo existe na tabela Department

Desta vez, se eu atualizar a tabela pai e atualizar a coluna Chave primária

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

O plano de execução mostra uma referência à tabela Person e consigo ver os bloqueios realizados em 613577224. Isso faz sentido, pois o SQL Server precisará verificar se não há registros Person com o DepartmentID antigo que estamos atualizando. Também recebo um erro, pois há uma Person associada ao DeptId 1.

Finalmente, se eu correr

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

Posso ver na saída do Traceflag que um bloqueio foi feito na tabela de pessoas (613577224), mas não há nenhuma referência à tabela de pessoas no plano

A saída resumida do TF1200 mostra os bloqueios realizados na tabela Pessoa

    ......

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.

O bloqueio na tabela Pessoa possivelmente parece fazer parte do processo de compilação, se eu executar o UPDATE várias vezes, eventualmente obtenho uma lista muito menor de bloqueios (abaixo na íntegra), embora o plano seja um plano trivial

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

Minhas perguntas são: quando atualizamos uma coluna não-chave na tabela Departamento:

  • Por que o bloqueio na tabela Pessoa é necessário
  • Por que o bloqueio só é feito em tempo de compilação?
sql-server

1 respostas

  1. Best Answer

    Por que o SCH-Sbloqueio na tabela Pessoa é necessário durante a compilação de um UPDATEplano?

    Não tenho certeza se é necessário.

    Imagino que o SQL Server poderia potencialmente analisar as colunas a serem atualizadas e fazer referência cruzada delas com as informações sys.foreign_key_columnspara determinar se o FK é relevante ou não.

    Para a direção oposta, ele só pega a trava quando necessário.

    Se eu bloquear SCH-M, dbo.Departmentainda posso compilar um plano com o abaixo sem ficar bloqueado.

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

    Esta variante será bloqueada, no entanto

    SET LOCK_TIMEOUT 100

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

    A pilha de chamadas quando ocorre o erro de tempo limite se parece com isso

    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

    Ao colocar a SCH-Mfechadura dbo.Persone executá-la

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

    A pilha de chamadas se parece com isso

    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

relate perguntas