I have a deadlock problem with two transactions that do not access any common records. There is also no lock escalation. So I can't explain why a deadlock is possible.
The deadlock occurs when two such transactions are executed at the same time:
begin transaction
update A set [value] = [value]
where id = 1; /* resp. 2 */
/* synchronize transactions here */
SELECT *
FROM
A inner join B on A.B_FK = B.id
inner join C on C.A_FK = A.id
WHERE
A.[value] = 1; /* resp. 2 */
rollback;
These are the tables and data to setup the scenario:
CREATE TABLE A (
id INT NOT NULL,
[value] INT,
B_FK INT
primary key (id)
)
CREATE TABLE B (
id INT NOT NULL,
primary key (id)
)
CREATE TABLE C (
id INT NOT NULL,
A_FK INT
primary key (id)
)
INSERT INTO A VALUES(1, 1, 1)
INSERT INTO B VALUES(1)
INSERT INTO C VALUES(1, 1)
INSERT INTO A VALUES(2, 2, 2)
INSERT INTO B VALUES(2)
INSERT INTO C VALUES(2, 2)
Table A is in the middle of three tables. If I change anything in the query, for instance remove one of the joined tables B or C, there is no deadlock. The same when I filter by A.id instead of A.value.
The deadlock-graph tells me that they both want to set an S lock to the primary key index of table A. Again: there is no lock escalation.
I'm using SqlServer 2005.
- Why are these transactions conflicting without accessing any common data? Can anyone explain this?
- What can I do to avoid it? I'm using NHibernate and can not change the query that easily.
- Could it be an SqlServer issue?
Thanks a lot.