Making snapshot isolation serializable

Alan Fekete, Dimitrios Liarokapis, Elizabeth O'Neil, Patrick O'Neil, Dennis Shasha

Research output: Contribution to journalArticle

Abstract

Snapshot Isolation (SI) is a multiversion concurrency control algorithm, first described in Berenson et al. [1995]. SI is attractive because it provides an isolation level that avoids many of the common concurrency anomalies, and has been implemented by Oracle and Microsoft SQL Server (with certain minor variations). SI does not guarantee serializability in all cases, but the TPC-C benchmark application [TPC-C], for example, executes under SI without serialization anomalies. All major database system products are delivered with default nonserializable isolation levels, often ones that encounter serialization anomalies more commonly than SI, and we suspect that numerous isolation errors occur each day at many large sites because of this, leading to corrupt data sometimes noted in data warehouse applications. The classical justification for lower isolation levels is that applications can be run under such levels to improve efficiency when they can be shown not to result in serious errors, but little or no guidance has been offered to application programmers and DBAs by vendors as to how to avoid such errors. This article develops a theory that characterizes when nonserializable executions of applications can occur under SI. Near the end of the article, we apply this theory to demonstrate that the TPC-C benchmark application has no serialization anomalies under SI, and then discuss how this demonstration can be generalized to other applications. We also present a discussion on how to modify the program logic of applications that are nonserializable under SI so that serializability will be guaranteed.

Original languageEnglish (US)
Pages (from-to)492-528
Number of pages37
JournalACM Transactions on Database Systems
Volume30
Issue number2
DOIs
StatePublished - Jun 2005

Fingerprint

Concurrency control
Data warehouses
Demonstrations
Servers

Keywords

  • Anomaly
  • Concurrency control
  • Consistency
  • Multiversion concurrency
  • Serializability
  • Snapshot isolation
  • Weak isolation

ASJC Scopus subject areas

  • Information Systems
  • Computer Graphics and Computer-Aided Design
  • Software

Cite this

Making snapshot isolation serializable. / Fekete, Alan; Liarokapis, Dimitrios; O'Neil, Elizabeth; O'Neil, Patrick; Shasha, Dennis.

In: ACM Transactions on Database Systems, Vol. 30, No. 2, 06.2005, p. 492-528.

Research output: Contribution to journalArticle

Fekete, A, Liarokapis, D, O'Neil, E, O'Neil, P & Shasha, D 2005, 'Making snapshot isolation serializable', ACM Transactions on Database Systems, vol. 30, no. 2, pp. 492-528. https://doi.org/10.1145/1071610.1071615
Fekete, Alan ; Liarokapis, Dimitrios ; O'Neil, Elizabeth ; O'Neil, Patrick ; Shasha, Dennis. / Making snapshot isolation serializable. In: ACM Transactions on Database Systems. 2005 ; Vol. 30, No. 2. pp. 492-528.
@article{a48f4b0804f140919b51abe0ce1eac40,
title = "Making snapshot isolation serializable",
abstract = "Snapshot Isolation (SI) is a multiversion concurrency control algorithm, first described in Berenson et al. [1995]. SI is attractive because it provides an isolation level that avoids many of the common concurrency anomalies, and has been implemented by Oracle and Microsoft SQL Server (with certain minor variations). SI does not guarantee serializability in all cases, but the TPC-C benchmark application [TPC-C], for example, executes under SI without serialization anomalies. All major database system products are delivered with default nonserializable isolation levels, often ones that encounter serialization anomalies more commonly than SI, and we suspect that numerous isolation errors occur each day at many large sites because of this, leading to corrupt data sometimes noted in data warehouse applications. The classical justification for lower isolation levels is that applications can be run under such levels to improve efficiency when they can be shown not to result in serious errors, but little or no guidance has been offered to application programmers and DBAs by vendors as to how to avoid such errors. This article develops a theory that characterizes when nonserializable executions of applications can occur under SI. Near the end of the article, we apply this theory to demonstrate that the TPC-C benchmark application has no serialization anomalies under SI, and then discuss how this demonstration can be generalized to other applications. We also present a discussion on how to modify the program logic of applications that are nonserializable under SI so that serializability will be guaranteed.",
keywords = "Anomaly, Concurrency control, Consistency, Multiversion concurrency, Serializability, Snapshot isolation, Weak isolation",
author = "Alan Fekete and Dimitrios Liarokapis and Elizabeth O'Neil and Patrick O'Neil and Dennis Shasha",
year = "2005",
month = "6",
doi = "10.1145/1071610.1071615",
language = "English (US)",
volume = "30",
pages = "492--528",
journal = "ACM Transactions on Database Systems",
issn = "0362-5915",
publisher = "Association for Computing Machinery (ACM)",
number = "2",

}

TY - JOUR

T1 - Making snapshot isolation serializable

AU - Fekete, Alan

AU - Liarokapis, Dimitrios

AU - O'Neil, Elizabeth

AU - O'Neil, Patrick

AU - Shasha, Dennis

PY - 2005/6

Y1 - 2005/6

N2 - Snapshot Isolation (SI) is a multiversion concurrency control algorithm, first described in Berenson et al. [1995]. SI is attractive because it provides an isolation level that avoids many of the common concurrency anomalies, and has been implemented by Oracle and Microsoft SQL Server (with certain minor variations). SI does not guarantee serializability in all cases, but the TPC-C benchmark application [TPC-C], for example, executes under SI without serialization anomalies. All major database system products are delivered with default nonserializable isolation levels, often ones that encounter serialization anomalies more commonly than SI, and we suspect that numerous isolation errors occur each day at many large sites because of this, leading to corrupt data sometimes noted in data warehouse applications. The classical justification for lower isolation levels is that applications can be run under such levels to improve efficiency when they can be shown not to result in serious errors, but little or no guidance has been offered to application programmers and DBAs by vendors as to how to avoid such errors. This article develops a theory that characterizes when nonserializable executions of applications can occur under SI. Near the end of the article, we apply this theory to demonstrate that the TPC-C benchmark application has no serialization anomalies under SI, and then discuss how this demonstration can be generalized to other applications. We also present a discussion on how to modify the program logic of applications that are nonserializable under SI so that serializability will be guaranteed.

AB - Snapshot Isolation (SI) is a multiversion concurrency control algorithm, first described in Berenson et al. [1995]. SI is attractive because it provides an isolation level that avoids many of the common concurrency anomalies, and has been implemented by Oracle and Microsoft SQL Server (with certain minor variations). SI does not guarantee serializability in all cases, but the TPC-C benchmark application [TPC-C], for example, executes under SI without serialization anomalies. All major database system products are delivered with default nonserializable isolation levels, often ones that encounter serialization anomalies more commonly than SI, and we suspect that numerous isolation errors occur each day at many large sites because of this, leading to corrupt data sometimes noted in data warehouse applications. The classical justification for lower isolation levels is that applications can be run under such levels to improve efficiency when they can be shown not to result in serious errors, but little or no guidance has been offered to application programmers and DBAs by vendors as to how to avoid such errors. This article develops a theory that characterizes when nonserializable executions of applications can occur under SI. Near the end of the article, we apply this theory to demonstrate that the TPC-C benchmark application has no serialization anomalies under SI, and then discuss how this demonstration can be generalized to other applications. We also present a discussion on how to modify the program logic of applications that are nonserializable under SI so that serializability will be guaranteed.

KW - Anomaly

KW - Concurrency control

KW - Consistency

KW - Multiversion concurrency

KW - Serializability

KW - Snapshot isolation

KW - Weak isolation

UR - http://www.scopus.com/inward/record.url?scp=23944513290&partnerID=8YFLogxK

UR - http://www.scopus.com/inward/citedby.url?scp=23944513290&partnerID=8YFLogxK

U2 - 10.1145/1071610.1071615

DO - 10.1145/1071610.1071615

M3 - Article

AN - SCOPUS:23944513290

VL - 30

SP - 492

EP - 528

JO - ACM Transactions on Database Systems

JF - ACM Transactions on Database Systems

SN - 0362-5915

IS - 2

ER -