SQL Server 索引关键列更新后缺失行

Question

SQL Server 索引关键列更新后缺失行

3

"T-SQL Querying"书籍(http://www.amazon.com/Inside-Microsoft-Querying-Developer-Reference/dp/0735626030)有一个有趣的例子，在默认事务隔离级别下查询表时，在聚集索引键列更新期间，您可能会错过一行或读取一行两次。这似乎是可以接受的，因为更新表/实体键不是一个好主意。但是，我已经更新了这个例子，使其在更新非聚集索引键列值时也会发生相同的情况。

以下是表结构:

SET NOCOUNT ON;
USE master;
IF DB_ID('TestIndexColUpdate') IS NULL CREATE DATABASE TestIndexColUpdate;
GO
USE TestIndexColUpdate;
GO

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL DROP TABLE dbo.Employees;
CREATE TABLE dbo.Employees
(
empid CHAR(900) NOT NULL, -- this column should be big enough, so that 9 rows fit on 2 index pages
salary MONEY NOT NULL,
filler CHAR(1) NOT NULL DEFAULT('a')
);
CREATE INDEX idx_salary ON dbo.Employees(salary) include (empid); -- include empid into index, so that test query reads from it
ALTER TABLE dbo.Employees ADD CONSTRAINT PK_Employees PRIMARY KEY NONCLUSTERED(empid);

INSERT INTO dbo.Employees(empid, salary) VALUES
('A', 1500.00),('B', 2000.00),('C', 3000.00),('D', 4000.00),
('E', 5000.00),('F', 6000.00),('G', 7000.00),('H', 8000.00),
('I', 9000.00);

这是第一次连接需要完成的任务（在每次更新时，行将在2个索引页面之间跳转）：

SET NOCOUNT ON;
USE TestIndexColUpdate;

WHILE 1=1
BEGIN
    UPDATE dbo.Employees SET salary = 10800.00 - salary WHERE empid = 'I'; -- on each update, "I" employee jumps between 2 pages
END

这是第二个连接需要完成的任务：

SET NOCOUNT ON;
USE TestIndexColUpdate;

DECLARE @c INT
WHILE 1 = 1
BEGIN
    SELECT salary, empid FROM dbo.Employees
    if @@ROWCOUNT <> 9 BREAK;
END

通常情况下，这个查询应该返回我们在第一个代码示例中插入的9条记录。然而很快，我看到只返回了8条记录。这个查询从“idx_salary”索引中读取所有数据，该索引正在被前面的示例代码更新。这似乎是SQL Server对数据一致性相当宽松的态度。我会期望在从索引中读取数据时，当其键列正在被更新时进行一些锁定协调。

我是否正确理解了这种行为？这是否意味着即使非聚集索引键也不应该被更新？

更新：要解决这个问题，您只需要在数据库上启用“快照”（READ_COMMITTED_SNAPSHOT ON）。不再有死锁或丢失的行。我试图在这里总结所有这些: http://blog.konstantins.net/2015/01/missing-rows-after-updating-sql-server.html 更新2：这似乎是与这篇古老文章中相同的问题：http://blog.codinghorror.com/deadlocked/

- Konstantin

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- usr · Accepted Answer

我的理解是正确的吗？

是的。

这是否意味着，即使是非聚集索引键也不应该被更新？

不是。您应该使用适当的隔离级别或使应用程序容忍READ COMMITTED允许的不一致性。

缺失行的问题不仅局限于聚集索引。它是通过将B树中的一行移动而导致的。聚集索引和非聚集索引都是作为B树实现的，它们之间只有微小的物理差异。

因此，您看到的是完全相同的物理现象。它适用于每次查询从B树中读取一系列行时。该范围的内容可能会移动。

使用提供您所需保证的隔离级别。对于只读事务，快照隔离级别通常是并发的非常优雅和完全的解决方案。它似乎适用于您的情况。

这似乎是SQL Server对数据一致性的相当放松的态度。我希望在从索引读取数据时，其键列正在更新时进行一些锁定协调。

这是可以理解的请求。另一方面，您明确请求了低隔离级别。如果您想的话，可以将其调至SERIALIZABLE。 SERIALIZABLE会呈现出与串行执行完全相同的结果。

缺失行只是READ COMMITTED允许的许多效果中的一个特殊情况。在允许各种其他不一致性的同时，专门防止它们毫无意义。

SET NOCOUNT ON;
USE TestIndexColUpdate;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @c INT
WHILE 1 = 1
BEGIN
    DECLARE @count INT
    SELECT @count = COUNT(*) FROM dbo.Employees WITH (INDEX (idx_salary))
    WHERE empid > '' AND CONVERT(NVARCHAR(MAX), empid) > '__'
        AND salary > 0
    if @count <> 9 BREAK;
END