有些问题,真的是你不知道自己不知道这回事,比如下面这例:
在40年前的一个晚上,1970年左右,IBM 的一群研究员决定给不满25000美金年薪的雇员,增加10% 的薪水。
他们写了一段 SQL,大意是这样的:
update Employee
Set Salary = Salary * (1 + 10%)
where Salary < 25000
结果等他们运行完毕,发现所有的年薪不满 25000 美金的雇员,他们的薪水统统加到了 25000.
例如,原本是 15000薪水的雇员和 8000 美金年薪的雇员,他们的薪水更新完了之后,都到了25000 美金。 这一天正好是 10月31日,Halloween Day. 所以被称为 Halloween Problem.
下面就举个例子来说此问题
CREATE TABLE T (PK INT, A INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)
CREATE INDEX TA ON T(A)
INSERT T VALUES (1, 1)
INSERT T VALUES (2, 2)
INSERT T VALUES (3, 3)
当我们用以下脚本来更新这三条数据的时候,理论上是完成一次更新,即每条数据的 A 值加10,而实际上,却不是
UPDATE T SET A = A + 10 from T with(index(TA))
DECLARE @PK INT
DECLARE C CURSOR DYNAMIC SCROLL_LOCKS FOR SELECT PK FROM T
WITH(INDEX(TA))OPEN C
WHILE 0=0
BEGIN
FETCH NEXT FROM C INTO @PK IF @@FETCH_STATUS <> 0 BREAK UPDATE T SET A = A + 10 FROM T WHERE PK = @PK
END
CLOSE C
DEALLOCATE C
运行完这段脚本,大家会发现,脚本是个死循环!
是不是开始怀疑人生了,这么简单的问题,难道是自己连 cursor 的用法都不会了么?
如何编写正确的脚本呢
DECLARE @PK INT
DECLARE C CURSOR DYNAMIC SCROLL_LOCKS FOR SELECT PK FROM T WITH(INDEX(TPK))
OPEN C
WHILE 0=0
BEGIN
FETCH NEXT FROM C INTO @PK
IF @@FETCH_STATUS <> 0
BREAK
UPDATE T SET A = A + 10 FROM T WHERE PK = @PK
END
CLOSE C
DEALLOCATE C
很奇怪,是不是?
至少这里引出 2 个问题:
1 使用 cursor 会有意想不到的异常出现,这大概就是为什么很多前辈告诉我们,不要用 cursor 的 一个原因;
2 Update 的本质。Update 操作执行的时候,通常会有两种策略,一是直接替换原本的数据,二是先 delete 再 insert.
在我们的场景里,恰恰是因为采用了第二种策略,先将原本的数据给 delete , 再 Insert 了一条新的数据进来,原本的clustered table 此时将新 row 的物理位置传给了 index (TA), 导致以 Index 为查找条件的 cursor ,循环的将已经更改的数据,再次挑选出来做更新。
更详细的解释,可以参考《Microsoft SQL Server 2008 Internals 》的讲解。
这本书中有很多知名人物,比如 Adam Machanic, 他写了著名的 5000 行 T-SQL 脚本,用来监控 SQL 服务器状态; SQLQueryStress 测试工具也是出自他之手。
如果想对 SQL Server 做深入的理解,这本书不可不看