How do ACID and database transactions work?

Question

What is the relationship between ACID and database transaction?

Does ACID give database transaction or is it the same thing?

Could someone enlighten this topic.

Conflux

Answer 1

ACID is a set of properties that you would like to apply when modifying a database.

• Atomicity
• Consistency
• Isolation
• Durability

A transaction is a set of related changes which is used to achieve some of the ACID properties. Transactions are tools to achieve the ACID properties.

Atomicity means that you can guarantee that all of a transaction happens, or none of it does; you can do complex operations as one single unit, all or nothing, and a crash, power failure, error, or anything else won't allow you to be in a state in which only some of the related changes have happened.

Consistency means that you guarantee that your data will be consistent; none of the constraints you have on related data will ever be violated.

Isolation means that one transaction cannot read data from another transaction that is not yet completed. If two transactions are executing concurrently, each one will see the world as if they were executing sequentially, and if one needs to read data that is written by another, it will have to wait until the other is finished.

Durability means that once a transaction is complete, it is guaranteed that all of the changes have been recorded to a durable medium (such as a hard disk), and the fact that the transaction has been completed is likewise recorded.

So, transactions are a mechanism for guaranteeing these properties; they are a way of grouping related actions together such that as a whole, a group of operations can be atomic, produce consistent results, be isolated from other operations, and be durably recorded.

Praxis

You say "Consistency means that you guarantee that your data will be consistent; none of the constraints you have on related data will ever be violated." If by constraints, you mean those defined in the database (e.g., check constraints), what does transaction management have to do with preventing them from being violated? Aren't they always in force? It seems to me that the C in ACID must have another meaning. – Bumbling

@MarcRochkind A transaction allows you to apply consistency checks even if individual operations within the transaction would violate the consistency constraints. For example, if you're updating a double-entry bookkeeping system, you may need to debit from several accounts and credit to several accounts at the same time. Without transactions, you would check the constraints after every statement, and would fail because the individual statements don't preserve consistency. With transactions, although individual statements don't preserve consistency, the transaction as a whole does. – Praxis

@BrianCampbell after reading the last para of your answer and Wiki's first para I find wiki has just opposite perspective than yours i.e. ACID properties guarantee reliable transaction and not other way round. – Selma

"and if one needs to read data that is written by another, it will have to wait until the other is finished" - is not entirely true. The first transaction can (and will) read that data and will see the state as it was before the second transaction started. – Halona

@BrianCampbell 1) Consistency is both "check constraints" like UNIQUE, FOREIGN KEY as well as valid state (according to the business logic / requirement of the application) at the the end of the transaction? 2) Doesn't Atomicity and or Isolation alone ensure Consistency? Should we mention Consistency explicitly as a required property? – Trover

@a_horse_with_no_name you're talking about OCC (Optimistic concurrency control) v/s PCC (Pessimistic). Both can be implemented in an ACID-compliant database system, depends on if you're going for consistency or high availability. – Dante

Answer 2

ACID are desirable properties of any transaction processing engine.

A DBMS is (if it is any good) a particular kind of transaction processing engine that exposes, usually to a very large extent but not quite entirely, those properties.

But other engines exist that can also expose those properties. The kind of software that used to be called "TP monitors" being a case in point (nowadays' equivalent mostly being web servers).

Such TP monitors can access resources other than a DBMS (e.g. a printer), and still guarantee ACID toward their users. As an example of what ACID might mean when a printer is involved in a transaction:

• Atomicity: an entire document gets printed or nothing at all
• Consistency: at end-of-transaction, the paper feed is positioned at top-of-page
• Isolation: no two documents get mixed up while printing
• Durability: the printer can guarantee that it was not "printing" with empty cartridges.

Belle

Good example. I don't understand the "Consistency" part though. In my mind, a better example is "The printer does not accept paper larger than 10 inches." – Malvasia

EDIT - "paper wider than 10 inches." – Malvasia

I admit that one is a bit of a stretch. But it's hard to find better examples because "consistency" is about data, and a printer isn't exactly a data device. – Belle

document starts printing, paper runs out - atomicity fails – Pasadena

Well then the printer has a facility that keeps it from starting to print if the page count of a document is higher than the sheet count currently known to be in the paper supply. You see that's the trouble with many people who comment here. You mention "printer" and they see only that dumb serial streaming device they have on their home desk, not the high-grade professional device that would fill up their entire living room (and that often has that kind of features I mentioned). – Belle

Answer 3

What is the relationship between ACID and database transaction?

In a relational database, every SQL statement must execute in the scope of a transaction.

Without defining the transaction boundaries explicitly, the database is going to use an implicit transaction which is wraps around every individual statement.

The implicit transaction begins before the statement is executed and end (commit or rollback) after the statement is executed. The implicit transaction mode is commonly known as auto-commit.

A transaction is a collection of read/write operations succeeding only if all contained operations succeed.

Inherently a transaction is characterized by four properties (commonly referred as ACID):

• Atomicity
• Consistency
• Isolation
• Durability

Does ACID give database transaction or is it the same thing?

For a relational database system, this is true because the SQL Standard specifies that a transaction should provide the ACID guarantees:

Atomicity

Atomicity takes individual operations and turns them into an all-or-nothing unit of work, succeeding if and only if all contained operations succeed.

A transaction might encapsulate a state change (unless it is a read-only one). A transaction must always leave the system in a consistent state, no matter how many concurrent transactions are interleaved at any given time.

Consistency

Consistency means that constraints are enforced for every committed transaction. That implies that all Keys, Data types, Checks and Trigger are successful and no constraint violation is triggered.

Isolation

Transactions require concurrency control mechanisms, and they guarantee correctness even when being interleaved. Isolation brings us the benefit of hiding uncommitted state changes from the outside world, as failing transactions shouldn’t ever corrupt the state of the system. Isolation is achieved through concurrency control using pessimistic or optimistic locking mechanisms.

Durability

A successful transaction must permanently change the state of a system, and before ending it, the state changes are recorded in a persisted transaction log. If our system is suddenly affected by a system crash or a power outage, then all unfinished committed transactions may be replayed.

Obliging

For "consistency" - I see "application specific logical correctness" as the explanation in many places. Assume if that is the case, DB can't know the app logic. So "consistency" can't be an inherent property of a DB. So for the app to implement its correctness (or "consistency") isn't Atomicity and Isolation enough ? – Trover

Let me take a look. But before that I need a basic clarification. Brain Campbell's comment in the accepted answer "With transactions, although individual statements don't preserve consistency, the transaction as a whole does". How is it possible ? And how does multiple debits and credits related to consistency ? – Trover

@VladMihalcea "A successful transaction must permanently change the state of a system" Can you please further elaborate on what "successful" means here? – Godfry

A successful transaction commits. One that fails will roll back. – Obliging

Answer 4

I slightly modified the printer example to make it more explainable

1 document which had 2 pages content was sent to printer

Transaction - document sent to printer

• atomicity - printer prints 2 pages of a document or none
• consistency - printer prints half page and the page gets stuck. The printer restarts itself and prints 2 pages with all content
• isolation - while there were too many print outs in progress - printer prints the right content of the document
• durability - while printing, there was a power cut- printer again prints documents without any errors

Hope this helps someone to get the hang of the concept of ACID

Antineutrino

I'm not sure what "while there were too many print outs in progress" means - it is just "multiple" rather than "too many"? – Tulley

Answer 5

ACID properties are very old and important concept of database theory. I know that you can find lots of posts on this topic, but still I would like to start share answer on this because this is very important topic of RDBMS.

Database System plays with lots of different types of transactions where all transaction has certain characteristic. This characteristic is known ACID Properties. ACID Properties take grantee for all database transactions to accomplish all tasks.

Atomicity : Either commit all or nothing.

Consistency : Make consistent record in terms of validate all rule and constraint of transaction.

Isolation : Make sure that two transaction is unaware to each other.

Durability : committed data stored forever. Reference taken from this article:

Disembowel

Answer 6

To quote Wikipedia:

ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee database transactions are processed reliably.

A DBMS that supports transactions will strive to support all of these properties - any commercial DBMS (as well as several open-source DBMSs) provide full ACID 'support' - although it's often possible (for example, with varying isolation levels in MSSQL) to lessen the ACIDness - thus losing the guarantee of fully transactional behaviour.

Oilla

Answer 7

ACID Properties in Databases:

• Atomicity : Transactions are all or nothing
• Consistency: Only valid data is saved (database from one state that is consistent to another state that is also consistent.)
• Isolation: Transaction do not effect each other (Multiple transactions can run at the same time in the system. Executing multiple transactions in parallel must have the same results as running them sequentially.)
• Durability: Written data will not be lost (even if the database crashes immediately or in the event of a power loss.)
  Credit

Eugenie

Answer 8

[Gray] introduced the ACD properties for a transaction in 1981. In 1983 [Haerder] added the Isolation property. In my opinion, the ACD properties would be have a more useful set of properties to discuss. One interpretation of Atomicity (that the transaction should be atomic as seen from any client any time) would actually imply the isolation property. The "isolation" property is useful when the transaction is not isolated; when the isolation property is relaxed. In ANSI SQL speak: if the isolation level is weaker then SERIALIZABLE. But when the isolation level is SERIALIZABLE, the isolation property is not really of interest.

I have written more about this in a blog post: "ACID Does Not Make Sense".

http://blog.franslundberg.com/2013/12/acid-does-not-make-sense.html

[Gray] The Transaction Concept, Jim Gray, 1981. http://research.microsoft.com/en-us/um/people/gray/papers/theTransactionConcept.pdf

[Haerder] Principles of Transaction-Oriented Database Recovery, Haerder and Reuter, 1983. http://www.stanford.edu/class/cs340v/papers/recovery.pdf

Onslaught

Answer 9

Transaction can be defined as a collection of task that are considered as minimum processing unit. Each minimum processing unit can not be divided further.

All transaction must contain four properties that commonly known as ACID properties. i.e ACID are the group of properties of any transaction.

• Atomicity :
• Consistency
• Isolation
• Durability

Dibbuk

Answer 10

This question was asked a decade ago, and since then, many SQL and NoSQL solutions have been developed. Unfortunately, the term ACID has become more of a marketing and advertising term. While some may claim that only "true" databases have ACID, it was never intended to be a silver bullet solution.

Determining whether a database is ACID-compatible can be challenging due to the lack of a consensus definition. Different databases and systems interpret and implement ACID differently. For example, some databases prioritize scalability over consistency and may provide eventual consistency, which deviates from the original ACID definition.

In general, it is advisable to check the documentation to see if a database provides some support for ACID and decide if it fits your needs. Additionally, I highly recommend reading Martin Kleppmann's book "Designing Data-Intensive Applications" to gain more in-depth understanding and details on this topic.

Humankind