Skip to content
Tutorials Center
My learning

Topics

Lesson 62 of 125 ยท Changing Data

Transactions

Learn SQL transactions with live examples. Group statements with BEGIN, COMMIT and ROLLBACK, undo part of the work with savepoints, and understand what ACID means.

Many real tasks need more than one statement. Moving money between two accounts takes two updates. Placing an order inserts the order, inserts its items and lowers the stock. If the power fails, the app crashes or a rule is broken halfway through, you'd be left with money that vanished or an order with no items.

A transaction groups statements into a single unit of work that either happens completely or not at all. You start it, run your statements, and then either commit (make every change permanent) or roll back (undo every change as if nothing had happened). Transactions are what make databases trustworthy, and every serious application relies on them. In this lesson you'll use BEGIN, COMMIT, ROLLBACK and savepoints, and learn the four guarantees known as ACID.

Autocommit: every statement is a transaction

You've been using transactions all along without noticing. Unless you start one yourself, the database runs in autocommit mode: each statement is its own tiny transaction, committed the moment it succeeds. That's why a failing multi-row INSERT adds no rows at all (you saw it in the INSERT lesson): the statement's transaction was rolled back as a whole.

The trouble starts when one unit of work needs several statements. In autocommit mode, each of them commits on its own, so a failure in the second statement can't undo the first.

BEGIN, COMMIT and ROLLBACK

To group statements, wrap them:

SQL
BEGIN;          -- start the transaction
-- ...statements...
COMMIT;         -- make all changes permanent
-- or
ROLLBACK;       -- undo all changes since BEGIN

Here is the classic example: transferring 300 from Amara's account to Ben's. Both updates must happen, or neither:

SQL A committed transfer

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

Amara now has 700 and Ben 550, and the total is still 1,250. Between BEGIN and COMMIT, nobody else could see the half-finished state where the money had left Amara's account but not yet reached Ben's. (Balances are stored as whole numbers here; real systems usually store money as integer cents. See Data Types.)

Rolling back

ROLLBACK throws away everything since BEGIN. Inside the transaction, your own session already sees its changes, which lets you check the result before deciding:

SQL Check, then roll back

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

The first SELECT shows the mistaken balances (10,000 and 2,500), visible only to this session. After ROLLBACK, the second SELECT shows the original 1,000 and 250. This "begin, change, look, commit or roll back" routine is the professional way to run any risky UPDATE or DELETE by hand.

What happens when something fails

A transaction doesn't roll itself back just because a statement fails. The failing statement is undone, but what happens to the transaction depends on the database:

Database After an error inside a transaction
SQLite The failed statement is undone; the transaction usually stays open (a few severe errors roll it back). You decide: COMMIT or ROLLBACK.
PostgreSQL The whole transaction is marked as failed. Every further statement is refused ("current transaction is aborted") until you ROLLBACK.
MySQL (InnoDB) The failed statement is undone; the transaction stays open (a deadlock rolls back the whole transaction).
SQL Server Depends on the error; with SET XACT_ABORT ON (recommended) any error rolls back the whole transaction.

Because of these differences, applications follow one simple rule: if anything fails, roll back. In code, that's a try/catch around the transaction. Here's the pattern in Python with the built-in sqlite3 module (read-only):

Python
import sqlite3

connection = sqlite3.connect("bank.db", isolation_level=None)  # we manage transactions
try:
    connection.execute("BEGIN")
    connection.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", (300, 1))
    connection.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (300, 2))
    connection.execute("COMMIT")
except Exception:
    connection.execute("ROLLBACK")
    raise

If the first update breaks the CHECK (balance >= 0) rule, the code jumps to ROLLBACK and no money moves. Most frameworks wrap this pattern for you, for example Laravel's DB::transaction(...) or Django's transaction.atomic(). In this lesson's runner an error stops the whole run, so the examples check conditions with queries instead.

A real unit of work: placing an order

In the shop database, placing an order touches three tables. As one transaction, either all three changes happen or none do:

SQL Placing an order ยท database: shop

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

The coffee kit drops from 53 to 52 units and the notebook from 57 to 55. Replace COMMIT with ROLLBACK and run it again: the final query returns no rows, because the order, its items and the stock changes were all undone together.

Savepoints: undoing part of a transaction

Sometimes you want to undo only the last few steps, not everything. A savepoint marks a point inside a transaction that you can roll back to:

  • SAVEPOINT name; sets a marker.
  • ROLLBACK TO name; undoes everything after the marker. The transaction stays open, and so does the savepoint.
  • RELEASE name; forgets the marker and keeps the changes.
SQL Savepoints

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

The final cart holds Tea and Biscuits: the gift wrap and card were undone, but the tea, added before the savepoint, survived. Savepoints can be nested, and frameworks use them to implement "nested transactions": an inner block that fails rolls back to its savepoint without ruining the outer transaction. PostgreSQL, MySQL and SQLite use the syntax above; SQL Server writes SAVE TRANSACTION name and ROLLBACK TRANSACTION name.

ACID: the four guarantees

Database people describe what transactions promise with the acronym ACID:

  • Atomicity. All of a transaction's changes happen, or none do. You saw this with ROLLBACK and the order example. After a crash, the database undoes any transaction that hadn't committed.
  • Consistency. A transaction moves the database from one valid state to another. Every NOT NULL, UNIQUE, CHECK and foreign key rule holds when it commits. (Rules that live only in your application code are up to you.)
  • Isolation. Concurrent transactions don't see each other's half-finished work. How strictly this is enforced is configurable, and that's the topic of the next lesson, Isolation Levels and Locking.
  • Durability. Once COMMIT returns, the changes survive a crash or power cut. Databases achieve this by writing changes to a log on disk (a write-ahead log or journal) before confirming the commit.

DDL inside transactions

Can you roll back a CREATE TABLE? That depends on the database, and it matters for schema migrations:

SQL Rolling back DDL

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

The query returns no rows: SQLite rolled back the table along with its data. PostgreSQL and SQL Server behave the same way. MySQL and Oracle don't: statements like CREATE, ALTER and DROP cause an implicit commit, ending the current transaction first. A migration that fails halfway on MySQL can leave the schema half-changed.

Syntax in different databases

Task Standard / SQLite PostgreSQL MySQL SQL Server Oracle
Start BEGIN; (standard: START TRANSACTION) BEGIN; or START TRANSACTION; START TRANSACTION; or BEGIN; BEGIN TRANSACTION; starts automatically with the first change
Finish COMMIT; / ROLLBACK; same same COMMIT; / ROLLBACK; same
Savepoint SAVEPOINT s; ROLLBACK TO s; same same SAVE TRANSACTION s; same as standard
Default mode autocommit autocommit autocommit autocommit changes wait for COMMIT

SQLite adds a choice of when to take its write lock: BEGIN DEFERRED (the default, lock on first write), BEGIN IMMEDIATE (take the write lock right away) and BEGIN EXCLUSIVE. END is a synonym for COMMIT. Starting a second BEGIN inside an open transaction fails with cannot start a transaction within a transaction; use a savepoint instead.

Transactions in practice

Transactions make bulk inserts fast. In autocommit mode, every INSERT is a separate commit, and every commit waits for the disk. Inserting 10,000 rows inside one transaction is often 100 times faster than 10,000 autocommitted inserts.

  • Keep them short. An open transaction holds locks and keeps old row versions alive. Never wait for user input, a slow API call or an email inside one.
  • Do the work, then commit. Read what you need, validate it, and make the writes quickly.
  • Always end them. A transaction left open by a crashed script can block other writers until the connection closes.
  • Check results as you go. If an UPDATE affects 0 rows when it should affect 1 (an account doesn't exist), roll back instead of continuing.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In autocommit mode, what is a transaction?

    Autocommit wraps every statement in its own transaction. You use BEGIN to group several statements.
  2. 2. Balances are Amara 1000 and Ben 250. You run BEGIN; UPDATE accounts SET balance = 0; ROLLBACK; What are the balances now?

    ROLLBACK undoes every change since BEGIN, so the original balances remain.
  3. 3. Inside a transaction you run SAVEPOINT s, insert row X, then ROLLBACK TO s and COMMIT. Row Y was inserted before the savepoint. What is saved?

    ROLLBACK TO s undoes only what came after the savepoint (X). Y was inserted earlier, and COMMIT saves it.
  4. 4. Which ACID property guarantees that committed changes survive a power failure?

    Durability means a committed transaction is safely on disk, usually through a write-ahead log.
  5. 5. In which database does CREATE TABLE inside a transaction cause an implicit commit?

    MySQL (and Oracle) commit implicitly before DDL statements. SQLite, PostgreSQL and SQL Server can roll back DDL.

Summary

  • A transaction makes several statements one all-or-nothing unit: BEGIN, your statements, then COMMIT or ROLLBACK.
  • Without BEGIN, every statement runs in autocommit mode as its own transaction.
  • Your session sees its own uncommitted changes; others don't.
  • On any error, roll back. Databases differ in what they do automatically after a failed statement.
  • Savepoints (SAVEPOINT, ROLLBACK TO, RELEASE) undo part of a transaction.
  • ACID: atomicity, consistency, isolation, durability.
  • DDL can be rolled back in SQLite, PostgreSQL and SQL Server, but not in MySQL or Oracle.
  • Keep transactions short; batch bulk inserts into one transaction for speed.

Next: Isolation Levels and Locking, where you'll see what happens when transactions run at the same time.