主页 / dba / 问题 / 187029
Accepted
Heinzi
Asked: 2017-09-28 01:40:49 +0800 CST

“不相关”的 INSERT 和 UPDATE 相互阻塞

  • 772

重现场景：

CREATE TABLE test (
  ID int IDENTITY(1,1),
  mykey nvarchar(255) NOT NULL,
  exp_date datetime,
PRIMARY KEY (ID));
GO

CREATE INDEX not_expired_keys ON test (exp_date, mykey);

INSERT INTO test (mykey, exp_date) VALUES ('A', NULL);

我开始交易1：

-- add key B
BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);
...

然后并行执行事务 2：

-- expire key A
BEGIN TRANSACTION;
UPDATE test SET exp_date = GETDATE() WHERE exp_date IS NULL AND mykey = 'A'; -- <-- Blocking
ROLLBACK;

事实证明，事务 1 的未提交 INSERT 会阻止事务 2 的 UPDATE，即使它们影响不相交的行集（mykey = 'B'vs. mykey = 'A'）。

观察：

  • 阻塞也发生在最低事务隔离级别上READ UNCOMMITTED。
  • 如果我将唯一索引放在mykey. 不幸的是，我不能这样做，因为一旦密钥过期，密钥名称就可以重复使用。

我的问题：

  • （出于好奇：）为什么这些语句即使在READ UNCOMMITTED级别上也会相互阻止？

  • 有没有一种简单可靠的方法可以让它们不互相阻挡？

sql-server locking

2 个回答

  1. Best Answer

    让我们看一下执行计划。

    第一个查询 - 插入

    BEGIN TRANSACTION;
INSERT INTO test (mykey, exp_date) VALUES ('B', NULL);

    及其执行计划在此处输入图像描述

    我们看到 sql server 正在做 Clustered Index Insert 操作。

    现在让我们看看更新

    BEGIN TRANSACTION;
UPDATE test 
SET exp_date = GETDATE() 
WHERE exp_date IS NULL AND mykey = 'A' -- <-- Blocking

    及其执行计划在此处输入图像描述

    SQL Server 会扫描表的聚集索引，并在其上加 U 锁，即使它可以选择另一个索引来查找匹配的行。原因是，因为我们在表中只有 1 行，并且 SQL Server Optimizer 发现更容易扫描聚集索引而不是在非聚集索引中搜索数据。

    但是如果我们强制 sql server 使用非聚集索引呢？

    BEGIN TRANSACTION;
UPDATE test 
SET exp_date = GETDATE() 
FROM test WITH(INDEX = not_expired_keys)
WHERE exp_date IS NULL AND mykey = 'A' -- <-- No Blocking!!!

    及其执行计划在此处输入图像描述

    我想如果我们在表中放更多的行，SQL Server 会选择非聚集索引来查找必须更新的行，并且不会出现阻塞。

    • 4

  2. 这本质上是 SQL Server 中的一个错误。MS 没有实现谓词锁，而是采用了一种廉价的方法来阻止用于从表中选择行的索引之一上的范围。如果它不使用索引（或者如果没有索引），它将改为阻止表上的范围。

    是的，强制索引会导致它锁定索引；但是它总是需要页面锁。页面锁不利于锁层次结构；碰巧在同一索引页或表页上的不相关行将被阻止。有两个书面陈述没有办法解决这个问题。如果您的其中一个语句正在阅读，WITH(ROWLOCK)则阅读器将绕过。

    我与 MS 开了一个案例，持续了两个月，然后他们完全拒绝修复它。

    • -2

相关问题