Transaction Control

This section documents standard SQL (ISO/IEC 9075-2 (SQL/Foundation)) functionality only. Vendor-specific extensions or behavior of any particular database management system are intentionally left out. In the US, the standard is also distributed via ANSI’s webstore (search "ISO/IEC 9075"). This content was generated with the assistance of AI and should be verified against your target DBMS’s own documentation before relying on it in production.

A transaction groups one or more SQL statements into a single unit of work: either every statement in the transaction is applied (COMMIT), or none of them are (ROLLBACK). This page covers the standard SQL statements used to start, checkpoint, undo, and configure the isolation of transactions. For the authoritative (if less readable) specification, see ISO/IEC 9075-2 (SQL/Foundation), the part of the standard that defines transaction control and isolation levels.

Starting a transaction

START TRANSACTION / BEGIN

Standard SQL (ISO/IEC 9075) defines START TRANSACTION to explicitly open a new transaction. Some implementations also accept BEGIN (or BEGIN WORK) as a synonym, but START TRANSACTION is the form defined by the standard.

START TRANSACTION accepts an optional, comma-separated list of characteristics:

  • ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } — sets the isolation level for this transaction only, overriding the session default. See Setting the isolation level below for what each level means.

  • READ ONLY / READ WRITE — declares whether the transaction will modify data. Declaring READ ONLY lets the DBMS apply optimizations that are not safe when writes may occur.

Every statement executed after START TRANSACTION becomes part of that transaction until a COMMIT or ROLLBACK is issued.

START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;

UPDATE accounts
   SET balance = balance - 100.00
 WHERE account_id = 1001;

UPDATE accounts
   SET balance = balance + 100.00
 WHERE account_id = 1002;

COMMIT;

Ending a transaction

COMMIT

COMMIT ends the current transaction and makes all of its changes permanent and visible to other transactions. Once a transaction is committed, its changes cannot be undone with ROLLBACK.

COMMIT takes a single optional keyword:

  • WORK — purely decorative; COMMIT and COMMIT WORK are equivalent.

START TRANSACTION;

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (5001, 42, DATE '2024-01-15');

COMMIT WORK;

ROLLBACK

ROLLBACK ends the current transaction and undoes every change it made since it started (or since the referenced savepoint, see below).

ROLLBACK accepts:

  • WORK — purely decorative, equivalent to plain ROLLBACK.

  • TO SAVEPOINT savepoint_name — undoes only the work performed after the named savepoint was established, without ending the transaction itself. The transaction remains open and can continue, be committed, or be rolled back further.

START TRANSACTION;

UPDATE accounts
   SET balance = balance - 500.00
 WHERE account_id = 2001;

-- Something went wrong with the update above; undo the whole transaction.
ROLLBACK;
START TRANSACTION;

UPDATE inventory
   SET quantity = quantity - 1
 WHERE product_id = 7001;

SAVEPOINT after_inventory_update;

UPDATE accounts
   SET balance = balance - 250.00
 WHERE account_id = 3001;

-- Only the account update was invalid; keep the inventory change.
ROLLBACK TO SAVEPOINT after_inventory_update;

COMMIT;

SAVEPOINT

SAVEPOINT marks a named point within the current transaction that a later ROLLBACK TO SAVEPOINT can return to, without discarding the entire transaction.

SAVEPOINT takes a single parameter:

  • savepoint_name — the identifier used to reference this point later.

START TRANSACTION;

UPDATE accounts
   SET balance = balance - 100.00
 WHERE account_id = 1001;

SAVEPOINT before_credit;

UPDATE accounts
   SET balance = balance + 100.00
 WHERE account_id = 1002;

ROLLBACK TO SAVEPOINT before_credit;

COMMIT;

Setting the isolation level

SET TRANSACTION ISOLATION LEVEL controls how much a transaction can see of the concurrent, uncommitted work of other transactions. The standard defines four isolation levels, ordered from least to most strict:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

The differences between the levels are defined in terms of three phenomena the standard permits or forbids at each level:

  • Dirty read — a transaction reads a row that another, concurrent transaction has modified but not yet committed. If that other transaction rolls back, the first transaction has read data that never really existed.

  • Non-repeatable read — a transaction reads the same row twice and gets different values because another transaction committed an update to that row in between.

  • Phantom read — a transaction re-executes a query with a WHERE clause and gets a different set of rows because another transaction committed an insert or delete that matches the predicate in between.

READ UNCOMMITTED

The weakest isolation level. Transactions may see uncommitted changes made by other transactions (dirty reads), as well as non-repeatable and phantom reads.

READ COMMITTED

Transactions only ever see data that has been committed by other transactions, so dirty reads are prevented. Non-repeatable and phantom reads are still possible, because a row (or the set of rows matching a predicate) may be changed and committed by another transaction between two reads in the same transaction.

REPEATABLE READ

In addition to preventing dirty reads, guarantees that if a transaction reads a given row more than once, it will always see the same values for that row (no non-repeatable reads). Phantom reads are still possible, since the standard’s definition of this level constrains rows already read, not the full set of rows that could match a predicate.

SERIALIZABLE

The strictest isolation level. Prevents dirty reads, non-repeatable reads, and phantom reads: the concurrent execution of a set of serializable transactions is guaranteed to produce the same effect as running those transactions one at a time, in some order.

Per-statement isolation clauses are not standard

Some products let a single query override the isolation level for just that statement — for example Db2’s WITH UR, WITH CS, WITH RS, and WITH RR clauses, appended directly to a SELECT, standing for Uncommitted Read, Cursor Stability, Read Stability, and Repeatable Read respectively. This is a vendor-specific extension, not standard SQL. The standard only lets isolation level be set for an entire transaction, via SET TRANSACTION ISOLATION LEVEL or START TRANSACTION ISOLATION LEVEL (see above) — there is no standard clause for overriding it on an individual statement.

Concurrency Control

When two or more transactions may modify the same row at around the same time, the application needs a strategy for handling the conflict. There are two broad strategies: pessimistic locking, which prevents conflicts up front by acquiring locks before a row is read or modified, and optimistic locking, which lets transactions proceed without locking and instead detects conflicts at commit time.

Pessimistic locking

Pessimistic locking prevents conflicts by assuming they will happen and blocking conflicting access up front, before data is read or modified, so that no other transaction can concurrently modify the same row.

Standard SQL has no statement that requests a lock directly. What it offers instead is the isolation level (see Setting the isolation level above): running the modifying statements inside a transaction at a sufficiently strict level, such as SERIALIZABLE, gives the guarantee that concurrent transactions cannot interfere with one another. Note that the standard defines each isolation level purely in terms of which phenomena (dirty, non-repeatable, and phantom reads) it permits, and deliberately says nothing about the mechanism used to achieve it. Locking is the traditional implementation, but it is not the only one — some implementations satisfy SERIALIZABLE without holding locks at all, detecting conflicts and aborting a transaction at commit time instead. Consult your target DBMS’s documentation for how it actually implements each level.

Products also commonly offer an explicit row-locking clause written directly on a plain SELECT, such as SELECT …​ FOR UPDATE; that form is a vendor extension. The standard does define a FOR UPDATE [OF <column>, …​] clause, but only as part of a <cursor specification> (that is, on DECLARE CURSOR, used together with a positioned UPDATE/DELETE …​ WHERE CURRENT OF <cursor>), not on a free-standing SELECT statement.

  • When it’s used — high-contention workloads, where conflicts between concurrent transactions are frequent enough that retrying after a failed optimistic check would be wasteful.

  • Advantages — guarantees no lost updates or read/write conflicts within the locked scope; simpler application logic, since no retry loop is needed.

  • Disadvantages — reduces concurrency, since other transactions block or fail while the lock is held; risks deadlocks between transactions that lock the same rows in different orders.

START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;

UPDATE sales.order_items
   SET quantity = quantity - 1
 WHERE order_id = 5001
   AND line_number = 1;

COMMIT;

Optimistic locking

Optimistic locking lets concurrent transactions proceed without locking, then detects conflicts at commit time by checking whether the row changed since it was read — typically via a version (or row_version) column that is incremented on every update, or a last-modified timestamp.

  • When it’s used — low-contention workloads, where conflicts are rare, so avoiding locks maximizes concurrency and throughput.

  • Advantages — no locks are held between read and write, so concurrency is higher and this pattern carries no deadlock risk of its own.

  • Disadvantages — requires explicit conflict handling (detecting a zero-row-affected UPDATE and retrying or surfacing an error to the caller); wasted work when a conflict does occur and the transaction must retry.

ALTER TABLE sales.orders
    ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

-- Application reads: SELECT total_amount, version FROM sales.orders WHERE order_id = 5001;
-- ...and later writes back, checking the version read earlier (here, 3):

UPDATE sales.orders
   SET total_amount = 150.00,
       version = version + 1
 WHERE order_id = 5001
   AND version = 3;

-- Zero rows affected means another transaction updated (and incremented) the row first: the
-- application must detect this and retry (re-read, then re-apply the update) or surface a conflict.

Choosing between the two strategies

Pessimistic locking is lock-based: it blocks conflicting access up front, trading some concurrency for a guarantee that no conflict can occur once the lock is held. Optimistic locking is version-check-based: it allows full concurrency up front, trading that guarantee for the possibility of a conflict being detected (and having to be retried) at commit time. As a rule of thumb, pessimistic locking suits high-contention workloads where conflicts are common, while optimistic locking suits low-contention workloads where conflicts are rare and maximizing throughput matters more.