Transactions

Explanation

A transaction bundles several SQL statements into a single, all-or-nothing unit of work. Either every statement succeeds and you COMMIT, or something goes wrong and you ROLLBACK to undo it all. This is what keeps your data consistent when multiple changes must happen together — for example, moving money between two accounts.

Transactions give you the ACID guarantees that make databases trustworthy:

• Atomicity — the whole unit succeeds or none of it does.
• Consistency — the data always obeys its rules.
• Isolation — concurrent transactions don't see each other's partial work.
• Durability — once committed, the change survives a crash.

Syntax

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
 
-- If something failed:
ROLLBACK;

Interactive Example

In a read-only environment you can't mutate data, but you can see the shape of a safe read inside a transaction. The queries below are the kind you'd run to check balances before committing a transfer.

Common Mistakes

• Forgetting to COMMIT. The changes stay pending and may be rolled back when the connection closes — your "update" silently vanishes.
• Holding transactions too long. Long transactions lock rows and block other users; do your thinking outside the transaction.
• Catching errors but still committing. Always roll back on failure.

Best Practices

• Wrap every multi-step mutation in BEGIN ... COMMIT.
• Use TRY/CATCH logic in your app to ROLLBACK on any error.
• Keep transactions short and focused on a single logical operation.

Practice Question

Describe the statements (in plain SQL) needed to transfer 50 units of stock from product id = 1 to product id = 2, wrapped in a transaction, committing only if both UPDATEs affect exactly one row.

Summary

Transactions group statements into an atomic unit via BEGIN, COMMIT, and ROLLBACK, delivering ACID guarantees. Use them whenever several changes must succeed or fail together.

FAQ