Lesson 26: Transactions and ACID Properties¶
All-or-nothing units of work, and how the DBMS guarantees them
About this lesson
Course: Database Management Systems
Unit 6: Data Storage and Indexes · 4 Lecture Hours
Lesson duration: 1 lecture
Reading: Elmasri & Navathe, Ch. 20
Learning Outcomes
- Define a transaction and its primitive operations.
- State and explain the four ACID properties, and name the component responsible for each.
- Draw and explain the transaction state diagram.
- Explain write-ahead logging and how it delivers atomicity and durability.
- Classify the kinds of failure a DBMS must survive.
26.1 What a Transaction Is¶
Transaction
A transaction is a logical unit of database processing — an indivisible sequence of operations that takes the database from one consistent state to another.
It is indivisible in the strict sense: either all its operations take effect, or none does.
The canonical example is a bank transfer of ₹5,000 from account A to account B:
T: read(A)
A := A - 5000
write(A)
read(B)
B := B + 5000
write(B)
These six steps are one logical action — "transfer money" — even though they touch two accounts. If the system crashes after write(A) but before write(B), ₹5,000 has vanished. The transaction concept exists to make that impossible.
The primitive operations¶
| Operation | Meaning |
|---|---|
read_item(X) |
Read database item X into a program variable. Involves finding the block, reading it into a buffer if not already there, and copying the value. |
write_item(X) |
Write the program variable's value back into item X in the buffer. The buffer block is written to disk later. |
BEGIN_TRANSACTION |
Mark the start. |
COMMIT |
Signal successful completion; all changes are to be made permanent. |
ROLLBACK / ABORT |
Signal failure; all changes must be undone. |
The gap between write and disk
write_item(X) writes to the buffer, not to disk. The buffer manager (Unit 1, Lesson 4) decides later when to flush the block.
This is the source of both problems that recovery must solve: a committed transaction's changes may not yet be on disk when the system crashes (needing redo), and an uncommitted transaction's changes may already have been flushed (needing undo).
26.2 The ACID Properties¶
ACID
A — Atomicity. A transaction is an indivisible unit: either all its operations are reflected in the database, or none are. Guaranteed by the recovery manager.
C — Consistency preservation. A transaction executed alone on a consistent database leaves it consistent — every integrity constraint still holds. Guaranteed jointly by the programmer (who writes a correct transaction) and the DBMS (which enforces declared constraints).
I — Isolation. A transaction's updates must not be visible to other transactions until it commits; concurrent execution must yield the same result as some serial execution. Guaranteed by the concurrency-control manager.
D — Durability (permanence). Once a transaction commits, its changes survive any subsequent failure. Guaranteed by the recovery manager, via the log.
Each property, with its failure mode¶
Atomicity. Without it, a crash between the debit and the credit destroys ₹5,000. The recovery manager guarantees it by undoing the partial work of any transaction that had not committed at the time of the crash.
Consistency. The transfer preserves the rule "total money is unchanged". Note this is not something the DBMS can infer — it enforces the constraints you declare (CHECK, FOREIGN KEY), but the logic of a correct transfer is the programmer's responsibility. A transaction that debits without crediting is atomic, isolated and durable, and still wrong.
Isolation. Without it, concurrent transactions interfere. Two clerks reading the same "last seat" both see it free and both admit a student — the lost update of Unit 1, Lesson 1. Isolation makes the outcome equal to some serial order.
Durability. Once the user is told "transfer complete", the money must stay transferred even if the power fails one millisecond later. The log record is forced to disk before the commit is acknowledged, so the change can be redone on restart.
Which component provides which
| Property | Responsible component | Mechanism |
|---|---|---|
| Atomicity | Recovery manager | Undo using the log |
| Consistency | Programmer + integrity subsystem | Correct transaction logic + declared constraints |
| Isolation | Concurrency-control manager | Locking, serialisable schedules (Lesson 4) |
| Durability | Recovery manager | Redo using the write-ahead log |
This table is a standard examination question. Note that atomicity and durability are two sides of the same mechanism — the log — and that consistency is the only one the DBMS cannot deliver by itself.
26.3 Transaction States¶
BEGIN
|
v
+------------+ read/write operations
+-----| ACTIVE |<--------+
| +-----+------+ |
| | last operation executed
failure | v
| +----------------------+
| | PARTIALLY COMMITTED | changes still in buffers;
| +------+--------+------+ log records being forced
| | |
| commit | | failure (e.g. cannot force the log)
| v |
| +------------+ |
| | COMMITTED | | changes are PERMANENT
| +-----+------+ |
| | |
v | v
+------------+ | +------------+
| FAILED |<--+---| FAILED |
+-----+------+ +-----+------+
| roll back (UNDO all changes)
v
+------------+
| ABORTED |----> restart the transaction, or kill it
+-----+------+
|
v
TERMINATED <----- also reached from COMMITTED
Figure 3.1 — The transaction state diagram.
| State | Meaning |
|---|---|
| Active | The initial state; the transaction is executing its operations. |
| Partially committed | The final operation has executed, but the changes may still be only in buffers. The log records must be forced to disk before the commit can be confirmed. |
| Committed | Successful completion recorded permanently. The changes can never now be undone. |
| Failed | Normal execution can no longer proceed — a constraint violation, a deadlock, a hardware error, or an explicit ROLLBACK. |
| Aborted | The transaction has been rolled back and the database restored to its state before the transaction began. |
| Terminated | The transaction has left the system, whether committed or aborted. |
Why 'partially committed' exists as a separate state
It marks the window in which the transaction has finished computing but its effects are not yet durable — the log records are still being written.
A crash in this window means the transaction must be rolled back, even though it executed every operation successfully, because it was never promised to the user. Only after the commit log record is safely on disk does the transaction become committed, and only then is the "transaction complete" message sent.
Once committed, a transaction can never be undone. The only remedy for a mistaken committed transaction is a compensating transaction — a new transaction that reverses its effect.
26.4 Failures and the Log¶
Kinds of failure¶
| Category | Examples | Data lost |
|---|---|---|
| Transaction failure | Logical error, constraint violation, deadlock, explicit ROLLBACK, bad input |
None — one transaction is undone |
| System crash | Power failure, operating-system crash, main-memory failure | Volatile storage (buffers) lost; disk intact |
| Media failure | Disk head crash, disk controller failure | Non-volatile storage lost |
Transaction and system failures are handled by the log. Media failure is handled by backups plus the archived log.
The system log¶
Log (journal)
A sequential file on stable storage recording every change made by every transaction, so that the changes can be undone or redone.
Log record types:
| Record | Meaning |
|---|---|
[start_transaction, T] |
T has begun. |
[write_item, T, X, old_value, new_value] |
T changed X; both values are recorded. |
[read_item, T, X] |
T read X (optional; needed only for some protocols). |
[commit, T] |
T completed successfully. |
[abort, T] |
T was rolled back. |
[checkpoint] |
All buffers were flushed at this point. |
Recording both the old and the new value is what makes the log usable in both directions: the old value supports UNDO, the new value supports REDO.
Write-Ahead Logging¶
The WAL protocol — two rules
Rule 1 (for undo). The log record for a change must be forced to disk before the changed data block is written to disk. Otherwise: a crash could leave a modified block on disk with no log record of its old value, so the change could never be undone.
Rule 2 (for redo). All log records for a transaction, including its commit record, must be forced to disk before the transaction is reported as committed. Otherwise: a crash could lose a change the user was told had succeeded, so it could never be redone.
TIME ---->
T: write(A) write(B) COMMIT
| | |
v v v
LOG: [T,A,old,new] [T,B,old,new] [commit,T]
| | |
+--- forced to disk BEFORE --------+ the data blocks are written
and BEFORE the user is told
"committed"
Only after [commit,T] is safely on disk may the DBMS answer "done".
Recovery after a crash¶
On restart the recovery manager scans the log from the last checkpoint:
- Build an undo list of transactions with a
start_transactionrecord but nocommitrecord, and a redo list of those with acommitrecord. - UNDO — traverse the log backwards, restoring the
old_valuefor every write by a transaction on the undo list. - REDO — traverse the log forwards, applying the
new_valuefor every write by a transaction on the redo list.
The result: every committed transaction's effects are present, and no uncommitted transaction's effects remain. That is precisely atomicity and durability delivered together.
Checkpoints
Periodically the DBMS suspends new transactions briefly, flushes all modified buffers to disk, and writes a [checkpoint] record to the log.
Recovery then needs to examine the log only from the last checkpoint onward, rather than from the beginning of time. Transactions that committed before the checkpoint need no redo, since their changes are known to be on disk.
26.5 Transactions in SQL¶
START TRANSACTION; -- or BEGIN
UPDATE ACCOUNT SET Balance = Balance - 5000 WHERE AccNo = 'A';
UPDATE ACCOUNT SET Balance = Balance + 5000 WHERE AccNo = 'B';
-- verify before committing
SELECT AccNo, Balance FROM ACCOUNT WHERE AccNo IN ('A','B');
COMMIT; -- make permanent
-- ROLLBACK; -- or undo everything
Savepoints allow partial rollback:
START TRANSACTION;
INSERT INTO STUDENT VALUES ('X9001','Test Student','2003-01-01');
SAVEPOINT after_student;
INSERT INTO ADMISSION VALUES ('X9001','BADSID','2019-07-15'); -- fails
ROLLBACK TO SAVEPOINT after_student; -- undo only the admission
COMMIT; -- the student insert survives
Autocommit
By default MySQL runs in autocommit mode: every individual statement is its own transaction, committed immediately. So
UPDATE ACCOUNT SET Balance = Balance - 5000 WHERE AccNo = 'A';
UPDATE ACCOUNT SET Balance = Balance + 5000 WHERE AccNo = 'B';
is two separate transactions, and a crash between them loses the money — exactly the atomicity failure we set out to prevent.
START TRANSACTION suspends autocommit until COMMIT or ROLLBACK. Use it whenever two or more statements must succeed or fail together. (SET autocommit = 0; changes the default for the session.)
Note also that DDL statements cause an implicit commit in MySQL: running CREATE TABLE inside a transaction silently commits everything before it.
26.6 Solved Questions¶
Solved Question 1
Q. Define a transaction. Explain the four ACID properties with the bank-transfer example, naming the component responsible for each.
A. A transaction is a logical unit of database processing — an indivisible sequence of operations taking the database from one consistent state to another.
Consider transferring ₹5,000 from account A to account B:
read(A); A := A − 5000; write(A); read(B); B := B + 5000; write(B)
Atomicity — all or nothing. If the system crashes after write(A) but before write(B), ₹5,000 has vanished. Atomicity guarantees that such a partial execution is undone entirely on restart. Component: the recovery manager, using the old values in the log.
Consistency preservation. The rule "total money across all accounts is unchanged" holds before and after. The transaction may violate it momentarily — between the two writes the total is ₹5,000 short — but that intermediate state is never visible to anyone else (which is isolation's job) and never final (atomicity's job). Component: the programmer, who must write a transfer that debits and credits equal amounts, together with the integrity subsystem, which enforces declared constraints such as CHECK (Balance >= 0).
Isolation. If another transaction computed the bank's total assets while ours sat between the two writes, it would report ₹5,000 too little. Isolation ensures concurrent transactions produce the same result as some serial order. Component: the concurrency-control manager.
Durability. Once the customer is told "transfer complete", the money must stay transferred even if the power fails immediately afterwards. Component: the recovery manager, which forces the commit log record to disk before acknowledging, so the change can be redone.
Note that consistency is the only property the DBMS cannot supply by itself — a transaction that debits without crediting satisfies A, I and D perfectly and is still wrong.
Solved Question 2
Q. Draw the transaction state diagram and explain each state. Why is "partially committed" a separate state?
A. (Diagram: Figure 3.1.)
Active — the initial state; the transaction is executing its reads and writes.
Partially committed — the last operation has executed, but the changes may still be in volatile buffers and the log records may not yet be on disk.
Committed — successful completion has been recorded permanently. The changes can never now be undone.
Failed — normal execution cannot proceed: a constraint violation, a deadlock victim, a hardware error, or an explicit ROLLBACK.
Aborted — the transaction has been rolled back and the database restored to its pre-transaction state. The transaction may then be restarted or killed.
Terminated — the transaction has left the system, from either committed or aborted.
Why "partially committed" is separate. It marks the interval in which the transaction has finished computing but its effects are not yet durable. Two things are still pending: dirty buffer blocks may not have reached disk, and the [commit, T] log record may not yet be forced.
If the system crashes in this window, the transaction must be rolled back — even though every operation succeeded — because it was never promised to the user, and the write-ahead rule may not yet have been satisfied. The transition to committed occurs only when the commit log record is safely on stable storage; only then is the user told "done".
Without this state the model could not express the difference between "finished executing" and "guaranteed permanent" — which is exactly the distinction durability depends on.
Solved Question 3
Q. What is a log? What does a write_item log record contain, and why? State the write-ahead logging protocol and explain what each rule prevents.
A. The log (journal) is a sequential file on stable storage recording every change made by every transaction, so that changes can be undone or redone after a failure.
A write_item record is [write_item, T, X, old_value, new_value]. It records both values because the log must serve two opposite purposes:
- the old value allows UNDO — restoring
XwhenTdid not commit; - the new value allows REDO — reapplying
T's change whenTdid commit but the data block had not reached disk.
The write-ahead logging protocol — two rules.
Rule 1 (undo rule). The log record describing a change must be forced to disk before the corresponding data block is written to disk. Prevents: a crash leaving a modified block on disk with no record of its old value. The change could then never be undone, breaking atomicity.
Rule 2 (redo rule). All of a transaction's log records, including [commit, T], must be forced to disk before the transaction is reported committed.
Prevents: a crash losing a change the user was told had succeeded. Without the log record there is nothing to redo, breaking durability.
Recovery. On restart, scan the log from the last checkpoint; build the undo list (started but not committed) and the redo list (committed). Traverse backwards restoring old values for the undo list, then forwards applying new values for the redo list. The outcome is exactly atomicity and durability: every committed effect present, no uncommitted effect remaining.
Solved Question 4
Q. Classify the kinds of failure a DBMS must survive, and state how each is handled.
A.
1. Transaction failure (local). One transaction cannot complete: a logical error (division by zero, bad input), a constraint violation, a deadlock in which it is chosen as the victim, or an explicit ROLLBACK.
Effect: only that transaction is affected; the database and other transactions are untouched.
Handling: the recovery manager undoes the transaction's writes using the old values in the log, restoring the pre-transaction state. The transaction may then be restarted.
2. System crash (soft failure). Power failure, operating-system crash, or main-memory failure. Effect: the contents of volatile storage — the buffer pool — are lost. Disk contents survive. Some committed changes may not yet have been flushed; some uncommitted changes may already have been. Handling: restart recovery from the log. Scan from the last checkpoint, undo every transaction without a commit record, redo every transaction with one.
3. Media failure (hard failure). A disk head crash or controller failure destroys part of the non-volatile storage. Effect: the database itself is damaged; the log alone cannot help, since it may be on the failed device. Handling: restore the most recent backup (dump), then redo all committed transactions from the archived log kept on separate media. This is why backups and the log must be stored on different devices, and why backups must be tested.
A fourth category sometimes listed: concurrency-control enforcement — the scheduler aborts a transaction to preserve serialisability or break a deadlock. This is really a form of transaction failure, and it is handled identically.
The general principle: the DBMS assumes disk is reliable and memory is not (categories 1–2, handled by the log); when even that assumption fails (category 3), it falls back on redundancy held elsewhere.
Solved Question 5
Q. What is a checkpoint? Why is it needed? Describe what happens at one.
A. A checkpoint is a point at which the DBMS guarantees that all modified buffer blocks have been written to disk, recorded by a [checkpoint] entry in the log.
Why needed. Without checkpoints, recovery after a crash would require scanning the log from the very beginning, because the recovery manager could not know which committed changes had already reached disk. For a system running for months this is impossible — recovery would take longer than the outage it was repairing. It would also mean redoing millions of changes already safely on disk.
What happens at a checkpoint:
- Suspend the start of new transactions (and briefly suspend execution of active ones).
- Force all log records currently in main memory to disk.
- Write all modified (dirty) buffer blocks to disk.
- Write a
[checkpoint]record — usually listing the currently active transactions — to the log, and force it to disk. - Resume normal execution.
Effect on recovery. The recovery manager scans the log backwards only as far as the last checkpoint. Any transaction that committed before the checkpoint needs no redo, because step 3 guarantees its changes are on disk. Only transactions active at or after the checkpoint need attention.
The trade-off. Checkpoints cost a brief pause and a burst of disk writes. Frequent checkpoints make recovery fast but slow normal operation; infrequent ones do the reverse. Real systems use fuzzy checkpointing, which avoids suspending transactions by recording the set of dirty pages rather than flushing them all synchronously.
Solved Question 6
Q. Explain autocommit and why it is dangerous for a multi-statement operation. Show the correct SQL for a transfer, and explain savepoints.
A. In autocommit mode — the default in MySQL — every individual statement is its own transaction, committed immediately on completion.
Why this is dangerous. A logical operation spanning several statements is then not atomic:
UPDATE ACCOUNT SET Balance = Balance - 5000 WHERE AccNo = 'A'; -- committed
UPDATE ACCOUNT SET Balance = Balance + 5000 WHERE AccNo = 'B'; -- separate txn
These are two transactions. If the second fails — a constraint violation, a crash, a lost connection — the first has already committed and cannot be undone. ₹5,000 has been destroyed, and the database is in a state no single constraint can detect as wrong. This is precisely the atomicity failure the transaction concept exists to prevent.
Correct form:
START TRANSACTION;
UPDATE ACCOUNT SET Balance = Balance - 5000 WHERE AccNo = 'A';
UPDATE ACCOUNT SET Balance = Balance + 5000 WHERE AccNo = 'B';
COMMIT; -- both, or (on ROLLBACK) neither
START TRANSACTION suspends autocommit until COMMIT or ROLLBACK, so the two updates become one atomic unit.
Savepoints mark an intermediate point to which a transaction may be partially rolled back, without abandoning the whole transaction:
START TRANSACTION;
INSERT INTO STUDENT VALUES ('X9001','Test Student','2003-01-01');
SAVEPOINT after_student;
INSERT INTO ADMISSION VALUES ('X9001','BADSID','2019-07-15'); -- fails
ROLLBACK TO SAVEPOINT after_student; -- undo only the admission
INSERT INTO ADMISSION VALUES ('X9001','CS01','2019-07-15'); -- retry
COMMIT; -- the student insert survives
Savepoints are useful for long transactions in which one step may fail recoverably. A caution specific to MySQL: DDL statements such as CREATE TABLE cause an implicit commit, silently ending the transaction and committing everything before them — so never mix DDL into a transaction you intend to be able to roll back.
26.7 Exercises¶
A. Short answer
- Define a transaction.
- List the ACID properties and the component responsible for each.
- Name the six transaction states.
- What does a
write_itemlog record contain, and why both values? - State the two rules of write-ahead logging.
- What is a checkpoint, and why is it needed?
- What is autocommit?
B. Long answer
- Explain the four ACID properties with the bank-transfer example. Which cannot be guaranteed by the DBMS alone, and why?
- Draw and explain the transaction state diagram, justifying the existence of the "partially committed" state.
- Explain the write-ahead logging protocol. State precisely what each rule prevents and which ACID property it protects.
- Classify the kinds of failure and state how each is handled.
- Describe the recovery procedure after a system crash, explaining the undo and redo lists.
C. Application
- Write the SQL for transferring ₹10,000 from account A to B as a single atomic transaction, including a check that A has sufficient balance.
- Write a transaction that admits a student to a course and increments an enrolment counter, so that both succeed or neither does.
- Given this log after a crash, determine which transactions must be undone and which redone:
[start, T1] [write, T1, A, 100, 150] [start, T2] [write, T2, B, 200, 250] [commit, T1] [start, T3] [write, T3, C, 300, 350] [write, T2, D, 400, 450] [commit, T3] <<< CRASH >>> - Repeat question 15 assuming a
[checkpoint]record appeared immediately after[commit, T1]. Which log records must now be examined? - A student runs two
UPDATEstatements in MySQL withoutSTART TRANSACTION, and the connection drops between them. Describe the resulting state and how it could have been prevented. - Explain what a compensating transaction is, and why it is the only remedy for a mistaken committed transaction.
D. Analysis
- Explain why atomicity and durability are described as "two sides of the same mechanism".
- Argue whether the following are transactions, and state the ACID property most at risk in each: a. Booking a railway ticket. b. Running a monthly report. c. Adding a column to a table. d. Transferring a student between two courses.
- Explain why a committed transaction can never be undone, and what this implies for application design.
- Explain the trade-off in checkpoint frequency.
- Why must the backup and the archived log be stored on different devices?
E. Objective — True or False with reason
- A transaction always changes the database.
- Consistency is guaranteed entirely by the DBMS.
- A committed transaction can be rolled back.
- The log records only new values.
- Checkpoints are needed for atomicity.
- In autocommit mode, two
UPDATEstatements form one transaction. - Durability protects against media failure.
Answers to Section E
- False. A read-only transaction (a report) changes nothing but is still a transaction, needing isolation and consistency.
- False. The DBMS enforces declared constraints; the logic of a correct transaction is the programmer's responsibility.
- False. Never. The only remedy is a compensating transaction that reverses its effect.
- False. It records both old and new values — old for undo, new for redo.
- False. They are a performance optimisation, bounding how much log must be scanned. Atomicity comes from the log itself.
- False. Each statement is its own transaction, committed immediately — which is exactly the danger.
- Partly. Durability guarantees committed changes survive system failure via the log. Surviving media failure additionally requires backups plus an archived log on separate media.
Lesson Summary
- A transaction is an indivisible logical unit taking the database from one consistent state to another.
- ACID: Atomicity (recovery manager, undo), Consistency (programmer + constraints), Isolation (concurrency control), Durability (recovery manager, redo).
- States: active → partially committed → committed, or active → failed → aborted. "Partially committed" is the window before durability is guaranteed.
- The log records old and new values. WAL: log before data; commit record before acknowledging.
- Recovery = undo the uncommitted, redo the committed, scanning from the last checkpoint.
- Autocommit makes each statement its own transaction — use
START TRANSACTIONwhenever several statements must be atomic.