Chapter 27 of 41

Transaction States

Think about ordering food online. Your order goes through stages: placed, being prepared, out for delivery, delivered, or sometimes cancelled. At any moment, the order is in exactly one of these stages, and it can only move between them in certain ways. A delivered order cannot go back to "being prepared".

A database transaction has a similar life cycle. From the moment it starts until it finishes, it passes through a small set of well-defined states.

Transaction states describe the stages a transaction goes through during its lifetime: Active, Partially Committed, Committed, Failed, Aborted and Terminated.

Knowing these states helps you understand when a transaction can still be cancelled, when its changes become permanent, and what the DBMS does after a failure. In the previous chapter, Transactions & ACID Properties, we saw what a transaction promises. Here we see how it gets there, step by step.


The State Diagram

Here is the complete picture. We will go through every box and every arrow below.

               last operation              commit info written
                  executed                  to stable storage
 +--------+  --------------->  +-------------+  --------------->  +-----------+
 | ACTIVE |                    |  PARTIALLY  |                    | COMMITTED |
 +--------+                    |  COMMITTED  |                    +-----------+
     |                         +-------------+                          |
     | error / abort                  |                                 |
     | request                        | failure before                  |
     v                                | commit completes                |
 +--------+  <------------------------+                                 |
 | FAILED |                                                             |
 +--------+                                                             |
     | rollback complete                                                |
     v                                                                  v
 +---------+                                                    +------------+
 | ABORTED |  ---------------------------------------------->  | TERMINATED |
 +---------+                                                    +------------+
     |
     +-->  restart as a NEW transaction,  or  kill

There are two main paths through this diagram:

  • Success path: Active → Partially Committed → Committed → Terminated

  • Failure path: Active (or Partially Committed) → Failed → Aborted → Terminated


1. Active State

A transaction enters the active state as soon as it starts, and stays there while it executes its reads, writes and computations.

START TRANSACTION;                                         <-- becomes ACTIVE
UPDATE accounts SET balance = balance - 1000 WHERE acc_no = 1;   (still ACTIVE)
UPDATE accounts SET balance = balance + 1000 WHERE acc_no = 2;   (still ACTIVE)

In this state:

  • The transaction's changes are not permanent. They can still be undone.

  • The DBMS is keeping undo information for every change, in case the transaction has to roll back.

  • Under normal isolation levels, other transactions cannot see these changes yet.

We can actually observe an active transaction in MySQL. While one session is in the middle of a transfer, another session can look at InnoDB's list of running transactions:

-- Session 1
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE acc_no = 1;
-- ... still working, no COMMIT yet ...

-- Session 2
SELECT trx_state, trx_operation_state, trx_rows_modified, trx_isolation_level
FROM information_schema.innodb_trx;
+-----------+---------------------+-------------------+---------------------+
| trx_state | trx_operation_state | trx_rows_modified | trx_isolation_level |
+-----------+---------------------+-------------------+---------------------+
| RUNNING   | NULL                |                 1 | REPEATABLE READ     |
+-----------+---------------------+-------------------+---------------------+

InnoDB reports the transaction as RUNNING with one modified row. That is our textbook active state. (On a busy server you would also see other sessions' transactions in this list; here session 1 was the only one running.) Once session 1 commits, its row disappears from innodb_trx. Counting the rows after the commit gives:

SELECT COUNT(*) AS active_trx FROM information_schema.innodb_trx;
+------------+
| active_trx |
+------------+
|          0 |
+------------+

Note: innodb_trx uses its own state names (such as RUNNING, LOCK WAIT, ROLLING BACK, COMMITTING). They are implementation details of InnoDB, not the textbook names, but you can see how they map onto the theory.


2. Partially Committed State

When the transaction has executed its final operation, it moves to the partially committed state.

Why "partially"? Because finishing the last statement is not the same as being permanent. At this moment:

  • All the work is done, but many of the changes may still be sitting in memory buffers, not on disk.

  • The DBMS still has to write the necessary log records (including a "commit" record) to stable storage.

  • Some systems also run final checks here, for example constraints declared as deferred (checked at commit rather than per statement, a feature available in PostgreSQL and Oracle but not in MySQL).

If something goes wrong now, for example the disk fails while the log is being written, or a deferred constraint check fails, the transaction can still move to the failed state. So a partially committed transaction is not safe yet.

Last statement done
       ↓
PARTIALLY COMMITTED
       ↓
Write log records + commit record to disk
       ↓
   succeeded? ──── no ───→ FAILED
       │
      yes
       ↓
   COMMITTED

3. Committed State

Once the DBMS has written enough information to stable storage that the changes can be recreated even after a crash, the transaction enters the committed state.

This is the point of no return:

  • The changes are now permanent (the Durability of ACID).

  • The changes become visible to other transactions (under isolation levels such as READ COMMITTED and above).

  • The transaction cannot be rolled back any more.

"But I made a mistake after committing!"

Suppose a bank wrongly committed a ₹1,000 transfer. It cannot "abort" that transaction now. Instead, it must run a new transaction that does the opposite, transferring ₹1,000 back. Such a transaction is called a compensating transaction.

T1 (committed):   A := A - 1000,  B := B + 1000
Compensating T2:  B := B - 1000,  A := A + 1000

A compensating transaction is ordinary business logic written by the application. The DBMS does not create it automatically, and other transactions may have already seen T1's effects before T2 runs.


4. Failed State

A transaction enters the failed state when the DBMS discovers that it cannot continue normally. Common reasons:

Reason

Example

Logical error inside the transaction

Bad input, division by zero, a constraint violation the program does not handle

Explicit user / application request

The program issues ROLLBACK, e.g. "insufficient balance"

System decision

Chosen as a deadlock victim, a lock-wait timeout, a concurrency-control protocol forcing an abort

System failure

Crash, power failure, disk failure while writing the log

A transaction can reach the failed state from active (most common) or from partially committed (failure while committing). It can never reach it from committed.

The failed state is short-lived. It simply means "this transaction must now be rolled back".


5. Aborted State

After failure, the DBMS rolls back the transaction: it uses the undo information to restore every data item the transaction changed back to its old value. When the rollback is complete, the transaction is in the aborted state.

At this point the database looks exactly as if the transaction had never started. That is how atomicity is preserved.

A real system-initiated abort in MySQL

Here two sessions transfer money in opposite directions at the same moment. T1 locks account 1 then wants account 2; T2 locks account 2 then wants account 1. Neither can continue, so InnoDB detects a deadlock and aborts one of them:

-- T1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE acc_no = 1;
-- (1 second later)
UPDATE accounts SET balance = balance + 100 WHERE acc_no = 2;
COMMIT;

-- T2, at the same time
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE acc_no = 2;
-- (1 second later)
UPDATE accounts SET balance = balance + 200 WHERE acc_no = 1;
COMMIT;

In our run (each session's statements fed to the mysql client as a script, so the error message mentions the line number of T2's second UPDATE), InnoDB picked T2 as the victim:

T2: ERROR 1213 (40001) at line 4: Deadlock found when trying to get lock; try restarting transaction

Final balances (starting from 5000 and 3000):

+--------+--------+---------+
| acc_no | holder | balance |
+--------+--------+---------+
|      1 | Aarav  | 4900.00 |
|      2 | Priya  | 3100.00 |
+--------+--------+---------+

T1's transfer of ₹100 committed. T2's first update (−200 on account 2) was undone, so T2 moved Active → Failed → Aborted and left no trace. Which session gets chosen as the victim depends on timing and on InnoDB's choice (it prefers to roll back the "smaller" transaction), so if you try this yourself, T1 might be the one aborted instead. Deadlocks are studied in detail in Deadlocks in DBMS.

What happens after abort? Restart or kill

Once a transaction is aborted, the system (or application) has two options:

  • Restart the transaction: run it again as a brand-new transaction (with a new identity). This makes sense when the failure was not the transaction's own fault: a deadlock, a timeout, a temporary hardware problem. Notice MySQL's message literally says "try restarting transaction".

  • Kill the transaction: give up on it. This is the right choice when the transaction itself is wrong, for example bad input or a logical error. Restarting would only fail again.

ABORTED
   ├── failure was external (deadlock, timeout, crash)  →  RESTART as new transaction
   └── failure was internal (bad data, logic error)      →  KILL

6. Terminated State

The terminated state simply means the transaction has left the system, whether it committed or aborted. Its resources (locks, memory, transaction-table entry) are released. Some textbooks do not draw this state separately and treat "committed" and "aborted" as the two final states. Both views are fine; the important idea is that every transaction ends in exactly one of commit or abort.


All Transitions at a Glance

From

To

When

(start)

Active

Transaction begins

Active

Partially committed

Last operation has executed

Active

Failed

Error, explicit rollback, system-initiated abort, crash

Partially committed

Committed

Commit information safely written to stable storage

Partially committed

Failed

Failure before the commit completes

Failed

Aborted

Rollback completed, database restored

Committed

Terminated

Transaction leaves the system

Aborted

Terminated

Transaction leaves the system (possibly restarted as a new one)

And the transitions that are impossible, which are popular in exam questions:

  • Committed → Failed / Aborted (a committed transaction cannot be undone)

  • Aborted → Committed (an aborted transaction cannot be resumed; it can only be restarted as a new one)

  • Active → Committed directly (it must pass through partially committed)


Walking a Transfer Through Its States

Let us trace our ₹1,000 transfer (A = 5000, B = 3000) in two scenarios.

Scenario 1: Everything works

Step                                   State                 A      B
-------------------------------------  -------------------   ----   ----
START TRANSACTION                      Active                5000   3000
read(A); A := A - 1000; write(A)       Active                4000   3000   (A not yet permanent)
read(B); B := B + 1000; write(B)       Active                4000   4000   (not yet permanent)
last statement done                    Partially committed   4000   4000
log + commit record forced to disk     Committed             4000   4000   (permanent now)
locks released                         Terminated

Scenario 2: Crash in the middle

Step                                   State                 A      B
-------------------------------------  -------------------   ----   ----
START TRANSACTION                      Active                5000   3000
read(A); A := A - 1000; write(A)       Active                4000   3000
*** system crashes ***                 Failed
recovery undoes write(A)               Aborted               5000   3000
                                       Terminated

In scenario 2, the half-done debit is erased. Nobody ever sees a total of ₹7,000 as a final state.


Observable External Actions

Some actions cannot be undone by the DBMS because they happen outside the database: printing a ticket, sending an SMS, dispensing cash from an ATM, calling a payment gateway.

If an ATM handed out cash while the transaction was still active, and the transaction then aborted, the bank could not "roll back" the cash from the customer's hand.

The rule is:

Perform external, irreversible actions only after the transaction has committed.

A common approach is to record the intention ("send SMS to user X") in the database as part of the transaction, and have a separate process perform it after the commit. If the transaction aborts, the intention record disappears with it, and nothing is sent.


Common Mistakes

  • Calling "partially committed" safe. It is not. A failure while writing the commit information still leads to abort.

  • Thinking an aborted transaction "resumes". A restarted transaction is a completely new transaction that runs from the beginning.

  • Believing a committed transaction can be rolled back. It can only be compensated by another transaction.

  • Forgetting that aborts are often system-initiated. Deadlock victims and timeouts are normal events in a busy database, so applications should be written to retry such transactions.


Interview Questions

1. Name the states of a transaction.

Active, Partially Committed, Committed, Failed, Aborted, and (in many textbooks) Terminated.

2. What is the difference between partially committed and committed?

Partially committed means the last statement has run, but the changes are not yet guaranteed to survive a crash. Committed means the commit information is on stable storage, so the changes are permanent.

3. Can a partially committed transaction fail?

Yes, for example if the system fails while writing the log or a deferred check fails. It then goes to the failed state and is rolled back.

4. What are the options after a transaction aborts?

Restart it as a new transaction (if the cause was external, like a deadlock) or kill it (if the cause was an internal logical error).

5. How do you undo a committed transaction?

You cannot roll it back. You run a compensating transaction that reverses its effects.

6. Why should an ATM dispense cash only after commit?

Because dispensing cash is an external action that the DBMS cannot undo. If the transaction aborted afterwards, the database and reality would disagree.


Summary

  • Active → executing; changes can still be undone.

  • Partially committed → last statement done; commit not yet guaranteed.

  • Committed → permanent; cannot be rolled back, only compensated.

  • Failed → cannot continue; must be rolled back.

  • Aborted → rolled back; database restored. Then restart (as a new transaction) or kill.

  • Terminated → the transaction has left the system.

  • External, irreversible actions should happen only after commit.

So far we have looked at one transaction at a time. In the next chapter, Schedules & Serializability, we will see what happens when several transactions run together, and how a DBMS decides whether their interleaving is safe.