SQLServer并发控制：谓词锁对外部插入的影响

SQL Server 显式谓词锁定与外部插入限制

在 SQL Server 中，显式谓词锁定（如使用 HOLDLOCK 提示）旨在确保数据的一致性和完整性，尤其是在高并发场景下。本文将探讨为什么在某些情况下，即使插入的数据不在已锁定的谓词范围内，SQL Server 也会阻止这些插入操作。

考虑以下两个表结构：

CREATE TABLE department (
  id BIGINT NOT NULL,
  budget BIGINT NOT NULL,
  name VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE employee (
  id BIGINT NOT NULL,
  name VARCHAR(255),
  salary BIGINT NOT NULL,
  department_id BIGINT,
  PRIMARY KEY (id)
);

ALTER TABLE employee
ADD CONSTRAINT FKbejtwvg9bxus2mffsm3swj3u9
FOREIGN KEY (department_id) REFERENCES department;

初始状态下，这两个表包含以下数据：

-- 部门数据
INSERT INTO department (name, budget, id) VALUES ('Department 1', 100000, 1);
INSERT INTO department (name, budget, id) VALUES ('Department 2', 75000, 2);
INSERT INTO department (name, budget, id) VALUES ('Department 3', 90000, 3);

-- 员工数据
INSERT INTO employee (department_id, name, salary, id) VALUES (1, 'CEO', 30000, 1);
INSERT INTO employee (department_id, name, salary, id) VALUES (1, 'CTO', 30000, 2);
INSERT INTO employee (department_id, name, salary, id) VALUES (2, 'CEO', 30000, 3);

假设存在两个并发用户 Alice 和 Bob。Alice 执行了一条带有 HOLDLOCK 提示的 SELECT 语句，锁定所有属于第一个部门的员工记录：

SELECT * FROM employee WITH (HOLDLOCK) WHERE department_id = 1;

此时，Bob 尝试插入一条新员工记录，该记录不属于 Alice 已锁定的部门范围：

INSERT INTO employee WITH (NOWAIT) (department_id, name, salary, id) VALUES (3, 'Dave', 9000, 7);

然而，这条插入语句同样失败，返回“锁请求超时”错误。这引发了疑问：为什么 SQL Server 的谓词锁会超出其预期范围？

原因在于，如果没有适当的索引支持，SQL Server 在处理带 HOLDLOCK 的 SELECT 语句时，可能会执行全表扫描来确定哪些行需要锁定。这意味着整个表的所有行都被锁定，从而阻止了任何外部插入操作，即使这些插入操作涉及的数据不在已锁定的谓词范围内。

为了验证这一点，可以通过在 department_id 列上创建索引来优化查询性能：

CREATE INDEX idx_department_id ON employee (department_id);

再次运行相同的测试，可以看到 Bob 的插入操作成功执行，这是因为索引使得 SQL Server 可以更精确地锁定所需行，而不会影响到其他未被锁定的行。

总结来说，SQL Server 的谓词锁行为受到索引设计的显著影响。在缺乏适当索引的情况下，谓词锁可能过于广泛，导致不必要的锁定和并发性能下降。因此，在设计数据库表和索引时，应充分考虑并发控制的需求，以优化系统性能。