Database Concepts
What Is a Transaction? ACID Demystified
A transaction is the database's promise that a group of reads and writes either all happen or none do. This lesson unpacks ACID one letter at a time using PostgreSQL, separates the real guarantees from the marketing, and shows how the topic comes up in real systems and interviews.
Picture a bank transfer. You move 500 rupees from account A to account B. Under the hood that is two separate writes: subtract 500 from A, then add 500 to B. Now suppose the server loses power in the gap between those two writes. The money has left A but never arrived at B. It has simply vanished, and no amount of apologizing to the customer will conjure it back.
A transaction is the database's answer to this problem. It is a promise that a group of reads and writes is treated as one indivisible unit: either every operation in the group takes effect, or none of them do. There is no surviving state in which the money is gone but not yet delivered.
A Transaction Is a Unit of Work
Concretely, you wrap related statements between a start marker and an end marker. The database then guarantees that the whole block behaves as a single logical action. You commit to make the changes permanent, or you roll back (also called abort) to discard them all as if they never ran. Here is the transfer written as a SQL transaction.
If both updates succeed, COMMIT writes them to durable storage and other sessions begin to see the new balances. If anything goes wrong before the commit (a constraint violation, a crash, or an explicit ROLLBACK), the database undoes the first update too. The half-finished state, money debited but not credited, is never visible to anyone. That ability to abort cleanly and retry is the entire reason transactions exist.
Why this matters
Without transactions, your application code has to handle every partial-failure combination by hand: the first write succeeded but the second failed, the network dropped after the second write but before the response came back, and so on. Transactions collapse that maze into one clean choice. It either happened or it did not, and you can safely retry.
Autocommit: one statement, one transaction
Most databases (including PostgreSQL and MySQL) wrap every statement you run outside an explicit BEGIN in its own implicit transaction and commit it automatically, so a single UPDATE is already atomic on its own. BEGIN (or START TRANSACTION) is how you extend that all-or-nothing guarantee across several statements, and SAVEPOINT lets you roll back just part of a long one. Oracle is the exception: it opens a transaction implicitly but waits for an explicit COMMIT.
ACID: Four Letters, One Slogan
The safety guarantees of transactions are usually summarized by the acronym ACID: Atomicity, Consistency, Isolation, and Durability. It was coined in 1983 by Theo Härder and Andreas Reuter to give people precise vocabulary for what a transaction promises.
A word of warning before we dive in. In practice ACID has drifted into a marketing term. One vendor's "ACID compliant" can mean something noticeably weaker than another's, and the C in particular is the odd letter out, as you will see. There is even a deliberately tongue-in-cheek opposite, BASE (Basically Available, Soft state, Eventual consistency), used by systems that trade these guarantees away for scale. Let us take the letters one at a time and separate the real promises from the slogan.
Atomicity: All or Nothing
Atomicity means a transaction's writes are indivisible. If the transaction makes several changes and then something fails partway through (a broken constraint, a full disk, a process crash), the database discards every change it had already made. You are never left with some writes applied and others not.
In the transfer example, atomicity is exactly what stops the money from disappearing. Either both the debit and the credit land, or neither does. There is no scenario where account A is lighter but account B never grew.
Here is that all-or-nothing behavior in SQL. When the second statement fails, the first is rolled back along with it.
Atomicity is not about concurrency
A more honest name for atomicity would be abortability. It describes the database's ability to throw away a half-done transaction on failure. It says nothing about what happens when several transactions run at the same time. That concurrency question belongs to a different letter (the I), and confusing the two is one of the most common mistakes people make.
Consistency: The Odd One Out
Consistency in the ACID sense means a transaction takes the database from one valid state to another, never leaving it in a state that breaks your rules. Those rules are called invariants: account balances never go negative, every order references a real customer, a username appears at most once.
The slippery word is valid. The database has no built-in idea of what your data means. A balance of -500 is not wrong to the database; it is just a number. It becomes wrong only because your business decided balances cannot go negative. Every invariant is a rule you bring to the database, never one it knows on its own.
What the database can do is enforce a narrow slice of those rules, the ones you can spell out in the schema. NOT NULL requires a column to always hold a value, UNIQUE forbids duplicates, a foreign key insists the row you point at actually exists, and CHECK demands each value satisfy a condition such as balance >= 0. These are real and worth using. But notice that the database is only policing rules you declared. It enforces your invariants; it does not invent them.
Most invariants cannot be written as a constraint at all. Take "the total money across all accounts is unchanged after a transfer." No column, foreign key, or CHECK can express that, because it is a rule about how two separate rows must move together, and only your code understands it. If your transaction debits 500 from A but credits 400 to B by mistake, every ACID guarantee still holds: the writes were atomic, isolated, and durable. The database commits the result without complaint, and 100 rupees quietly vanish. The data is now invalid and nothing in the engine noticed.
This is the real reason consistency is the odd letter out. Atomicity, isolation, and durability are things the database does for you regardless of what your data means. Consistency is a claim about meaning, and meaning lives in your application. The database hands you tools to help keep your invariants true, constraints plus the safety of atomicity and isolation, but it cannot decide what correct means for your domain and it cannot guarantee it. The other three letters are the engine's promises. The C is yours.
The split is easiest to see in SQL: one invariant the database can guard for you, another it has no way to know about.
The C is the impostor
The C is widely regarded as the letter that does not belong. Joe Hellerstein has remarked that consistency was tossed into ACID largely to make the acronym pronounceable. Unlike the other three, it is fundamentally a property of the application, not a guarantee the storage engine provides on its own.
Isolation: Concurrency Without Chaos
Isolation governs what happens when many transactions run at the same time. Each one is correct on its own; the trouble starts when they overlap, because one transaction can read or overwrite data another is halfway through changing, and the result is a state neither of them intended.
The textbook example is the lost update. Two sessions both want to increment the same counter, say a likes column. Each reads the current value, adds one, and writes it back. If the two run one after the other the counter ends up correct, but when their steps interleave one of the increments silently disappears, as the diagram below shows.
The cure for anomalies like this is the isolation level, the setting that controls how much concurrent transactions are allowed to see of each other. The strongest level is serializable: the database guarantees the outcome is as if the transactions had run one after another in some order, even though they actually overlapped. At that level the lost update cannot happen.
Serializable is also the most expensive level, because the database has to do real work to detect and block conflicting interleavings. To stay fast, databases offer weaker isolation levels that permit some anomalies in exchange for throughput. The catch is that the default level is not the same everywhere, which is a classic source of bugs when a team moves between engines.
| Database | Default isolation level | Worth knowing |
|---|---|---|
| PostgreSQL | Read Committed | Also offers Repeatable Read (snapshot isolation) and a true Serializable level built on SSI. |
| MySQL (InnoDB) | Repeatable Read | A stronger default than Postgres. Uses next-key locks to block most phantom rows. |
| Oracle | Read Committed | Has no Read Uncommitted and never allows dirty reads. Only Read Committed and Serializable exist. |
| SQL Server | Read Committed | Lock-based by default; can be switched to Read Committed Snapshot (RCSI) for MVCC-style reads. |
Default isolation levels differ across engines, a favorite interview gotcha. The next lesson covers what each level actually guarantees and where it leaks.
Durability: Once Committed, Never Forgotten
Durability is the promise that once the database has told you a transaction committed, the data will survive, even if the machine crashes, loses power, or restarts a second later. A committed write is not allowed to evaporate.
Databases deliver this with a write-ahead log (WAL, also called a redo log). Before a change is applied to the main data files, it is appended to the log and flushed to physical disk with fsync. If the server crashes, recovery replays the log to restore every committed transaction. Most engines let you trade a little durability for speed by relaxing that flush (PostgreSQL has synchronous_commit, MySQL has innodb_flush_log_at_trx_commit), and in a replicated cluster you can require the log to reach a standby before a commit is acknowledged.
The diagram below follows a single commit through the WAL and out the other side of a crash.
Durability in the real world
Perfect durability does not exist. Disks fail, fsync can lie on cheap consumer hardware, and every replica can die in a single correlated outage. Durability is best read as "committed data is on stable storage and will survive the failures we planned for," not as an absolute, eternal guarantee.
| Property | Plain-English guarantee | Who enforces it | Problem it prevents |
|---|---|---|---|
| Atomicity | All writes apply, or none do | Database | Money debited but never credited after a crash |
| Consistency | Invariants stay true across the change | Mostly your application | A transfer that quietly creates or destroys money |
| Isolation | Concurrent transactions do not corrupt each other | Database | Lost updates and other race conditions |
| Durability | Committed data survives crashes | Database | A confirmed order vanishing on restart |
ACID at a glance. Note that consistency is the only property you are largely on the hook for yourself.
Single-Object vs Multi-Object Transactions
Almost every database, including key-value stores that loudly claim to have no transactions, gives you atomicity and isolation for a single object. Incrementing one row, or doing a compare-and-set on one document, is safe out of the box. That is the easy case.
The hard and genuinely valuable case is the multi-object transaction: changing several rows, or several tables, together as one unit. The bank transfer is one example. Another common one is inserting a new email and bumping an unread-message counter in the same breath, so a reader never sees the email without the count, or the count without the email.
Most relational databases handle multi-statement transactions like this comfortably. A few go further: PostgreSQL even makes schema changes transactional, so you can wrap CREATE TABLE or ALTER TABLE in a transaction and roll them back, whereas MySQL and Oracle commit implicitly around such statements. The difficulty appears when the data is spread across many machines, because coordinating an all-or-nothing write across nodes is genuinely hard. That difficulty is exactly why some distributed systems chose to drop multi-object transactions entirely.
Keep transactions short: no network calls inside them
A transaction is not free to leave open. While it is open it pins a database connection and holds any locks it has taken, so a classic production outage is opening a transaction, making a slow external call inside it (charging a card through a payment provider, say), and only then committing. Under load every request holds its connection for the length of that API call, the connection pool drains in seconds, and the whole system stalls. The rule is to keep transactions short and never make a network call to another service inside one: do the external call first, then open a brief transaction to record the result.
Do You Always Need Transactions?
Through the 2000s, many NoSQL and distributed systems abandoned multi-object transactions in pursuit of horizontal scale and high availability. They rallied around BASE as the philosophical opposite of ACID: stay available, accept that state is soft and replicas drift, and let the data become consistent eventually.
This is a real engineering trade, not a free upgrade. You gain throughput and the ability to keep serving during a network partition, and in return you give up strong guarantees and push correctness handling back into your application code. The decision usually comes down to one question: what does wrong or lost data cost?
Rule of thumb
Reach for transactions when correctness is non-negotiable and the data is small and related, like payments, inventory, and bookings. Lean on eventual consistency when scale and availability matter more than a brief window of staleness, like view counts, likes, and activity feeds.
How This Shows Up in Interviews
ACID is one of the most reliably asked database topics, and interviewers are listening for whether you understand it or have only memorized the acronym. A few questions come up again and again. "What does ACID stand for, and which letter is special?" The answer they want is that consistency is the application's responsibility, while the database owns the other three. "What is the difference between atomicity and isolation?" Atomicity is all-or-nothing for one transaction's writes; isolation is about keeping concurrent transactions from interfering. "What is the default isolation level?" Read Committed in PostgreSQL, Oracle, and SQL Server, but Repeatable Read in MySQL, which surprises people who assume databases are serializable by default. "How does a database guarantee durability?" By writing to a write-ahead log and flushing it to disk before acknowledging the commit. If you can also name a case where you would deliberately give up ACID for scale, you will stand out.
A transaction debits account A, then the server crashes before it can credit account B. On restart, the database has discarded the debit, so A is back to its original balance. Which ACID property made this happen?
Two transactions both read a counter value of 42, both add one, and both write 43. The final value is 43 instead of 44, so one increment vanished. Which property would have prevented this?
Key Takeaways
A transaction groups reads and writes into one all-or-nothing unit, so you can stop hand-coding every partial-failure path and simply retry.
The ACID letters are best read as: atomicity is really abortability, isolation keeps concurrent transactions from corrupting each other, and durability means committed data survives crashes. Consistency is the odd one out, an application-level property the database can only partly enforce.
Single-object atomicity comes almost for free; multi-object transactions are the hard, valuable case and the reason BEGIN and COMMIT exist. In most engines the default isolation level is Read Committed (MySQL defaults to Repeatable Read), durability rests on a write-ahead log, and in PostgreSQL even schema changes are transactional.
In the next lesson we zoom in on isolation and meet the specific race conditions (dirty reads, lost updates, write skew, and phantoms) that weaker isolation levels quietly allow.