Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 79 ยท Transactions and ACID

What Is a Transaction?

Learn what a database transaction is: a unit of work, BEGIN, COMMIT and ROLLBACK, the transaction state diagram, savepoints and autocommit, with live SQLite examples.

Moving 100 dollars from one bank account to another takes two changes: subtract from one balance and add to the other. If the machine crashes after the first change, 100 dollars have vanished. The two updates must succeed together or not happen at all. A transaction is the DBMS's answer: a group of operations that the database treats as one indivisible unit of work.

Everything in the next two chapters (ACID, schedules, locking, recovery) is about what a DBMS must do to make that promise true, even with crashes and many users at once. This lesson sets the vocabulary: what a transaction is, how you start and end one, which states it passes through, and how to undo part of it. If you only want the SQL syntax, Transactions in the SQL tutorial is the quick reference; here we focus on the model behind it.

A transaction is...

One unit of work Several reads and writes, treated as one.
All Every change becomes permanent.
Or nothing Every change is undone.

There is no visible "half done".

1 / 2

The transaction as the DBMS sees it

At the lowest level a DBMS sees a transaction as a sequence of reads and writes of database items, bracketed by a start and an end. A transfer of an amount x from account A to account B looks like this in the notation used by textbooks:

TEXT
T1:  read(A)
     A := A - x
     write(A)
     read(B)
     B := B + x
     write(B)
     commit

The program between the reads and writes is ordinary code; the database only watches the read and write operations and the final commit or abort. This tiny model is enough to reason about everything in later chapters, such as schedules and serializability.

Notice that after write(A) and before write(B) the database is inconsistent: the total money has changed. A transaction is allowed to pass through inconsistent states internally, as long as it ends in a consistent one and nobody else is allowed to see the middle. That requirement is spelled out in ACID Properties in Depth.

BEGIN, COMMIT and ROLLBACK

In SQL you mark the boundaries yourself:

  • BEGIN (or BEGIN TRANSACTION, START TRANSACTION in other engines) opens a transaction.
  • COMMIT ends it successfully. The DBMS guarantees that the changes are now durable and visible to others.
  • ROLLBACK ends it unsuccessfully. Every change made since BEGIN is undone, as if the transaction never ran.

The next example performs a transfer, looks at the intermediate balances from inside the transaction, and then rolls back. All the statements run in one script on a fresh in-memory database.

SQL A transfer that is rolled back

Press Run (or Ctrl + Enter) to see the result here.

The first query shows Ana at 400 and Bo at 300. The second, after ROLLBACK, shows 500 and 200 again: nothing was left behind. Replace ROLLBACK with COMMIT and the transfer becomes permanent. There is no way to commit "half" of a transaction.

Inside a transaction you see your own uncommitted changes. Whether other sessions can see them is controlled by the isolation level, covered in Isolation Levels and Concurrency Anomalies. In the sandbox on this page there is only one session, so we see only the single-user side of the story.

Autocommit

Most clients start in autocommit mode: when you run a statement without BEGIN, the DBMS wraps that one statement in its own implicit transaction and commits it immediately. So even UPDATE account SET balance = 0 is atomic: it either changes all the matching rows or none of them. Autocommit is convenient for one-off statements, and dangerous for multi-step work, because each step is committed separately and a crash between steps leaves the job half done.

The rule is simple: if two or more statements must succeed or fail together, put them inside one explicit transaction. Application drivers usually expose this as connection.begin() / commit() / rollback(), or turn autocommit off for you.

Transactions also end by themselves in some cases. If the connection drops or the server crashes before COMMIT, the DBMS rolls the transaction back during recovery (Failure Classification and Recovery Goals). If a statement violates a constraint, most engines abort the statement; whether the whole transaction stays usable differs by vendor, so check your engine's rules and handle the error in your code.

Transaction states

A transaction passes through a small set of states between start and finish.

flowchart LR
  A["Active"] --> P["Partially committed"]
  P --> C["Committed"]
  A --> F["Failed"]
  P --> F
  F --> B["Aborted"]

The life of a transaction: it ends either Committed or Aborted.

  • Active: the initial state. The transaction is running its reads and writes.
  • Partially committed: the last statement has run, but the changes may still be only in memory or in the log buffer. The DBMS is making them durable.
  • Committed: the changes are safe on stable storage. The effects can no longer be rolled back automatically. (To undo a committed transaction you must run a new compensating transaction.)
  • Failed: something went wrong (an error, a deadlock, a constraint violation or a crash) and normal execution cannot continue.
  • Aborted: the transaction has been rolled back and the database is back to its state before the transaction. From here the system may restart the transaction, if the failure was temporary (such as a deadlock), or kill it if the cause was a bug or bad input.

The two end states, committed and aborted, are called terminated. A good mental model is that a commit is a single point in time at which the whole transaction becomes real.

Savepoints: undoing part of a transaction

ROLLBACK throws away everything. Sometimes you only want to undo the last step and carry on. A savepoint marks a position inside the transaction that you can return to with ROLLBACK TO. The transaction stays open afterwards. This is useful for optional steps ("try to add the gift wrap, skip it if the stock is out").

SQL Savepoints: undo only the last step

Press Run (or Ctrl + Enter) to see the result here.

The final table has three rows: create order, choose shipping and confirm order. The gift-wrap work disappeared with ROLLBACK TO, but the transaction itself kept going until COMMIT. RELEASE removes a savepoint without undoing anything. Savepoints can be nested, and ROLLBACK TO also discards savepoints created after it.

Savepoints are part of the SQL standard and exist in PostgreSQL, MySQL, Oracle and SQL Server too, though the syntax for SQL Server differs (SAVE TRANSACTION). They are a way to build recoverable steps inside one atomic unit; they do not make a part of a transaction durable by themselves. Nothing is permanent until the outer COMMIT.

What a transaction should and should not contain

Keep transactions short and focused. A transaction that stays open holds locks or old row versions in many engines, which slows down everyone else (see Locks). Good habits:

  • Do not wait for user input between BEGIN and COMMIT.
  • Do not call slow external services (payment gateways, emails) inside a transaction if you can avoid it.
  • Do all reads that decide the outcome inside the transaction, so decisions are based on consistent data.
  • Always handle both paths: commit on success, rollback on error, so a failure never leaves a transaction dangling.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a DBMS guarantee about a transaction that has been rolled back?

    ROLLBACK ends the transaction unsuccessfully and every change it made is undone. Retrying is a separate decision made by the application or the system.
  2. 2. A client in autocommit mode runs two UPDATE statements without BEGIN. What happens if the server crashes between them?

    In autocommit each statement is its own transaction, committed as soon as it finishes. Work that must be atomic needs one explicit transaction.
  3. 3. In the transaction state diagram, which state follows 'partially committed' when everything goes well?

    After the final statement the transaction is partially committed. Once its changes are safely stored it becomes committed. A failure at that point would lead to Failed and then Aborted.
  4. 4. What does ROLLBACK TO my_savepoint do?

    ROLLBACK TO reverts only the changes made after the savepoint. The transaction continues and still needs its own COMMIT or ROLLBACK. RELEASE is the command that forgets a savepoint without undoing anything.

Summary

  • A transaction is a unit of work: a sequence of reads and writes that must happen entirely or not at all.
  • BEGIN starts it, COMMIT makes it permanent and ROLLBACK undoes it; autocommit wraps each lone statement in its own transaction.
  • A transaction may be inconsistent internally but must end in a consistent state, and others must not see the middle.
  • States: active, partially committed, committed, or failed then aborted; committed and aborted are the end states.
  • Savepoints let you undo part of a transaction (ROLLBACK TO) without ending it; RELEASE forgets one.
  • Keep transactions short and make every path end in a commit or a rollback.

Next: ACID Properties in Depth.