Chapter 26 of 41

Transactions & ACID Properties

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 X from the database into a local variable of the transaction

  • write(X) → copy the value of the local variable back into data item X in 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 familiar try { ... 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, CHECK constraints, 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 CHECK did 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 WHERE query 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 without START TRANSACTION are 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 TABLE and DROP TABLE cause 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.