Halloween Problem In Database

Halloween Problem in Database

1、What’s the HalloweenProblem in database?

Form Wikipedia(http://en.wikipedia.org/wiki/Halloween_Problem)

In computing, the Halloween Problem refersto a phenomenon in databases in which an update operation causes a change inthe physical location of a row, potentially allowing the row to be visited morethan once during the operation. This could even cause an infinite loop in somecases where updates continually place the updated record ahead of the scanperforming the update operation.

The potential for this database error wasfirst discovered by Don Chamberlin, Pat Selinger, and Morton Astrahan in 1976,on Halloween day while working on a query that was supposed to give a tenpercent raise to every employee who earned less than $25,000. This query wouldrun successfully, with no errors, but when finished all the employees in thedatabase earned at least $25,000, because it kept giving them a raise untilthey reached that level. The expectation was that the query would iterate overeach of the employee records with a salary less than $25,000 precisely once. Infact, because even updated records were visible to the query execution engineand so continued to match the query's criteria, salary records were matchingmultiple times and each time being given a 10% raise until they were allgreater than $25,000.

2、Halloween简单描述

IBM的研究人员在雇员表上执行一个update操作，对salary<25000的员工增加10的工资。

SQL: update Employee set Salary=Salary*1.1where Salary<25000。

雇员表结构和数据如下图所示，

但他们当时的执行结果是：所有的员工的工资都大于等于25000。

我不认这是这是个problem，多发点工资不好么？！^.^。应该是HalloweenSurprise，哈哈

研究人员找出的原因是：执行update操作时optimizer使用salary上的索引，一些记录上的update操作执行1次或者多次，直到所有员工的工资大于等于25000。

上面的是表面原因，真正的原因呢？因为update操作更修数据之后，同时更新了salary上的索引，索引的顺也也发生了改变，这样导致已经更新过的行重启被读取，再次执行update操作。Halloween Problem因而也能引起更新操作无限循环执行。

如Smith 21000这条记录执行update操作之后，并更新salary上的索引之后的结果为

在Brown 22000执行完update操作之后，

 一般的认为这时候不再会执行update操作，但是由于Smith      23100;Brown 24200这两条记录会继续扫描到，update操作继续执行，直到Salary的值不满足查询条件。

The Halloween Problem – Part 1一文中对HalloweenProblem描述的很清楚，虽然使用的MS SQL Server进行举例说明的，该系列文章也还提到了Halloween Problem对INSERT, DELETE and MERGE queries操作的影响。(http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-1)

The Halloween Problem – Part 1文章中说：

The SQL language provides a way for usersto specify database changes using an UPDATE statement, but the syntax saysnothing about how the database engine should perform the changes. On the otherhand, the SQL standard does specify that the result of an UPDATE must be thesame as if it had been executed in three separate and non-overlapping phases:

1、A read-only search determines the records to be changed and the newcolumn values

2、Changes are applied to affected records

3、Database consistency constraints are verified

在实际的RDBMS系统实现过程中，为了数据库系统的执行效率和内存资源原因，并不能保证完全按照上面的步骤执行update操作，因而会出现Halloween Problem。

目前的数据库系统都解决Halloween Problem，解决的方法不尽相同。

http://www.benjaminnevarez.com/tag/halloween-protection/这篇文章讲到SQLServer如何解决Hallloween Problem问题，主要方法是把读和写相互隔离起来，在写之前就完成了所有读操作。

参考：

The　HalloweenProblem Part 1：http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-1

TheHalloween Problem – Part 2：http://www.sqlperformance.com/2013/02/t-sql-queries/halloween-problem-part-2

TheHalloween Problem – Part 3：http://www.sqlperformance.com/2013/02/sql-plan/halloween-problem-part-3

TheHalloween Problem – Part 4：http://www.sqlperformance.com/2013/02/sql-plan/halloween-problem-part-4

HalloweenProtection：http://www.benjaminnevarez.com/tag/halloween-protection/

SQL　Performance：http://www.sqlperformance.com/

最后

以上就是怡然指甲油为你收集整理的Halloween Problem In Database的全部内容，希望文章能够帮你解决Halloween Problem In Database所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。