Início / dba / Perguntas / 45962
Accepted
Mitch
Asked: 2013-07-09 18:39:18 +0800 CST

Bloquear dicas sobre pré-condições

  • 772

Supondo por um momento que as referências FK não estejam disponíveis, quais dicas de bloqueio devem ser colocadas nas pré-condições do exemplo a seguir para evitar impasses e dados inconsistentes?

(Este é um exemplo artificial, é claro, para ilustrar um cenário em que uma exclusão/atualização só pode ser executada dependendo do estado de alguma outra tabela)

-- Schema
CREATE TABLE EntityTypes (
    EntityTypeID int NOT NULL, 
    Name nvarchar(50) NOT NULL, 
    CONSTRAINT PK_EntityTypes PRIMARY KEY CLUSTERED (EntityTypeID)
);

CREATE TABLE Entities (
    EntityID int IDENTITY(1,1) NOT NULL,
    EntityTypeID int NOT NULL,
    EntityName nvarchar(50) NOT NULL,
    CONSTRAINT PK_Entities PRIMARY KEY CLUSTERED (EntityID)
);

-- Test data
INSERT INTO EntityTypes (EntityTypeID, Name) VALUES (1, 'Test 1');

-- Updater 1
SET XACT_ABORT ON;
BEGIN TRAN;

IF EXISTS (SELECT 1 FROM Entities WHERE EntityTypeID = 1)
BEGIN 
    RAISERROR('Cannot delete entity type which is referenced', 16, 1);
    RETURN;
END

DELETE FROM EntityTypes WHERE EntityTypeID = 1;

COMMIT TRAN;

-- Updater 2
INSERT INTO Entities (EntityTypeID, EntityName) VALUES (1, 'Test Entity')

Ambiente: SQL Server 2012

sql-server locking

2 respostas

  1. Best Answer

    Assumir que EntityTypeIdé a chave primária de dbo.EntityTypesentão REPEATABLE READseria suficiente:

    SET XACT_ABORT ON;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRAN;

IF EXISTS (SELECT 1 FROM Entities WHERE EntityTypeID = 1)
BEGIN 
    RAISERROR('Cannot delete entity type which is referenced', 16, 1);
    RETURN;
END

DELETE FROM EntityTypes WHERE EntityTypeID = 1;

COMMIT TRAN;

    A NOT EXISTSsintaxe sugerida por Aaron também funcionaria como você precisa em REPEATABLE READ. O mesmo seria alcançado especificando uma dica em dbo.Entities:

    DELETE et
FROM dbo.EntityTypes AS et
WHERE EntityTypeID = 1 
AND NOT EXISTS 
(
  SELECT 1 
  FROM dbo.Entities WITH (HOLDLOCK)
  WHERE EntityTypeID = 1
);
    • 4

  2. Bem, por que a condição "pré"? Por que não:

    DELETE et
  FROM dbo.EntityTypes AS et
  WHERE EntityTypeID = 1 
  AND NOT EXISTS 
  (
    SELECT 1 FROM dbo.Entities
      WHERE EntityTypeID = 1
  );
    • 1

relate perguntas