Tutorial :SqlServer 2005: deadlock problem with no shared records


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.


The conflict could happen, because SQL-Server does locking not only on row level, but also on page or even table level.

That means a record can be locked even though it is not actually in use itself, but only a different record "nearby".

SQL Server Lock Contention Tamed might be helpful


Also, another thing to consider when you sometimes get these problems is that the locking could come from processing done by triggers.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »