SQL Reference ยท Transactions and Performance
SQL ACID properties
ACID explained for SQL databases: what atomicity, consistency, isolation and durability guarantee, how to see each in a transaction, and where they break down.
ACID is the checklist that makes database transactions trustworthy: Atomicity, Consistency, Isolation and Durability. A database that provides all four lets you write application code as if each transaction ran alone, completely, and survived any crash. Most relational databases provide ACID transactions; many NoSQL systems relax some of the letters to gain speed or scale.
This page explains each property, shows what you can try in a single session, and points out the common ways the guarantees are weaker than people assume.
Atomicity: all or nothing
A transaction is a single unit. Either every statement takes effect, or none of them does. If something fails halfway, ROLLBACK (or a crash) returns the data to its state before the transaction started.
Press Run (or Ctrl + Enter) to see the result here.
Both updates appear inside the transaction, and both vanish after the rollback. Nobody can observe a state where stock left product 1 but never arrived at product 2.
Consistency: rules always hold
A transaction takes the database from one valid state to another. "Valid" means every constraint is satisfied: primary keys, foreign keys, NOT NULL, UNIQUE and CHECK. If a statement would break a rule, the database rejects it. Note that the database only enforces the rules you declared; business rules that exist only in application code are not protected.
Press Run (or Ctrl + Enter) to see the result here.
The second update fails with CHECK constraint failed: price >= 0, because products.price must be at least 0. In SQLite only the failing statement is undone; the first update is still pending until you commit or roll back.
Isolation: concurrent transactions do not interfere
While one transaction runs, other sessions should not see its half-finished work, and concurrent updates should not corrupt each other. Perfect isolation is expensive, so databases offer levels of isolation that trade strictness for concurrency. A single browser session cannot show concurrent behavior, but you can see why isolation matters by simulating two sessions that each read a value, compute, and write back:
Press Run (or Ctrl + Enter) to see the result here.
Two sales happened but the first version shows only one sale: a lost update. Relative updates (qty = qty - 1) or locking avoid it.
Durability: committed means saved
Once COMMIT returns, the data survives crashes and power loss. Databases achieve this by writing changes to a log (the write-ahead log, journal or redo log) on disk before they confirm the commit. You can weaken durability on purpose for speed: PostgreSQL's synchronous_commit = off, MySQL's innodb_flush_log_at_trx_commit = 2 and SQLite's PRAGMA synchronous = OFF all risk losing the most recent commits in a crash. Durability also depends on the hardware honoring flush requests.
What ACID does not promise
- It is per database. A transaction cannot span your database and an email service or payment API. Cross-system work needs patterns such as outboxes and idempotent retries.
- Isolation is a dial. The default level in many databases still allows anomalies such as non-repeatable reads.
- Replicas lag. Reading from a replica right after a commit may show old data.
- Rollback does not undo side effects outside the database, such as sent emails or consumed sequence numbers (
AUTOINCREMENTand sequences are not rolled back).
Dialect notes
- PostgreSQL, SQL Server, Oracle and MySQL (InnoDB) provide full ACID transactions; MySQL's MyISAM engine does not.
- SQLite is fully ACID with a single writer at a time.
- Oracle and SQL Server commit DDL implicitly or transactionally in different ways; see BEGIN, COMMIT and ROLLBACK.