Transactions

A transaction groups several statements into one unit of work: either all of them take effect, or none do.

Real-world analogy: a bank transfer. Taking £100 from one account and adding it to another must happen together — money must never leave one account without arriving in the other.

BEGIN, COMMIT, ROLLBACK

-- standalone
CREATE TABLE accounts (id INTEGER PRIMARY KEY, owner TEXT, balance INTEGER CHECK (balance >= 0));
INSERT INTO accounts VALUES (1, 'Ada', 500), (2, 'Linus', 100);

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

SELECT * FROM accounts;

+----+-------+---------+
| id | owner | balance |
+----+-------+---------+
|  1 | Ada   |     300 |
|  2 | Linus |     300 |
+----+-------+---------+

• BEGIN starts a transaction (START TRANSACTION in MySQL; BEGIN TRANSACTION in SQL Server).
• Changes are visible only inside the transaction until COMMIT makes them permanent and visible to everyone.
• ROLLBACK undoes everything since BEGIN.

Rolling back

-- standalone
CREATE TABLE accounts (id INTEGER PRIMARY KEY, owner TEXT, balance INTEGER);
INSERT INTO accounts VALUES (1, 'Ada', 500), (2, 'Linus', 100);

BEGIN;
DELETE FROM accounts;
SELECT COUNT(*) AS inside_transaction FROM accounts;
ROLLBACK;

SELECT COUNT(*) AS after_rollback FROM accounts;

+--------------------+
| inside_transaction |
+--------------------+
|                  0 |
+--------------------+
+----------------+
| after_rollback |
+----------------+
|              2 |
+----------------+

The accidental DELETE never became permanent. Running risky changes inside a transaction — check the result, then COMMIT or ROLLBACK — is an excellent habit.

ACID

Transactions give four guarantees, known as ACID:

• Atomicity — all or nothing.
• Consistency — constraints hold before and after; a transaction that would break one fails.
• Isolation — concurrent transactions don't see each other's half-finished work (with some nuances, covered next).
• Durability — once committed, the change survives crashes and power loss.

Savepoints

A savepoint marks a point you can roll back to without abandoning the whole transaction:

-- standalone
CREATE TABLE log (msg TEXT);

BEGIN;
INSERT INTO log VALUES ('step 1');
SAVEPOINT before_step_2;
INSERT INTO log VALUES ('step 2 (bad)');
ROLLBACK TO before_step_2;
INSERT INTO log VALUES ('step 2 (fixed)');
COMMIT;

SELECT * FROM log;

+----------------+
|      msg       |
+----------------+
| step 1         |
| step 2 (fixed) |
+----------------+

Autocommit

Outside an explicit transaction, most databases run each statement in its own transaction and commit it immediately (autocommit). Applications wrap related changes in a transaction — for example, creating an order and its items together.

Keep transactions short

A transaction can hold locks that block other users. Don't keep one open while waiting for user input or calling a slow external service.