Testing isolation levels of relational database management systems

This thesis studies approaches for testing and understanding the implementation of Isolation Levels by Relational Database Management Systems. Isolation is the property of these systems that ensures that concurrent transactions do not interfere. The Isolation Levels feature has been developed to improve performance when full correctness is not desirable or when correctness could be assured at the application level. The definitions of Isolation Levels, below the serializable level, are evolving and there are not yet well-accepted general definitions. A tool and methodology for testing whether a system behaves according to some definition, or for understanding the exact behavior of a given system, is very important because very critical applications use this technology, and problems in implementations or misuse of Isolation Levels could have very adverse side-effects (e.g. money being lost, collisions in reservations, bad estimates etc). The tool we have developed processes specifications of concurrency scenarios, called input histories and produces output histories at various database Isolation Levels. The input histories are made up of a series of generically specified transactional operations for several concurrent transactions. By analyzing the results in the output history, it can be determined whether the execution was correct under a given isolation level or levels. We introduce two methodologies: comparative testing and gray box testing and we focus on gray-box testing of database systems that are known to use single-version concurrency control algorithms based on preventing concurrent execution of conflicting operations. This is usually achieved by locking. We prove a theorem showing that for testing these types of schedulers it is adequate to test whether each isolation level proscribes the execution of certain pairs of conflicting operations. We have executed histories including all different types of conflicting pairs of operations. Among interesting results, we have detected that the isolation level of a particular version of a database system that corresponds to the READ COMMITTED ANSI SQL would allow execution of a certain type of conflicting operations (write/predicate-read) that should be proscribed. The results we have generated demonstrate the utility of our methodology.