Suppose you send ₹1,000 to a friend using a banking app. Behind the scenes, the bank's database has to do at least two things: take ₹1,000 out of your account and put ₹1,000 into your friend's account.
Now imagine the server crashes after the first step but before the second. Your money has left your account, but it never reached your friend. ₹1,000 has simply vanished.
No real bank can allow that. The two steps must behave like one single, indivisible action: either both happen, or neither happens.
This is exactly what a transaction gives us.
A transaction is a sequence of database operations that is treated as one logical unit of work. Either all of its operations take effect, or none of them do.
In this chapter we will understand what a transaction is, how a DBMS views it, and the four famous guarantees every transaction gets: Atomicity, Consistency, Isolation and Durability (ACID). We will also run real transactions on MySQL 8 to see these guarantees in action.
What Is a Transaction?
A transaction is a logical unit of work from the point of view of the application. It may contain one SQL statement or many, but the application thinks of it as one job.
Some everyday examples:
Money transfer → debit one account, credit another
Online order → insert the order, reduce stock, record the payment
Train ticket booking → mark a berth as booked, create the ticket, charge the passenger
Course registration → add the student to the course, reduce available seats
In each case, doing only some of the steps would leave the database in a wrong, half-finished state. A sold-out train with no ticket issued, or a payment with no order, is worse than not doing anything at all.
Why "logical" unit?
The DBMS cannot guess which statements belong together. The application must tell it, by marking where a transaction begins and where it ends. In SQL this is done with commands such as START TRANSACTION, COMMIT and ROLLBACK (the TCL commands you met in the chapter Database Languages (DDL, DML, DCL, TCL)).
How the DBMS Sees a Transaction: Read and Write
A transaction may run complex business logic, but from the DBMS's point of view only two kinds of operations really matter, because only these touch the database:
read(X) → copy data item
Xfrom the database into a local variable of the transactionwrite(X) → copy the value of the local variable back into data item
Xin the database
Here a data item can be a row, a column value, a page or even a whole table. For theory, we just call it A, B, X and so on.
The money transfer of ₹1,000 from account A to account B looks like this:
T1:
read(A)
A := A - 1000
write(A)
read(B)
B := B + 1000
write(B)
commit
The lines A := A - 1000 and B := B + 1000 happen in the transaction's own memory. The database itself only sees the reads and writes.
In theory chapters (and in GATE questions), this is usually written in a short form:
R1(A) W1(A) R1(B) W1(B) C1
where R1(A) means "transaction T1 reads A", W1(A) means "T1 writes A", C1 means "T1 commits" and A1 would mean "T1 aborts". We will use this notation heavily in the next few chapters.
Note: a
write(X)does not necessarily go to disk immediately. The new value usually sits in a buffer in main memory first, and the DBMS decides later when to flush it. This detail is exactly why atomicity and durability need special care, as we will see.
COMMIT and ROLLBACK
A transaction always ends in one of two ways:
Ending | Meaning | Effect on the database |
|---|---|---|
COMMIT | "Everything went fine, make it permanent." | All changes become permanent and visible to others |
ROLLBACK (abort) | "Something went wrong, cancel everything." | All changes made by the transaction are undone |
Let us try this on a real MySQL 8.4 server. We create a small table of bank accounts. The CHECK constraint says a balance can never go negative:
CREATE TABLE accounts (
acc_no INT PRIMARY KEY,
holder VARCHAR(50) NOT NULL,
balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0)
) ENGINE=InnoDB;
INSERT INTO accounts VALUES (1, 'Aarav', 5000.00), (2, 'Priya', 3000.00);
The total money in the system is ₹8,000.
A Committed Transfer
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE acc_no = 1;
UPDATE accounts SET balance = balance + 1000 WHERE acc_no = 2;
COMMIT;
SELECT * FROM accounts;
SELECT SUM(balance) AS total FROM accounts;
Output:
+--------+--------+---------+
| acc_no | holder | balance |
+--------+--------+---------+
| 1 | Aarav | 4000.00 |
| 2 | Priya | 4000.00 |
+--------+--------+---------+
+---------+
| total |
+---------+
| 8000.00 |
+---------+
Both updates happened, and the total is still ₹8,000.
A Rolled-Back Transfer
Starting again from 5000 / 3000:
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE acc_no = 1;
SELECT * FROM accounts; -- inside the transaction
ROLLBACK;
SELECT * FROM accounts; -- after the rollback
Output:
+--------+--------+---------+
| acc_no | holder | balance |
+--------+--------+---------+
| 1 | Aarav | 4000.00 |
| 2 | Priya | 3000.00 |
+--------+--------+---------+
+--------+--------+---------+
| acc_no | holder | balance |
+--------+--------+---------+
| 1 | Aarav | 5000.00 |
| 2 | Priya | 3000.00 |
+--------+--------+---------+
Inside the transaction, the debit was visible (to this session). After ROLLBACK, the DBMS undid it completely, as if it never happened.
Autocommit
If you run an UPDATE without START TRANSACTION, is it still a transaction? Yes. MySQL runs with autocommit turned on by default, which means every single statement is automatically its own transaction and is committed as soon as it finishes successfully.
SELECT @@autocommit, @@transaction_isolation;
+--------------+-------------------------+
| @@autocommit | @@transaction_isolation |
+--------------+-------------------------+
| 1 | REPEATABLE-READ |
+--------------+-------------------------+
So you need START TRANSACTION (or SET autocommit = 0) only when you want several statements to succeed or fail together. (We will come back to the isolation level shown here later in this chapter.)
The ACID Properties
To keep data correct in the presence of crashes and many simultaneous users, a DBMS guarantees four properties for every transaction. Together they are called ACID:
Letter | Property | One-line meaning | Protects against |
|---|---|---|---|
A | Atomicity | All or nothing | Half-finished work after a failure |
C | Consistency | Valid state to valid state | Broken rules / invariants |
I | Isolation | Concurrent transactions do not see each other's half-done work | Interference between users |
D | Durability | Once committed, it stays | Losing committed data in a crash |
Let us look at each one carefully, along with who is responsible for it inside the DBMS. That second part is a favourite interview follow-up.
A — Atomicity
Atomicity: either all operations of a transaction are reflected in the database, or none are.
The word comes from "atom", which was once believed to be indivisible. A transaction cannot be split into "the part that happened" and "the part that did not".
Why it is needed
Look at the transfer again and imagine a failure between the two writes:
read(A) A = 5000
A := A - 1000
write(A) A = 4000
<-- power failure / crash / error here
read(B)
B := B + 1000
write(B)
After the crash, A = 4000 and B = 3000. The total is ₹7,000 instead of ₹8,000. The database is in an inconsistent state.
How the DBMS achieves atomicity
Before changing data, the DBMS records enough information to undo the change, usually the old value of the item. If the transaction fails or the system crashes before commit, the DBMS uses this information to put the old values back.
Before write(A): remember "A was 5000"
Failure happens
Recovery: put A back to 5000 -> as if T1 never ran
In MySQL's InnoDB engine, this information is kept in undo logs. The component in charge is the recovery manager (also called the transaction manager's recovery subsystem). The full mechanism of logs, undo and redo is covered in the chapter Recovery & Logging.
We can see atomicity after a "crash" of the client. Here a session starts a transaction, sets Aarav's balance to 0, and then disconnects without committing (just like an app that crashed). A fresh session then looks at the table:
-- session 1 (then disconnects without COMMIT)
START TRANSACTION;
UPDATE accounts SET balance = 0 WHERE acc_no = 1;
-- session 2
SELECT * FROM accounts;
+--------+--------+---------+
| acc_no | holder | balance |
+--------+--------+---------+
| 1 | Aarav | 5000.00 |
| 2 | Priya | 3000.00 |
+--------+--------+---------+
The unfinished transaction was rolled back automatically. Nothing of it survived.
A common trap: an error does not always roll back the whole transaction
Many students assume that if one statement inside a transaction fails, the database cancels the entire transaction. In MySQL, that is not what happens for most errors. Only the failing statement is undone. The transaction stays open, and earlier statements are still pending.
Watch this transfer of ₹7,000 from Aarav (who has only ₹5,000). We credit first and debit second, so the second statement violates the CHECK constraint:
START TRANSACTION;
UPDATE accounts SET balance = balance + 7000 WHERE acc_no = 2;
UPDATE accounts SET balance = balance - 7000 WHERE acc_no = 1; -- fails
SELECT * FROM accounts;
ERROR 3819 (HY000) at line 3: Check constraint 'accounts_chk_1' is violated.
+--------+--------+----------+
| acc_no | holder | balance |
+--------+--------+----------+
| 1 | Aarav | 5000.00 |
| 2 | Priya | 10000.00 |
+--------+--------+----------+
Priya's credit of ₹7,000 is still there inside the open transaction! If the program now blindly ran COMMIT, ₹7,000 would be created out of thin air. The correct thing is for the application to notice the error and issue ROLLBACK:
ROLLBACK;
SELECT * FROM accounts;
+--------+--------+---------+
| acc_no | holder | balance |
+--------+--------+---------+
| 1 | Aarav | 5000.00 |
| 2 | Priya | 3000.00 |
+--------+--------+---------+
Lesson: the DBMS gives you the tool for atomicity (
ROLLBACK, automatic undo on crash or disconnect), but your application must decide to roll back when a step fails. In application code this is the familiartry { ... commit } catch { rollback }pattern.
C — Consistency
Consistency: a transaction, executed alone on a consistent database, takes it to another consistent database state.
A database is consistent when it satisfies all the rules it is supposed to satisfy. These rules come in two kinds:
Rules the DBMS can check → primary keys, foreign keys,
NOT NULL,UNIQUE,CHECKconstraints, data types (see Integrity Constraints)Rules only the application knows → "a transfer must not change the total money", "seats booked must not exceed seats available", "an order's total equals the sum of its items"
For the transfer, the important business rule is:
A + B before the transaction = A + B after the transaction
5000 + 3000 = 8000 = 4000 + 4000 = 8000
Who is responsible for consistency?
This is the property that confuses people most, because it is shared:
The application programmer must write each transaction correctly. If the code subtracts ₹1,000 from A but adds ₹100 to B, no DBMS can save you. The DBMS will happily commit a wrong program.
The DBMS enforces the declared integrity constraints and rejects statements that break them (as our
CHECKdid above).The other three properties (A, I, D) make sure that correct transactions stay correct even with crashes and concurrency.
Notice that during a transaction the database can be temporarily inconsistent. Between write(A) and write(B), the total is ₹7,000. That is allowed. Atomicity makes sure nobody is left with that state after a failure, and isolation makes sure no other transaction sees it.
Do not confuse the "C" in ACID with the "C" in the CAP theorem. In CAP, consistency means every replica returns the latest value (a property of distributed copies). In ACID, it means integrity rules hold. They are different ideas that share a name. (CAP is covered in Distributed Databases & CAP Theorem.)
I — Isolation
Isolation: even though many transactions run at the same time, each transaction should behave as if it were running alone. The intermediate state of one transaction should not be visible to others.
Why do we run transactions concurrently at all?
It would be simplest to run one transaction at a time. But a real database serves hundreds or thousands of users. Running them one by one would be painfully slow, because:
While one transaction waits for the disk or the network, the CPU sits idle. Overlapping transactions keeps the CPU and disks busy, which improves throughput.
A short transaction would have to wait behind a long one. Interleaving reduces average response time.
So databases run transactions concurrently, and isolation is about doing that safely.
What goes wrong without isolation?
Suppose transaction T1 is in the middle of our transfer. It has written A = 4000 but has not yet written B. At this moment, transaction T2 calculates the bank's total money:
T1 T2
read(A) A = 5000
A := A - 1000
write(A) A = 4000
read(A) sees 4000
read(B) sees 3000
total = 7000 (wrong!)
read(B)
B := B + 1000
write(B) B = 4000
commit
T2 reports ₹7,000, an amount that never existed in any correct state of the database. T2 saw T1's half-finished work.
The ideal goal of isolation is that the final result of running transactions concurrently is the same as running them one after another in some order. This idea is called serializability, and it is the subject of the chapters Schedules & Serializability and Conflict & View Serializability.
The DBMS component responsible for isolation is the concurrency-control manager. It uses techniques such as locking (Lock-Based Concurrency Control (2PL)), timestamps (Timestamp Ordering Protocol) and, in engines like InnoDB, multi-version reads (keeping older versions of rows so readers can see a stable snapshot).
Isolation levels: trading safety for speed
Full isolation has a cost: transactions sometimes have to wait for one another. So SQL lets you choose a weaker isolation level when your application can tolerate certain anomalies. The SQL standard defines four levels in terms of three "read phenomena":
Dirty read → reading a value written by a transaction that has not committed yet (it may still roll back)
Non-repeatable read → reading the same row twice and getting different values, because another transaction committed a change in between
Phantom read → running the same
WHEREquery twice and getting a different set of rows, because another transaction inserted or deleted matching rows
Isolation level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
READ UNCOMMITTED | Possible | Possible | Possible |
READ COMMITTED | Not possible | Possible | Possible |
REPEATABLE READ | Not possible | Not possible | Possible (per the standard) |
SERIALIZABLE | Not possible | Not possible | Not possible |
This table describes what the standard allows at each level. Real engines often give more than the minimum. For example, InnoDB's default is REPEATABLE READ (we saw it above), and its plain SELECTs read from a consistent snapshot taken at the first read, so in practice a repeated plain SELECT inside one transaction does not see newly inserted rows either. The default in PostgreSQL, Oracle and SQL Server is READ COMMITTED.
Seeing a dirty read in MySQL
Let us run two sessions at the same time. Session T1 debits ₹1,000 from Aarav, waits a few seconds, and then rolls back. While T1 is waiting, session T2 reads Aarav's balance, once at READ UNCOMMITTED and once at READ COMMITTED.
-- T1
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE acc_no = 1;
-- ... waits 3 seconds ...
ROLLBACK;
-- T2 (runs while T1 is waiting)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT balance FROM accounts WHERE acc_no = 1;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE acc_no = 1;
What T2 saw:
READ UNCOMMITTED:
+---------+
| balance |
+---------+
| 4000.00 |
+---------+
READ COMMITTED:
+---------+
| balance |
+---------+
| 5000.00 |
+---------+
And after T1 rolled back:
+---------+
| balance |
+---------+
| 5000.00 |
+---------+
At READ UNCOMMITTED, T2 saw ₹4,000, a value that was later cancelled and so never really existed. That is a dirty read. At READ COMMITTED, T2 saw only the last committed value, ₹5,000.
D — Durability
Durability: once a transaction has committed, its changes are permanent. They must survive any later failure such as a crash or power cut.
When the bank app shows "Transfer successful", you expect that message to stay true even if the bank's server loses power one millisecond later.
Why is this hard?
As we noted earlier, a write(X) usually changes a page in a memory buffer, not on disk. Writing every changed page to disk at every commit would be very slow, because pages are scattered all over the disk.
How the DBMS achieves durability
Instead of flushing all data pages, the DBMS writes a small, sequential log record describing the change ("T1 changed A from 5000 to 4000") and forces the log to stable storage before it reports the commit as successful. The data pages themselves can be written later at a convenient time.
COMMIT requested
↓
Write log records (including "T1 committed") to disk <-- must finish first
↓
Tell the user "committed"
↓
(later) Write the changed data pages to disk
If the system crashes before the data pages reach disk, the recovery manager redoes the committed changes from the log on restart. This rule of writing the log before the data is called Write-Ahead Logging (WAL); it is explained fully in Recovery & Logging.
In InnoDB, this log is the redo log. The setting innodb_flush_log_at_trx_commit controls how strictly it is flushed:
SELECT @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
| 1 |
+----------------------------------+
The default value 1 means the log is written and flushed to disk at every commit, which gives full durability. Values 0 and 2 are faster, but up to about a second of committed transactions can be lost in a crash (with 2, only if the operating system or machine crashes, not just MySQL). This is a real-world example of trading durability for speed.
Note: durability protects against system failures. It cannot protect you if the disk itself is destroyed. For that, databases rely on backups and replication to other machines.
Who Ensures What? (Summary Table)
Property | Main responsibility | Typical mechanism |
|---|---|---|
Atomicity | Recovery manager | Undo information (undo log), rollback |
Consistency | Application programmer + DBMS integrity checks | Correct transaction logic, constraints, triggers |
Isolation | Concurrency-control manager | Locking (2PL), timestamps, multi-versioning |
Durability | Recovery manager | Write-ahead (redo) log, forced at commit; backups |
How the Four Properties Fit Together
It helps to see that the properties support one another:
Correct transaction logic (programmer)
+
Integrity constraints (DBMS)
↓
CONSISTENCY
↑ ↑ ↑
ATOMICITY ISOLATION DURABILITY
no half-done work no interference no lost commits
after a failure between users after a crash
If a transaction is correct when it runs alone, then atomicity, isolation and durability together make sure it stays correct when it runs with failures and with other transactions. That is why consistency is often described as the goal, and A, I and D as the means.
Common Mistakes
Thinking a failed statement cancels the whole transaction. In MySQL, most errors undo only the failing statement. Your code must call
ROLLBACK.Forgetting autocommit. Two separate
UPDATEs withoutSTART TRANSACTIONare two separate transactions. A crash between them leaves a half-done transfer.Saying "the DBMS guarantees consistency" with no qualification. It only enforces declared constraints. A buggy transaction that keeps all constraints can still break business rules.
Mixing up atomicity and isolation. Atomicity is about failures (all or nothing). Isolation is about other concurrent transactions (no peeking at half-done work). A single-user system still needs atomicity.
Running DDL inside a transaction and expecting rollback. In MySQL, statements like
CREATE TABLE,ALTER TABLEandDROP TABLEcause an implicit commit, so they cannot be rolled back, and they also commit whatever was pending before them.Confusing ACID consistency with CAP consistency. Same word, different meaning.
Interview Questions
1. What is a transaction?
A sequence of operations treated as one logical unit of work. Either all of its changes are made permanent (commit) or none are (rollback).
2. Explain ACID with one example.
For a transfer from A to B: Atomicity means both debit and credit happen or neither does; Consistency means the total money is unchanged; Isolation means no other transaction sees the debit without the credit; Durability means once "success" is shown, a crash cannot undo it.
3. Which ACID property is the programmer's responsibility?
Consistency, mainly. The DBMS enforces declared constraints, but only the programmer can make sure each transaction's logic preserves the business rules.
4. How does a DBMS implement atomicity and durability?
Through logging. Undo information lets it roll back uncommitted changes (atomicity). A redo log forced to disk before commit lets it redo committed changes after a crash (durability).
5. Why not just run transactions one at a time?
Throughput and response time. Concurrency keeps the CPU and disks busy and stops short transactions from waiting behind long ones. Isolation lets us get that benefit safely.
6. What is a dirty read?
Reading a value written by a transaction that has not yet committed. If that transaction rolls back, the reader has used a value that never officially existed.
7. What is MySQL InnoDB's default isolation level?
REPEATABLE READ. PostgreSQL, Oracle and SQL Server default to READ COMMITTED.
8. Is the database allowed to be inconsistent during a transaction?
Yes, temporarily. Between the debit and the credit, the total is wrong. ACID only requires consistency at transaction boundaries, and isolation hides the temporary state from others.
Summary
A transaction is a logical unit of work that ends in COMMIT (make permanent) or ROLLBACK (undo everything).
The DBMS sees a transaction as a series of read(X) and write(X) operations, written as
R1(A),W1(A),C1,A1.Atomicity → all or nothing, using undo information.
Consistency → valid state to valid state; shared between programmer and DBMS.
Isolation → concurrent transactions behave as if run alone; isolation levels let you trade strictness for speed.
Durability → committed changes survive crashes, using a write-ahead redo log.
In MySQL, autocommit is on by default, and a failing statement does not roll back the whole transaction by itself.
In the next chapter, Transaction States, we will follow a single transaction through its life, from the moment it starts until it commits or aborts.