Abstract
A method for detecting potential violations of integrity constraints of concurrent transactions running under snapshot isolation (SI) is presented. Although SI provides a high level of isolation, it does not, by itself, ensure that all integrity constraints are satisfied. In particular, while current implementations of SI enforce all internal integrity constraints, in particular key constraints, they fail to enforce constraints implemented via triggers. One remedy is to turn to serializable SI (SSI), in which full serializability is guaranteed. However, SSI comes at the price of either a substantial number of false positives, or else a high cost of constructing the full direct serialization graph. In this work, a compromise approach, called constraint-preserving snapshot isolation (CPSI), is developed, which while not guaranteeing full serializability, does guarantee that all constraints, including those enforced via triggers, are satisfied. In contrast to full SSI, CPSI requires testing concurrent transactions for conflict only pairwise, and thus involves substantially less overhead while providing a foundation for resolving conflicts via negotiation rather than via abort and restart. As is the case with SSI, CPSI can result in false positives. To address this, a hybrid approach is also developed which combines CPSI with a special version of SSI called CSSI, resulting in substantially fewer false positives than would occur using either approach alone.
This is a preview of subscription content, log in via an institution to check access.
Similar content being viewed by others
References
Adya, A., Liskov, B., O’Neil, P.E.: Generalized isolation level definitions. In: Lomet, D.B., Weikum, G. (eds.) Proceedings of the 16th International Conference on Data Engineering, San Diego, California, USA, February 28 - March 3, 2000, pp 67–78 (2000)
Berenson, H., Bernstein, P.A., Gray, J., Melton, J., O’Neil, E.J., O’Neil, P.E.: A critique of ANSI SQL isolation levels. In: Proceedings of the 1995 ACM SIGMOD International Conference on Management of Data, San Jose, California, May 22-25, 1995, pp 1–10 (1995)
Bernstein, P., Newcomer, E.: Principles of transaction processing, 2nd edn. Morgan Kaufmann (2009)
Breitbart, Y., Georgakopoulos, D., Rusinkiewicz, M., Silberschatz, A.: On rigorous transaction scheduling. IEEE Trans. Software Eng. 17(9), 954–960 (1991)
Cahill, M.J., Röhm, U., Fekete, A.D.: Serializable isolation for snapshot databases. ACM Trans. Database Syst. 34(4) (2009)
Date, C.J.: A guide to the SQL standard. Addison-Wesley (1997). (with Hugh Darwen)
Elmasri, R., Navathe, S.B.: Fundamentals of database systems, 6th edn. Addison Wesley (2011)
Eswaran, K.P., Gray, J., Lorie, R.A., Traiger, I.L.: The notions of consistency and predicate locks in a database system. Comm. ACM 19(11), 624–633 (1976)
Fekete, A., Liarokapis, D., O’Neil, E.J., O’Neil, P.E., Shasha, D.: Making snapshot isolation serializable. ACM Trans. Database Syst. 30(2), 492–528 (2005)
Härder, T., Reuter, A.: Principles of transaction-oriented database recovery. ACM Comput. Surv. 15(4), 287–317 (1983)
Hegner, S.J.: A model of independence and overlap for transactions on database schemata. In: Catania, B., Ivanović, M., Thalheim, B. (eds.) Advances in Databases and Information Systems, 14th East European Conference, ADBIS 2010, Novi Sad, Serbia, September 20-24, 2010, Proceedings, Lecture Notes in Computer Science, vol. 6295, pp 209–223. Springer (2010)
Hegner, S.J.: A simple model of negotiation for cooperative updates on database schema components (2011)
Hegner, S.J.: Guard independence and constraint-preserving snapshot isolation. In: Bierle, C., Meghini, C. (eds.) Foundations of Information and Knowledge Systems: Eighth International Symposium, FoIKS 2014, Bordeaux, France, March 3-7, 2014, Proceedings, Lecture Notes in Computer Science, vol. 8367, pp 231–250. Springer (2014)
Hegner, S.J., Schmidt, P.: Update support for database views via cooperation. In: Ioannis, Y., Novikov, B., Rachev, B. (eds.) Advances in Databases and Information Systems, 11th East European Conference, ADBIS 2007, Varna, Bulgaria, September 29 - October 3, 2007, Proceedings, Lecture Notes in Computer Science, vol. 4690, pp 98–113. Springer (2007)
Kifer, M., Bernstein, A.: Lewis, P.M., 2nd edn. Addison-Wesley, Database systems: An application-oriented approach (2006)
Kung, H.T., Robinson, J.T.: On optimistic methods for concurrency control. ACM Trans. Database Systems 6(2), 213–226 (1981)
Lin, Y., Kemme, B., Jiménez-Peris, R., Patiño-Martínez, M., Armendáriz-Iñigo, J.E.: Snapshot isolation and integrity constraints in replicated databases. ACM Trans. Database Systems 34(2) (2009)
Papadimitriou, C.: The theory of database concurrency control. Computer Science Press (1986)
Ports, D.R.K., Grittner, K.: Serializable snapshot isolation in PostgreSQL. Proc. VLDB Endowment 5(12), 1850–1861 (2012)
Revilak, S., O’Neil, P.E., O’Neil, E.J.: Precisely serializable snapshot isolation (PSSI). In: Proceedings of the 27th International Conference on Data Engineering, ICDE 2011, April 11-16, 2011, Hannover, Germany, pp 482–493 (2011)
Rosenkrantz, D.J., Stearns, R.E., Lewis II, P.M.: Consistency and serializability in concurrent database systems. SIAM J. Comput. 13(3), 508–530 (1984)
Silberschatz, A., Korth, H.F., Sudarshan, S.: Database system concepts, 6th edn. McGraw Hill (2011)
Weikum, G., Vossen, G.: Transactional information systems. Morgan Kaufmann (2002)
Author information
Authors and Affiliations
Corresponding author
Rights and permissions
About this article
Cite this article
Hegner, S.J. Constraint-preserving snapshot isolation. Ann Math Artif Intell 76, 281–326 (2016). https://doi.org/10.1007/s10472-015-9482-6
Published:
Issue Date:
DOI: https://doi.org/10.1007/s10472-015-9482-6