Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 79 ยท Transactions and ACID

ACID Properties in Depth

Learn the four ACID properties, which part of a DBMS enforces each one, what goes wrong when one is missing, and watch atomicity at work in a live SQLite demo.

A transaction is a group of operations that the database treats as one logical unit of work. The promise a DBMS makes about transactions is summed up by four words: atomicity, consistency, isolation, durability, or ACID. The acronym is easy to memorize and surprisingly easy to misunderstand, because the four properties are not equals. Two of them (atomicity and durability) are guarantees about failures, one (isolation) is a guarantee about concurrency, and one (consistency) is a shared responsibility between the database and you.

In this lesson you will learn what each property really promises, which component of the system is responsible for it, and what a database without it would look like. You will also watch atomicity break on purpose, so you remember why it matters.

Four promises about your data

Atomicity All of it happens, or none of it.
Consistency Rules hold before and after.
Isolation Transactions do not trip over each other.
Durability Committed means it stays.

Atomicity and durability handle crashes; isolation handles concurrency.

1 / 2

The running example: a bank transfer

Every ACID explanation uses a money transfer, because it is the smallest example where a half-finished job is a disaster. Moving 100 from account A to account B takes two writes: subtract 100 from A, add 100 to B. If the system dies between them, 100 has vanished. If another transaction reads both balances between them, it sees a total that is 100 short. If the transfer commits and the power fails a second later, the customer wants the transfer to still be there. ACID answers each of these scenarios.

Atomicity: all or nothing

Atomicity means the transaction is indivisible from the outside. Either every operation takes effect, or none does. If anything goes wrong before the commit (an error, a constraint violation, an application crash, a power cut) the database must behave as if the transaction had never started.

Notice what atomicity does not promise: it does not promise the transaction succeeds. It promises that a failure leaves no half-done work behind. The component responsible is the recovery manager (part of the transaction manager). While a transaction runs, it records before-images of the changed data in a log, so a rollback can put the old values back. After a crash, the same log tells the system which unfinished transactions to undo. Logging and write-ahead logging shows how.

Here is the failure mode without atomicity. The second statement of the transfer "fails" because the destination account does not exist: an UPDATE that matches no row is not an SQL error, so nothing stops the debit from standing alone.

SQL No transaction: money disappears

Press Run (or Ctrl + Enter) to see the result here.

The total was 800 and is now 700. The credit changed zero rows, the debit was already permanent, and nobody got an error. Now wrap the same two statements in a transaction and decide, after looking at what happened, to undo everything:

SQL Same transfer inside a transaction

Press Run (or Ctrl + Enter) to see the result here.

Inside the transaction the total is still 700, but after ROLLBACK it is 800 again: the debit never happened as far as anyone else can tell. In a real application the check changes() = 0 would be made by your code, which then issues ROLLBACK. Atomicity gives you the undo button; deciding to press it is still your job.

The unit of atomicity also applies to a single statement. A multi-row UPDATE that violates a constraint on the third row is undone entirely, including the rows already changed. The next example ends with an error on purpose: it tries to subtract 400 from every account, which is fine for Ana (500 to 100) but would push Ben (300) below zero.

SQL A statement that fails as a whole (ends with an error)

Press Run (or Ctrl + Enter) to see the result here.

The error message is the point. If you ran SELECT * FROM account afterwards in the same database, both balances would still be 500 and 300.

Consistency: valid state to valid state

A database has rules: primary keys are unique, foreign keys point at real rows, balances are not negative, and application rules such as "total money in the bank is constant". A state that satisfies all of them is consistent. The C in ACID says that a transaction, run alone and to completion, moves the database from one consistent state to another.

This is the odd one out. The DBMS can enforce consistency only for the rules it knows about: keys, foreign keys, CHECK constraints and triggers. A rule like "the sum of all balances never changes in a transfer" lives in your transaction's logic. If you write a transfer that subtracts 100 and adds only 90, the database will happily commit it. So consistency is a contract: the DBMS checks the declared rules and guarantees the other three properties, and the programmer guarantees that each transaction is correct on its own. Many textbooks say the other three properties exist in order to make consistency possible.

Constraint checking also shows a design choice. Most checks happen at the end of each statement, but some engines let you defer them to commit time, which allows a transaction to pass through a temporarily invalid state as long as it is valid at the end.

Isolation: transactions do not see each other's mess

With thousands of transactions running at once, each one should feel as if it were alone. Isolation means the effect of running transactions concurrently is the same as running them one after another in some order, a property called serializability. Without isolation you get the anomalies of the next lessons: reading uncommitted data, two clients overwriting each other's updates, totals that do not add up.

The component responsible is concurrency control: locks with two-phase locking, timestamps, or multiple versions of each row. Full isolation is expensive, so real systems offer weaker isolation levels and let you trade correctness guarantees for speed. Isolation is therefore the only ACID property that is a dial rather than a switch.

Durability: committed stays committed

Once the system tells you a transaction has committed, its effects must survive any later failure: a crash, a power loss, a restart. Memory is lost on power failure, so durability means the changes (or enough information to rebuild them) must reach non-volatile storage before the commit is acknowledged.

Writing every changed data page to disk at commit would be slow, because the pages are scattered. Instead the system appends a small commit record to a sequential write-ahead log and forces only the log to disk. The data pages can be written lazily later; after a crash the log is replayed (redone) to restore them. The recovery manager is again the owner. In SQLite the knobs look like this (read-only here, because a browser database lives in memory and has no file to protect):

SQL
PRAGMA journal_mode = WAL;     -- keep a write-ahead log file
PRAGMA synchronous = FULL;     -- fsync at commit so the log really reaches the disk

Durability has limits worth knowing. A disk that lies about having flushed its cache, or a destroyed machine, defeats a single-server log. Replication and backups extend the promise to those cases (Replication, Sharding and Distributed Transactions). Many systems also let you relax durability on purpose (flush the log once per second, for example) for speed, accepting that the last moments before a crash can be lost.

What breaks without each property

Property If it is missing Typical symptom Main mechanism
Atomicity Half-done work stays Money debited but never credited Undo log, rollback
Consistency Rules can be violated Orders pointing at deleted customers Constraints, triggers, correct code
Isolation Transactions interfere Lost updates, wrong totals Locks, versions, timestamps
Durability Commits can vanish "Paid" order missing after a restart Write-ahead log, fsync, replication

Remember that these properties interact. A rollback (atomicity) must not expose data that other transactions already saw (isolation), and recovery after a crash must redo committed work and undo uncommitted work (durability and atomicity together). That is why one log serves several properties at once.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A transfer debits account A and then the server crashes before crediting account B. After restart, A is unchanged. Which property made that happen?

    Atomicity means an unfinished transaction leaves no trace. The recovery manager uses the log to undo the debit.
  2. 2. Who is responsible for the rule "the total money in the bank stays constant during a transfer"?

    The DBMS can only enforce rules it has been told about. Application-level invariants depend on each transaction being written correctly.
  3. 3. Why does a DBMS force only the log to disk at commit, not all changed data pages?

    Sequential log appends are fast. After a crash the log is replayed to rebuild any pages that were not yet written, which gives durability without slow random writes at commit.
  4. 4. Which ACID property is usually offered as a configurable level rather than all-or-nothing?

    Full serializable isolation costs concurrency, so databases offer weaker isolation levels as a trade-off between correctness guarantees and performance.

Summary

  • Atomicity: all operations of a transaction take effect, or none; the recovery manager provides it with an undo log.
  • Consistency: a correct transaction moves the database between valid states; the DBMS checks declared constraints and you write correct logic.
  • Isolation: concurrent transactions behave as if run one after another; concurrency control provides it, in adjustable strength.
  • Durability: committed changes survive crashes; the write-ahead log forced at commit provides it.
  • An UPDATE that matches no row is not an error, so atomicity needs you to check results and roll back deliberately.

Next: Schedules, Serializability and Precedence Graphs.