SQL Transactions: BEGIN, COMMIT, ROLLBACK and ACID

Some changes only make sense together. Moving money between two accounts means subtracting from one and adding to the other. If the first step happens and the second fails, money disappears. A transaction groups statements so that either all of them take effect, or none of them do.

The basic commands#

SQL Server writes BEGIN TRANSACTION; MySQL prefers START TRANSACTION; PostgreSQL and SQLite accept BEGIN.

A transfer that commits#

Move 5,000 from Ravi's salary budget to Asha's (imagine these are account balances):

BEGIN;

UPDATE employees SET salary = salary - 5000 WHERE name = 'Ravi';
UPDATE employees SET salary = salary + 5000 WHERE name = 'Asha';

COMMIT;

SELECT name, salary FROM employees WHERE name IN ('Asha', 'Ravi');

name | salary
-----+-------
Asha | 57000
Ravi | 73000

Until COMMIT, other users do not see either change. After COMMIT, they see both at once, never just one.

Undoing with ROLLBACK#

Here is the classic accident, caught in time:

BEGIN;

UPDATE employees SET salary = 0;   -- oops, forgot WHERE

ROLLBACK;

SELECT name, salary FROM employees;

name  | salary
------+-------
Asha  | 52000
Ravi  | 78000
Meera | 81000
John  | 46000
Priya | 61000

Every salary is back. Outside a transaction, the same mistake would have been permanent.

Autocommit#

By default, most databases run in autocommit mode: every statement is its own transaction and is committed immediately. Writing BEGIN switches that off until the next COMMIT or ROLLBACK.

If an error happens inside a transaction, roll back. In PostgreSQL, after any error the transaction refuses further statements until you ROLLBACK.

ACID: what transactions guarantee#

These four properties are why relational databases are trusted with money, orders and medical records.

Savepoints: partial rollback#

A savepoint marks a point inside a transaction that you can roll back to without abandoning everything:

BEGIN;

INSERT INTO orders (order_id, customer_id, amount, order_date)
VALUES (105, 1, 300, '2026-03-15');

SAVEPOINT before_discount;

UPDATE orders SET amount = amount * 0.5 WHERE order_id = 105;
-- Discount was a mistake
ROLLBACK TO SAVEPOINT before_discount;

COMMIT;  -- order 105 is saved with amount 300

Isolation levels#

When many users work at the same time, isolation decides what one transaction can see of others. From least to most strict:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Stricter levels are safer but allow less concurrency, and may make the database abort transactions that conflict, which your application must then retry.

Locks and keeping transactions short#

While a transaction is changing rows, other transactions that want to change the same rows must wait. A transaction left open (for example, someone runs BEGIN, edits a row, and goes to lunch) can block everyone else. Two transactions waiting for each other create a deadlock, which the database resolves by aborting one of them.

Keep transactions short: do the reading and calculating first, then begin, write, and commit quickly.

What can and cannot be rolled back#

• INSERT, UPDATE and DELETE can always be rolled back.
• CREATE, ALTER and DROP (DDL) can be rolled back in PostgreSQL, SQL Server and SQLite, but in MySQL and Oracle they commit immediately, ending the current transaction.

Common mistakes#

Practice#

BEGIN;

INSERT INTO orders (order_id, customer_id, amount, order_date)
VALUES (105, 2, 300, '2026-03-20');

UPDATE products
SET stock = stock - 3
WHERE product_id = 4;

COMMIT;

Key takeaway#