Consistency in database systems refers to the requirement that any given database transaction must change affected data only in allowed ways. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
This does not guarantee correctness of the transaction in all ways the application programmer might have wanted (that is the responsibility of application-level code) but merely that any programming errors cannot result in the violation of any defined database constraints.
As an ACID guarantee
Consistency is one of the four guarantees that define ACID transactions; however, significant ambiguity exists about the nature of this guarantee. It is defined variously as:
- The guarantee that any transactions started in the future necessarily see the effects of other transactions committed in the past
- The guarantee that database constraints are not violated, particularly once a transaction commits
- The guarantee that operations in transactions are performed accurately, correctly, and with validity, with respect to application semantics
As these various definitions are not mutually exclusive, it is possible to design a system that guarantees “consistency” in every sense of the word, as most relational database management systems in common use today arguably do.
As a CAP trade-off
The CAP theorem is based on three trade-offs, one of which is “atomic consistency” (shortened to “consistency” for the acronym), about which the authors note, “Discussing atomic consistency is somewhat different than talking about an ACID database, as database consistency refers to transactions, while atomic consistency refers only to a property of a single request/response operation sequence. And it has a different meaning than the Atomic in ACID, as it subsumes the database notions of both Atomic and Consistent.”
- ^ J. Date, “SQL and Relational Theory: How to Write Accurate SQL Code 2nd edition”, O’reilly Media, Inc., 2012, pg. 180.
- ^ Jump up to:ab http://www.glassbeam.com/sites/all/themes/glassbeam/images/blog/10.1.1.67.6951.pdf “Brewer’s Conjecture and the Feasibility of Consistent, Available, Partition-Tolerant Web Services”
- ^Ports, D.R.K; Clements, A.T; Zhang, I; Madden, S; Liskov, B. “Transactional Consistency and Automatic Management in an Application Data Cache” (PDF). MIT CSAIL.
- ^Haerder, T; Reuter, A. (December 1983). “Principles of Transaction-Oriented Database Recovery” (PDF). Computing Surveys. 15 (4): 287–317.
- ^Mike Chapple. “The ACID Model”. About.
- ^“ACID properties”.
- ^Cory Janssen. “What is ACID in Databases? – Definition from Techopedia”. Techopedia.com.
- ^“ISO/IEC 10026-1:1998 – Information technology — Open Systems Interconnection — Distributed Transaction Processing — Part 1: OSI TP Model”.
Ofer Abarbanel is a 25 year securities lending broker and expert who has advised many Israeli regulators, among them the Israel Tax Authority, with respect to stock loans, repurchase agreements and credit derivatives. Founder of TBIL.co STATX Fund.