SQL Transactions

SQL transactions group several statements into one all-or-nothing unit. Either every statement succeeds and is saved with COMMIT, or none are, thanks to ROLLBACK. The classic example is moving money: the debit and the credit must both happen or neither.

BEGIN, COMMIT, ROLLBACK

Here we start a transaction, delete all orders, check the count, then roll back. The final SELECT shows the orders are back.

BEGIN TRANSACTION;
DELETE FROM Orders;
SELECT COUNT(*) AS during_tx FROM Orders;
ROLLBACK;

SELECT COUNT(*) AS after_rollback FROM Orders;

during_tx
---------
0
(1 row)

after_rollback
--------------
10
(1 row)

Committing Changes

When everything is correct, COMMIT makes the changes permanent. This "places an order" by inserting it and reducing stock in one transaction.

BEGIN;
INSERT INTO Orders VALUES (111, 4, 3, 5, '2026-06-01');
UPDATE Products SET Stock = Stock - 5 WHERE ProductID = 3;
COMMIT;

SELECT ProductName, Stock FROM Products WHERE ProductID = 3;

ProductName | Stock
------------+------
Olive Oil   | 40
(1 row)

ACID Properties

• Atomicity: all statements succeed or none do
• Consistency: constraints hold before and after
• Isolation: concurrent transactions do not see each other's half-done work
• Durability: once committed, data survives a crash

Syntax Differences

SQLite and PostgreSQL accept BEGIN; MySQL uses START TRANSACTION (BEGIN also works); SQL Server uses BEGIN TRANSACTION. By default most databases run in autocommit mode, where each statement is its own transaction. Application code usually wraps the commit in try/catch and calls rollback on error.

Frequently asked questions

What happens if I forget to COMMIT?

The changes stay invisible to other sessions and are rolled back when the connection closes. Long-open transactions can also hold locks and block other users.

Can I roll back after COMMIT?

No. Once committed, the change is permanent; you would have to write another statement to reverse it or restore a backup.

What is a SAVEPOINT?

A savepoint marks a point inside a transaction so you can ROLLBACK TO that point without undoing the whole transaction.