Transactions

The idea

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK; to undo both

If anything fails before COMMIT, the database can ROLLBACK and both updates disappear. Other sessions do not see the half-finished state. Some products use START TRANSACTION instead of BEGIN, and most run each single statement as its own transaction by default (autocommit).

ACID

Transactions in relational databases are usually described by four properties:

• Atomicity means all or nothing.
• Consistency means the data moves from one valid state to another, respecting constraints.
• Isolation means concurrent transactions do not see each other’s unfinished work, to a degree set by the isolation level.
• Durability means that once committed, the change survives a crash.

Concurrency effects

When many users work at once, weaker isolation can allow surprises such as reading data that another transaction later rolls back, or two users overwriting each other’s update. Databases offer isolation levels and locking to trade safety against speed. You rarely need to tune these early, but you should know the problems exist.

Using transactions in practice

• Keep transactions short. Long ones hold locks and block other work.
• Do not call slow external services (such as an HTTP API) inside an open transaction.
• Handle errors: a failed transaction must be rolled back, and the code may need to retry on conflicts or deadlocks.
• A transaction covers one database. Spanning several services needs other patterns; see Distributed Systems Basics.
• Many frameworks start and commit transactions for you, so find out where your project’s boundaries are.
• Many NoSQL stores offer weaker guarantees; see NoSQL Concepts.

Try it

In a local SQLite or PostgreSQL database, open a transaction, change a row, and run ROLLBACK. Check that the row is unchanged. Repeat with COMMIT. If you can, open a second session and check whether it sees the change before the commit.