"T-SQL Querying"书籍(http://www.amazon.com/Inside-Microsoft-Querying-Developer-Reference/dp/0735626030)有一个有趣的例子,在默认事务隔离级别下查询表时,在聚集索引键列更新期间,您可能会错过一行或读取一行两次。这似乎是可以接受的,因为更新表/实体键不是一个好主意。但是,我已经更新了这个例子,使其在更新非聚集索引键列值时也会发生相同的情况。
以下是表结构:
这是第一次连接需要完成的任务(在每次更新时,行将在2个索引页面之间跳转):
这是第二个连接需要完成的任务:
通常情况下,这个查询应该返回我们在第一个代码示例中插入的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/
以下是表结构:
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/