ACID
ACID (atomicity, consistency, isolation, durability) is a set of properties of database transactions intended to guarantee validity even in the event of errors, power failures, etc. These four properties describe the major guarantees of the transaction paradigm, which has influenced many aspects of development in database systems.
Atomicity
Each transaction is treated as a single unit, which either succeeds completely or fails completely. If any of the statements constituting a transaction fails to complete, the entire transaction fails and the database is left unchanged.
- An atomic system must guarantee atomicity in each and every situation, including power failures, errors and crashes.
Consistency failure
A very general term, which demands that the data must meet all validation rules.
- Assume that a transaction attempts to subtract 10 from A without altering B. If the transaction removes 10 successfully, atomicity will be achieved. However, a validation check will show that A + B = 90, which is inconsistent with the rules of the database. The entire transaction must be cancelled and the affected rows rolled back to their pre-transaction state
Durability failure
Consider a transaction that transfers 10 from A to B. First, it removes 10, then adds 10. At this point, the user is told the transaction was a success and the changes are still queued in the disk buffer waiting to be committed to disk. The user assumes (understandably) that the changes persist
Locking vs multiversioning
Locking means that the transaction marks the data that it accesses so that the DBMS knows not to allow other transactions to modify it until the first transaction succeeds or fails.
- Multiversion concurrency control: the database provides each reading transaction the prior, unmodified version of data that is being modified by another active transaction.
Examples
The following examples further illustrate the ACID properties.
Distributed transactions
The two-phase commit protocol provides atomicity for distributed transactions to ensure that each participant in the transaction agrees on whether the transaction should be committed or not
- Briefly, in the first phase, one node (the coordinator) interrogates the other nodes (the participants) and only when all reply that they are prepared does the coordinator formalize the transaction
Atomicity
The guarantee that series of database operations in an atomic transaction will either all occur (a successful operation), or none will occur (an unsuccessful operation).
- A guarantee of atomicity prevents updates to the database occurring only partially, which can cause greater problems than rejecting the whole series outright.
- Atomicity means indivisibility and irreducibility.
Isolation failure
If two transactions execute at the same time, each attempting to modify the same data, one must wait until the other completes to maintain isolation.
- For example, if T1 fails halfway through modifying B, T2 can already modify A; it cannot be restored to the value it had before T1 without leaving an invalid database.
Consistency
This ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants
- Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof
Isolation
Concurrent execution ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially
- Isolation is the main goal of concurrency control; depending on the method used, the effects of an incomplete transaction might not even be visible to other transactions.
Implementation
There are two popular families of techniques: write-ahead logging and shadow paging
- Write ahead logging: atomicity is guaranteed by copying the original (unchanged) data to a log before changing the database.
- Shadowing: updates are applied to a partial copy of the database, and the new copy is activated when the transaction commits