Deadlock Using self-referential foreign key

Using SQL server 2005: I've got a table which has a self referencing foreign key.

A deadlock occurs when an update & an insert on this table occur simultaneously but only in the following conditions:

• the update occurs 1st
• the update is against the record which will be ref开发者_开发知识库erenced by the foreign key of the insert statement

During the update an X lock is created on the primary key which is the clustered index of the table. I have tried several things to try and prevent this happening such as:

• I've tried changing transaction isolation levels
• providing table hints ie WITH(nolock)
• I tried creating a non-clustered index on the primary key of the table so that this could be used instead of the clustered one.

The foreign key needs to be there so removing it is not an option. Any suggestions on how I can either prevent the lock which stops the insert, or allow the insert to work around the lock would be very helpful.

Thanks.

Have you tried separating your UPDATE and INSERT transactions by BEGIN TRANSACTION and COMMIT. That way you stay away from deadlocks.

Something like that;

BEGIN TRANSACTION insert
  <INSERT SQL>
  COMMIT TRANSACTION insert
  BEGIN TRANSACTION update
     <UPDATE SQL>
  COMMIT TRANSACTION update
END

Please provide DDL and your UPDATE / INSERT statements I can't reproduce this.

CREATE TABLE T
(
id int identity(1,1) primary key,
refid int references T(id),
filler char(10)
)

INSERT INTO T (refid)
select number 
FROM master..spt_values where number between 1 and 2248

Connection 1

BEGIN TRAN
UPDATE T SET filler = 'A' WHERE id=500  

Connection 2

BEGIN TRAN
INSERT INTO T (refid) VALUES (500) /*Blocked - No deadlock*/

When a record is found eligible for UPDATE, an X lock is placed on it (or its page etc., depending on the locking granularity chosen by the engine).

This prevents placing the S lock on the resource affected which is required to retrieve its value.

Since inserting a value into the child table requires checking it against the parent table, the INSERT statement will have to wait until the UPDATE transaction commits or rolls back.

If you make the PRIMARY KEY non-clustered, UPDATE should not affect it (unless you are updating the PRIMARY KEY itself which you should not do in normal circumstances), so the INSERT will succeed.

The commands below work for me:

Transaction 1:

CREATE TABLE parent (id INT NOT NULL PRIMARY KEY NONCLUSTERED, value INT NOT NULL, parentId INT REFERENCES parent)

INSERT
INTO    parent
VALUES  (1, 1, NULL)

BEGIN TRANSACTION
UPDATE  parent
SET     value = 2
WHERE   id = 1

Transaction 2:

INSERT
INTO    parent
VALUES  (2, 1, 1)

I recently ran into this exact same problem. As mentioned in this post, the solution for me was to simply add an index on the self-referencing column (not the pk). After that, the deadlocks completely disappeared.