A transaction groups several operations into a whole that the database accepts entirely or refuses entirely. This lesson is for anyone who administers or chooses a database and wants to know what this all-or-nothing really promises. It applies to every relational engine, PostgreSQL as well as MySQL. The running example remains the incident tracker of a server fleet from the previous lesson. You will see the four guarantees of ACID, only one of which can be tuned, isolation, then BASE, the contract chosen by some distributed systems, and how to decide between the two.
What you will learn
- Define a transaction and explain why all-or-nothing is the foundation of trust in a database
- Match each letter of ACID to the incident it prevents
- Name the isolation anomalies and say why the default level changes from one engine to another
- Explain BASE as a contract distinct from ACID, with what it gains and what it loses
- Choose between ACID and BASE on a realistic case
What is a transaction, and why all or nothing?
A transaction is a series of operations that the database treats as a single unit: either all of them succeed and are committed (the term is commit), or none takes effect and everything is cancelled (the term is rollback). There is no in-between: the database never keeps the result of a half-executed transaction.
In our fleet, closing an incident is never a single write. You must set the incident to the "resolved" state, decrement the server's open-incident counter (a precomputed, copied value, called denormalized, which the database does not keep up to date by itself: see Data modeling) and add a row to the history. If just one of these three writes is missing, the figures contradict each other. The transaction turns this list of actions into one reliable action, and that is what lets you trust a database without rereading its tables after each crash.
Relational databases (in French) keep this promise under the name ACID.
What does each letter of ACID promise?
ACID brings together four guarantees: Atomicity, Consistency, Isolation, Durability. Each prevents a specific kind of damage.
Atomicity: what happens to an operation cut in two?
Atomicity guarantees all or nothing. If the server stops after the "resolved" state is written and before the counter is, the database cancels the incident at restart: it stays open, as if nothing had happened. Without atomicity, you would have a closed incident and a counter that still shows three alerts, with no error message to flag it.
Consistency: who guards the model's rules?
Consistency guarantees that a transaction takes the database from one valid state to another valid state, in terms of the declared rules: foreign keys, uniqueness constraints, mandatory columns, checks. A transaction that would violate a rule is refused entirely. Two nuances avoid a frequent misreading. This consistency only checks what the model has declared: a rule nobody wrote down in the database is not protected. And it has nothing to do with the consistency of the CAP theorem, which is about copies of a piece of data on several machines (see the section on BASE).
Durability: what survives a crash?
Durability guarantees that once the transaction is confirmed to the application, its result survives an abrupt stop: the database has written the essentials to a log on disk before answering. It promises not to forget what you committed, not to commit only what is right: a mistaken but committed deletion is perfectly durable, and the loss of the whole disk is handled by backup and replication, not by the transaction.
Why is isolation the letter that gets negotiated?
The other three letters are binary guarantees: a transaction is atomic, or it is not. Isolation, on the other hand, is a setting with several levels. It describes what a transaction sees of the changes other transactions make at the same time. The stricter the level, the more the database must coordinate sessions, that is, connections working at the same time (make them wait, refuse, have them replay), and the less work it can run in parallel, which is called concurrency. A permissive level goes faster, but allows surprising reads.
A widespread idea says isolation guarantees you see nothing of other transactions. That is false, and even the strictest level, SERIALIZABLE, does not promise it: it guarantees a result identical to running one transaction after the other, which the engine obtains by making a transaction wait or fail. An engine is allowed to be stricter than the promise of its level, which PostgreSQL does (see below the table of levels). The anomalies below say what a level lets you see of other transactions.
Which anomalies does isolation let through?
The SQL standard names three anomalies, that is, three ways a read can surprise you.
- A dirty read reads a change from another transaction that is not yet committed, and may be cancelled.
- A non-repeatable read rereads the same row in the same transaction and gets another value, because another transaction modified it and committed in the meantime.
- Phantom rows appear when the same range query ("all incidents of web-01") returns a different set of rows, because another transaction inserted or deleted some: a new incident pops up in the list.
The diagram shows a non-repeatable read over time. Look at technician A: she asks the same question twice in the same transaction and gets 3 then 2, although nothing changed on her side. Between the two reads, technician B moved one of the incidents already read from "open" to "resolved" and committed: he is the one who changed the answer.
Other anomalies exist outside this standard, such as the lost update, where two sessions read a value then each write their own: one overwrites the other. The three anomalies only serve to place the levels.
Which level allows which anomaly?
The standard defines four levels, from the most permissive to the strictest. This table gives what each one can let through at most.
| Level | Dirty read | Non-repeatable read | Phantom rows |
|---|---|---|---|
READ UNCOMMITTED | possible | possible | possible |
READ COMMITTED | impossible | possible | possible |
REPEATABLE READ | impossible | impossible | possible |
SERIALIZABLE | impossible | impossible | impossible |
The practical trap is that engines do not start from the same level. PostgreSQL applies READ COMMITTED by default, MySQL with InnoDB applies REPEATABLE READ. The same code can therefore behave differently from one engine to the other, and a migration from one to the other moves bugs around.
PostgreSQL also benefits twice from the freedom to be stricter than the standard. First, it treats READ UNCOMMITTED as READ COMMITTED, so that other people's uncommitted work stays invisible at every level: no dirty read is possible. Second, its REPEATABLE READ also forbids the phantom rows that the table allows. These are behaviors of the engine and not promises of the level: another engine is not bound to offer them. For InnoDB, check the actual behavior in the engine's documentation rather than deducing it from the table.
The lesson on transactions and concurrency (in French) replays these levels on two truly concurrent sessions: waiting on a lock, the lost update, the deadlock and the maximum wait time. Here, just remember that there is an isolation level to choose, and that it trades read consistency against throughput.
What is BASE, and why does it exist?
BASE is an acronym for Basically Available, Soft state, Eventually consistent. The name, a pun on ACID (acid and base in chemistry), comes from a 2008 article by Dan Pritchett ("BASE: An Acid Alternative", ACM Queue). It describes a way of designing systems spread over several machines, not a standard.
- Basically Available: the system keeps answering, even if with partial or slightly old data, even when part of the machines or of the network is down.
- Soft state: the visible state can change without a new write, because the copies of a piece of data catch up with each other.
- Eventually consistent: if nobody writes any more, all the copies end up converging to the same value, without promising when.
The origin of the choice is the network partition. As soon as data is copied on several machines, the network eventually breaks between them, and the system must decide: refuse requests to stay consistent, or answer with diverging copies. This is the trade-off of the CAP theorem, detailed in Synchronous, asynchronous, eventual consistency (in French). BASE chooses to answer. The diagram below, taken from that lesson, shows this moment: look at node 2, isolated, which must either refuse to write or answer with its own data.
Is BASE a discount ACID?
No: it is another contract, which trades one guarantee for another. ACID protects the correctness of each transaction and pays for it in coordination, hence in latency (response time) and in availability when machines are lost. BASE protects the fast and permanent answer and pays for it in potentially stale reads and in write conflicts to resolve. The table compares the two on what you get and what you put up with.
| ACID | BASE | |
|---|---|---|
| Central promise | each transaction is correct and durable | the system always answers |
| Read right after a write | the new value | maybe the old one, for a short moment |
| During a network partition | may refuse requests | answers with diverging copies |
| What the application must handle | little: the database guarantees | conflicts, duplicates, compensations |
| Original ground | one database, one primary server | data spread over several machines |
The cost therefore moves from the database to the application. To undo an operation that half-succeeded across several services, there is no more automatic rollback: compensations (the Saga pattern, a sequence of steps each with its inverse action, covered in the CAP lesson cited above) take over.
Do ACID and BASE split along SQL and NoSQL?
Not as neatly as the received idea says, and the 2026 state must be read up to date. MongoDB offers multi-document transactions since version 4.0 (2018), extended in 4.2 to sharded clusters, that is, to data spread over several machines. DynamoDB has offered transactional writes since late 2018. These transactions have their own limits (size, number of items, cost) and do not make these databases "relational", but claiming that a NoSQL database cannot do transactions is outdated.
The reverse is true too: a relational database with an asynchronous read replica (a copy of the primary server, fed with a slight delay) offers, on that replica, eventual consistency, since it can show a value already modified on the primary (see PostgreSQL replication, in French). The contract depends on what you read and where, not on the logo of the engine. For the four families of NoSQL databases and their CAP choice, see NoSQL databases (in French).
ACID or BASE: which fits which need?
The useful question is: what does wrong data cost, and what does a refused answer cost? There is no good contract in itself, only a contract suited to each piece of data, and one system often uses two.
| Need | Contract | Reason |
|---|---|---|
| Close an incident and update counter and history | ACID | three writes that must hold together |
| Transfer between two accounts | ACID | a debit without a credit is lost money |
| View counter of the status page | BASE | a gap of a few units is harmless |
| Stock of an item when the last unit leaves | ACID | two sales of the last unit are unacceptable |
In our fleet, the incident history stays in a transactional database, while the view count of the public page can catch up with the other copies without anyone noticing. A bank account and a view counter do not have the same needs, and it is not a matter of taste: it is the price of the error that decides.
Key takeaways
- A transaction is all or nothing: all its operations are committed together, or none is.
- Atomicity (no half-written state), consistency (the declared rules hold), durability (what is committed survives the crash) are binary guarantees.
- Durability protects neither against a committed mistake nor against the loss of the disk: those are the roles of backup and replication.
- The consistency of ACID is not that of CAP: one is about the rules of the model, the other about copies on several machines.
- Isolation is a setting: each level allows more or less (dirty read, non-repeatable read, phantom rows), and only
SERIALIZABLEequals running one transaction after the other. - The default level changes with the engine:
READ COMMITTEDfor PostgreSQL,REPEATABLE READfor MySQL with InnoDB. PostgreSQL is also stricter than what its levels promise, which remains a behavior of the engine and not a guarantee of the level. - BASE (Basically Available, Soft state, Eventually consistent) is another contract: availability against temporarily stale reads, and compensation work on the application side.
- ACID and BASE do not map onto SQL and NoSQL: MongoDB and DynamoDB have transactions, and a relational asynchronous replica is eventually consistent.
Knowledge check
Eight questions on the all-or-nothing and what atomicity and durability promise, the anomalies and levels of isolation, what BASE trades for what, and why ACID and BASE do not split between SQL and NoSQL.
Knowledge check
Check what you have learned with this interactive quiz
How it works
- The timer starts when you click Start
- Multiple choice, true/false and short answer questions
- You can move between questions
- Detailed results are shown at the end
Starts the quiz and the timer
Review
(0/0)Score by domain
A domain groups several guides and gets several questions, so its score is more reliable than a per-topic score, which often rests on a single question.
Summary
Strengths
No topic passed at 80% on at least two questions yet.
Weak areas
No topic below the pass mark.
Recommended next
Hints before you try again?
Continue the course: How does a database server work?
A new full quiz with random questions
Work again only on the questions you missed