Automating the Detection of Snapshot Isolation Anomalies

Abstract

Snapshot isolation (SI) provides significantly improved co ncurrency over 2PL, allowing reads to be non-blocking. Unfortunately, it can also lead to non-serializable executions in general. Despi te this, it is widely used, supported in many commercial databases, and is in fact the highest available level of consistency in Oracle an d Post- greSQL. Sufficient conditions for detecting whether SI anom alies could occur in a given set of transactions were presented rec ently, and extended to necessary conditions for transactions with out pred- icate reads. In this paper we address several issues in extending the earl ier theory to practical detection/correction of anomalies. We first show how to mechanically find a set of programs which is large enoug h so that we ensure that all executions will be free of SI anomalies, by modifying these programs appropriately. We then address the problem of false positives, i.e., transaction programs wro ngly iden- tified as possibly leading to anomalies, and present techniq ues that can significantly reduce such false positives. Unlike earli er work, our techniques are designed to be automated, rather than manually carried out. We describe a tool which we are developing to carry out this task. The tool operates on descriptions of the progr ams either taken from the application code itself, or taken from SQL query traces. It can be used with any database system. We have used our tool on two real world applications in production use at IIT Bombay, and detected several anomalies, some of which have caused real world problems. We believe such a tool will be invalu- able for ensuring safe execution of the large number of appli cations which are already running under SI.