SQLServer 2005:没有共享记录的死锁问题 [英] 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)
表 A
在三个表的中间。如果我在查询中进行了任何更改,例如删除联接表 B
或 C
之一,则没有死锁。当我通过 A.id
而不是 A.value
进行过滤时。
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
.
死锁图告诉我,他们俩都希望为表 A
的主键索引设置S锁。再次:没有锁升级。
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.
我正在使用SqlServer 2005。
I'm using SqlServer 2005.
- 为什么这些事务在不访问任何公共数据的情况下发生冲突?有人可以解释吗?
- 如何避免这种情况?我正在使用NHibernate,无法轻松更改查询。
- 这可能是SqlServer问题吗?
非常感谢。
推荐答案
可能会发生冲突,因为SQL-Server不仅锁定行级,也可以在页面甚至是表格级别使用。
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锁争用可能有帮助
这篇关于SQLServer 2005:没有共享记录的死锁问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!