Lock hints on preconditions Assuming for a moment that FK references are not available, what lock hints should be placed on the preconditions of the following example to prevent deadlocks and inconsistent data?

(This is a contrived example, of course, to illustrate a scenario in which a delete/update can only be performed dependent upon the state of some other table)

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


Environment: SQL Server 2012
Assuming EntityTypeId is the primary key of dbo.EntityTypes then REPEATABLE READ would suffice:

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;


The NOT EXISTS syntax Aaron suggested would also function as you require at REPEATABLE READ. The same would be achieved by specifying a hint on 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
);