Ensuring Consistency under the Snapshot Isolation

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.

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

• Data consistency
• serialization
• snapshot.

[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.