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.
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
另外，当你有时遇到这些问题时，需要考虑的另一件事是锁定可能来自触发器完成的处理。
链接地址: http://www.djcxy.com/p/32848.html