Ensuring Consistency under the Snapshot Isolation

Authors: Carlos Roberto Valˆencio, F´abio Renato de Almeida, Thatiane Kawabata, Leandro Alves Neves, Julio Cesar Momente, Mario Luiz Tronco, Angelo Cesar Colombini

Abstract:

By running transactions under the SNAPSHOT isolation we can achieve a good level of concurrency, specially in databases with high-intensive read workloads. However, SNAPSHOT is not immune to all the problems that arise from competing transactions and therefore no serialization warranty exists. We propose in this paper a technique to obtain data consistency with SNAPSHOT by using some special triggers that we named DAEMON TRIGGERS. Besides keeping the benefits of the SNAPSHOT isolation, the technique is specially useful for those database systems that do not have an isolation level that ensures serializability, like Firebird and Oracle. We describe all the anomalies that might arise when using the SNAPSHOT isolation and show how to preclude them with DAEMON TRIGGERS. Based on the methodology presented here, it is also proposed the creation of a new isolation level: DAEMON SNAPSHOT.

Keywords: Data consistency, serialization, snapshot.

Digital Object Identifier (DOI): doi.org/10.5281/zenodo.1337285

References:

[1] Ansi, "Ansi x3.135-1992, american national standard for information systems — database language — sql,” American National Standards Institute, Tech. Rep., 1992.
[2] M. Yabandeh and D. G´omez Ferro, "A critique of snapshot isolation,” in Proceedings of the 7th ACM european conference on Computer Systems. ACM, 2012, pp. 155–168.
[3] H. Berenson, P. Bernstein, J. Gray, J. Melton, E. O’Neil, and P. O’Neil, "A critique of ansi sql isolation levels,” ACM SIGMOD Record, vol. 24, no. 2, pp. 1–10, 1995.
[4] A. Fekete, D. Liarokapis, E. O’Neil, P. O’Neil, and D. Shasha, "Making snapshot isolation serializable,” ACM Transactions on Database Systems (TODS), vol. 30, no. 2, pp. 492–528, 2005.
[5] A. Adya, B. Liskov, and P. O’Neil, "Generalized isolation level definitions,” in Data Engineering, 2000. Proceedings. 16th International Conference on. IEEE, 2000, pp. 67–78.
[6] A. Fekete, E. O’Neil, and P. O’Neil, "A read-only transaction anomaly under snapshot isolation,” ACM SIGMOD Record, vol. 33, no. 3, pp. 12–14, 2004.
[7] S. A. Revilak, "Precisely serializable snapshot isolation,” Ph.D. dissertation, Office of Graduate Studies, University of Massachusetts Boston, 2011.
[8] M. Alomari, M. Cahill, A. Fekete, and U. Rohm, "The cost of serializability on platforms that use snapshot isolation,” in Data Engineering, 2008. ICDE 2008. IEEE 24th International Conference on. IEEE, 2008, pp. 576–585.
[9] A. Adya, "Weak consistency: a generalized theory and optimistic implementations for distributed transactions,” Ph.D. dissertation, Massachusetts Institute of Technology, 1999.
[10] M. Alomari, "Ensuring serializable executions with snapshot isolation dbms,” Ph.D. dissertation, University of Sydney, 2008.
[11] M. Alomari, A. Fekete, and U. Rohm, "A robust technique to ensure serializable executions with snapshot isolation dbms,” in Data Engineering, 2009. ICDE’09. IEEE 25th International Conference on. IEEE, 2009, pp. 341–352.
[12] M. J. Cahill, U. R¨ohm, and A. D. Fekete, "Serializable isolation for snapshot databases,” ACM Trans. Database Syst., vol. 34, no. 4, pp. 20:1–20:42, Dec. 2009. (Online). Available: http: //doi.acm.org/10.1145/1620585.1620587
[13] M. J. Cahill, "Serializable isolation for snapshot databases,” Ph.D. dissertation, The University of Sydney, 2009.
[14] M. J. Cahill, U. R¨ohm, and A. D. Fekete, "Serializable isolation for snapshot databases,” in Proceedings of the 2008 ACM SIGMOD international conference on Management of data, ser. SIGMOD ’08. New York, NY, USA: ACM, 2008, pp. 729–738. (Online). Available: http://doi.acm.org/10.1145/1376616.1376690
[15] S. Revilak, P. O’Neil, and E. O’Neil, "Precisely serializable snapshot isolation (pssi),” in Data Engineering (ICDE), 2011 IEEE 27th International Conference on. IEEE, 2011, pp. 482–493.