Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Transactions and Performance

SQL BEGIN, COMMIT and ROLLBACK

Reference for SQL transaction control: BEGIN or START TRANSACTION, COMMIT and ROLLBACK, autocommit, SQLite's BEGIN modes and dialect differences.

A transaction groups several statements into one unit of work. BEGIN (or START TRANSACTION) opens it, COMMIT makes every change permanent, and ROLLBACK throws them all away. Without an explicit transaction, most databases run in autocommit mode, where each statement is its own tiny transaction.

Use transactions whenever two or more changes must succeed or fail together, such as moving money between accounts or creating an order and its line items. Inside the transaction your own session sees its changes immediately; other sessions see them only after the commit.

SQL
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Try it

SQL Roll back a raise ยท database: company

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

SQL Commit a change and use BEGIN IMMEDIATE ยท database: bookstore

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

SQL A second BEGIN inside a transaction (fails on purpose) ยท database: bookstore

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

The last example stops with the error cannot start a transaction within a transaction. To nest work inside a transaction use a SAVEPOINT.

Syntax

SQL
BEGIN [DEFERRED | IMMEDIATE | EXCLUSIVE] [TRANSACTION];   -- SQLite
START TRANSACTION [ISOLATION LEVEL level] [READ WRITE | READ ONLY];  -- standard
COMMIT [TRANSACTION];
ROLLBACK [TRANSACTION];
Part Description
BEGIN / START TRANSACTION Opens a transaction. START TRANSACTION is the standard spelling; BEGIN is shorter and widely accepted.
COMMIT Ends the transaction and makes the changes permanent and visible to others. In SQLite END is a synonym.
ROLLBACK Ends the transaction and discards every change made since it began.
DEFERRED SQLite default: locks are taken when the first read or write happens.
IMMEDIATE SQLite: takes the write lock right away, so later writes cannot fail with a busy error.
EXCLUSIVE SQLite: like IMMEDIATE, but also blocks readers in rollback-journal mode.

Dialect differences

Database Open a transaction Notes
SQLite BEGIN [DEFERRED / IMMEDIATE / EXCLUSIVE] One writer at a time. DDL is transactional.
PostgreSQL BEGIN or START TRANSACTION DDL is transactional, so CREATE TABLE can be rolled back. A failed statement puts the whole transaction in an aborted state until ROLLBACK.
MySQL START TRANSACTION or BEGIN Needs InnoDB. DDL statements such as CREATE TABLE commit implicitly. SET autocommit = 0 also starts transactions implicitly.
SQL Server BEGIN TRANSACTION COMMIT TRANSACTION / ROLLBACK TRANSACTION. SET IMPLICIT_TRANSACTIONS ON changes the default. @@TRANCOUNT shows nesting depth.
Oracle No BEGIN statement A transaction starts with the first change and ends at COMMIT or ROLLBACK. DDL commits implicitly. (BEGIN opens a PL/SQL block there, not a transaction.)
SQL
-- PostgreSQL
START TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- SQL Server
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
IF @@ERROR <> 0 ROLLBACK TRANSACTION ELSE COMMIT TRANSACTION;

-- Oracle: no BEGIN
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Usage notes

  • Always finish what you start. An open transaction holds locks and prevents cleanup. Keep transactions short and never wait for user input inside one.
  • A closed connection rolls back. If your program crashes before COMMIT, the database discards the changes.
  • Statement errors differ. In SQLite and MySQL a failed statement undoes only itself and the transaction continues; in PostgreSQL the whole transaction must be rolled back.
  • Not every statement can run inside a transaction. SQLite's VACUUM and some PRAGMA changes refuse to; MySQL commits implicitly on DDL.
  • Application code should wrap transactions in a try/except (or try/finally) block that commits on success and rolls back on any exception.
  • COMMIT can fail (for example a deferred constraint, or a serialization failure under SERIALIZABLE), so retry logic belongs around the whole transaction.
  • Reading inside a transaction gives results that depend on the isolation level.