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.

@Mitch: It means that I run transaction 1 until this point, then run transaction 2, which waits on the select, then I run transaction 1 to the end, which also waits on the select. — Stefan Steinegger, Sep 17 '09 at 07:23
@StefanSteinegger when first transaction would execute update statement, the second transaction would wait at the update statement and cannot proceed further till first transaction commits. How is deadlock possible here? — Rajat Aggarwal, Dec 18 '21 at 20:07

Wolfgang · Accepted Answer · 2012-05-18T15:43:34.973

8

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

edited May 18 '12 at 15:43

answered Sep 17 '09 at 07:31

Wolfgang

3,460
3
28
38

Yes - if it can't obtain row-level locking, it escalates to page and then table. – Sep 17 '09 at 07:37
But isn't this a lock escalation? Shouldn't it be traced in the profiler? – Stefan Steinegger Sep 17 '09 at 07:39
From my understanding Sql Server could decide to use page locking from the start. In that case there would be no escalation. – Wolfgang Sep 17 '09 at 11:09
Thanks for the notice. Seems the article was moved to a new location. I updated the link so it should be working again – Wolfgang May 18 '12 at 15:45

score 1 · Answer 2 · answered Sep 17 '09 at 17:40

1

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

answered Sep 17 '09 at 17:40

HLGEM

94,695
15
113
186

Fortunately, I don't have any triggers in the db. Thanks anyway. – Stefan Steinegger Sep 18 '09 at 06:46
Could you please elaborate on why triggers make any difference? – Eugene D. Gubenkov Oct 14 '16 at 08:35
triggers may update other tables, which can reduce concurrency & this looks like an unexpected lock if you didn't realise there was a trigger present – simon coleman Dec 07 '21 at 12:06

SqlServer 2005: deadlock problem with no shared records

2 Answers2

Linked