Why does SQL Server explicit predicate locking disallow INSERT statements outside of the predicate lock
假设我们有以下数据库表：
|
1
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
create table department (
id bigint not null, budget bigint not null, name varchar(255), primary key (id) ) create table employee ( alter table employee |
我们有 3 个 department 行：
|
1
2 3 4 5 6 7 8 |
insert into department (name, budget, id)
values (‘Department 1’, 100000, 1) insert into department (name, budget, id) insert into department (name, budget, id) |
我们还有 3 个 employee 行：
|
1
2 3 4 5 6 7 8 |
insert into employee (department_id, name, salary, id)
values (1, ‘CEO’, 30000, 1) insert into employee (department_id, name, salary, id) insert into employee (department_id, name, salary, id) |
假设我们有两个并发用户：Alice 和 Bob。
首先，Alice 锁定所有属于第一个 department 的员工：
|
1
2 3 4 |
SELECT *
FROM employee WITH (HOLDLOCK) WHERE department_id = 1 |
现在，与此同时，预计 Bob 不能使用相同的 department_id 插入新的 employee：
|
1
2 |
INSERT INTO employee WITH(NOWAIT) (department_id, name, salary, id)
VALUES (1, ‘Carol’, 9000, 6) |
上面的插入语句以 Lock request time out period exceeded 结尾，这很好，因为 Alice 锁定了那个特定的范围。
但是，为什么下面的插入也被阻止了：
|
1
2 |
INSERT INTO employee WITH(NOWAIT) (department_id, name, salary, id)
VALUES (3, ‘Dave’, 9000, 7) |
这个插入语句使用了一个超出 Alice 谓词锁范围的 department_id 值。但是，这个插入语句也以 Lock request time out period exceeded 异常结束。
为什么 SQL Server HOLDLOCK 谓词锁会超出其范围？
更新
通过向 FK 添加索引：
|
1
|
create index IDX_DEPARTMENT_ID on employee (department_id)
|
并将第一个和第二个 department 中的 employee 条目数增加到 1000，我设法看到谓词锁的行为符合预期。
满足 SELECT 查询的唯一方法是执行表扫描。没有自然资源可以仅基于 department_id 来定位和锁定，因此它最终会锁定所有行并阻止任何插入。
在这个玩具示例中，仅在 department_id 上添加索引将无济于事，因为优化器仍会选择执行表扫描。但是，在更大更现实的表上，我相信添加这样的索引将允许查询以更有针对性的方式应用锁。
Damien 说的是正确的..当您没有部门 id(谓词列)的索引时，范围会增加并且 HoldLock 意味着
HOLDLOCK means SERALIZABLE and therefore allows SELECTS, but blocks UPDATE and DELETES of the rows selected by T1, as well as any INSERT in the range selected by T1 .
所以在这种情况下，以下形式的索引会有所帮助，我的测试也证实了这一点
下面是我做的一个示例测试
在会话 1 中：
|
1
2 3 4 5 6 7 8 9 10 11 |
create index nci_department_id on dbo.employee(department_id)
include (id,name,salary) go begin tran SELECT * |
在会话 2 中：
|
1
2 |
INSERT INTO employee WITH(NOWAIT) (department_id, name, salary, id)
VALUES (3, ‘Dave’, 9000, 7) |
现在上面的插入成功了
参考资料：
https://stackoverflow.com/a/7845331/2975396
来源：https://www.codenong.com/42580238/
