Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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
);
share|improve this answer
    
Does the HOLDLOCK table hint makes dbo.Entities table SERIALIZABLE and hence the second example has lower concurrency than the first REPEATABLE READ example? –  Travis Gan Jul 9 '13 at 14:23
    
@TravisGan No, it will take the same locks as if run at READ COMMITTED but the lock will be held until COMMIT. –  Mark Storey-Smith Jul 9 '13 at 20:08
add comment

Well, why the "pre" condition? Why not:

DELETE et
  FROM dbo.EntityTypes AS et
  WHERE EntityTypeID = 1 
  AND NOT EXISTS 
  (
    SELECT 1 FROM dbo.Entities
      WHERE EntityTypeID = 1
  );
share|improve this answer
    
Despite being a single statement, my understanding is that SQL Server will still only take an shared lock on the Entities during the select. Is there some hidden magic I am unaware of? If this pattern is extended for an insert on EntityTypes, we would end up with the other side taking a shared lock on EntityTypes followed by an Exclusive lock on Entities. Since this would go in the opposite order (S on Entities, X on EntityTypes vs S on EntityTypes, X on Entities) it would still deadlock. Furthermore, this does not carry the side-effects of the example. –  Mitch Jul 9 '13 at 4:12
    
@Mitch No hidden magic, I wasn't suggesting this as a complete solution, just a tidier way to write the code (and it didn't work for a comment). Since Mark has incorporated my syntax and actually thought about the problem, I'll delete. –  Aaron Bertrand Jul 9 '13 at 11:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.