主页 / dba / 问题 / 45962
Accepted
Mitch
Asked: 2013-07-09 18:39:18 +0800 CST

前提条件的锁定提示

  • 772

假设暂时FK引用不可用，下面例子的前置条件应该放置什么锁提示来防止死锁和数据不一致？

（当然，这是一个人为的例子，用于说明只能根据其他表的状态执行删除/更新的场景）

-- 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')

环境：SQL Server 2012

sql-server locking

2 个回答

  1. Best Answer

    假设EntityTypeId是dbo.EntityTypesthen的主键REPEATABLE READ就足够了：

    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;

    Aaron 建议的NOT EXISTS语法也可以按照您在REPEATABLE READ. 通过指定提示可以达到同样的效果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. 那么，为什么是“前置”条件呢？为什么不：

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

相关问题