A Critique of ANSI SQL Isolation Level

From Microsoft, 1995

Summary

This paper discussed the definition of ANSI SQL(92) isolation level, and pointed out its ambiguity and failure to preclude some abnormality. The authors proposed a new set of definitions which could naturally map to the standard locking implementation of each level, and also discussed some additional isolation level, such as snapshot isolation.

The author first introduced the Dirty Read, Non-repeatable Rad and Phantom phenomena; and pointed out due to the ambiguity of their English statement, there could be a broad and a strict interpretation of each of the phenomena. They stated each interpretation in terms of execution history and defined different ANSI SQL Isolation level by how they preclude those phenomena.

The author then defined another set of isolation level in terms of different locking implementation. They discussed what kind of abnormality each of these isolation levels preclude, and noticed that their discrepancy with the ANSI isolation levels. They defined a new kind of phenomena, namely dirty write. A new set of isolation levels, defined by how they preclude those four phenomena (dirty write, dirty read, non-repeatable read and phantom) in the strict interpretation, are proposed. The author then showed that they are equivalent to their corresponding locking isolation levels.

The author discussed two additional kinds of isolation level: cursor stability and snapshot isolation. They clearly defined them by what abnormality they preclude, and showed that how they sit in the isolation level hierarchy.

Finally a summary has been given to show under the better definition, what kind of abnormality each isolation level precludes; and how they form an isolation level hierarchy.