Tutorial :SqlServer 2005: deadlock problem with no shared records



Question:

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.


Solution:1

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


Solution:2

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
Previous
Next Post »