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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With