Transaction Fundamentals

Ensure atomic, consistent, and durable state changes using transaction boundaries.

In this lesson, you will learn how to treat a series of database changes as a single, indivisible unit of work to ensure your data never ends up in a broken state.

The Problem: Partial Success

Imagine you are building a banking application. You need to transfer $100 from Alice’s account to Bob’s account. This requires two distinct steps:

UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

What happens if the system crashes exactly between those two commands? Alice has lost her money, but Bob hasn't received it. Your database is now "inconsistent."

The Solution: Transaction Boundaries

To prevent this, we use Transaction Boundaries. A transaction acts like a protective wrapper around your commands. You tell the database, "Start a transaction," perform your operations, and then "Commit" the results.

BEGIN TRANSACTION; marks the starting point.
COMMIT; saves all changes permanently to the disk.
ROLLBACK; cancels every change made since the BEGIN command, returning the database to its exact state before the transaction started.

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

-- If everything went well:
COMMIT;

-- If a power outage or error occurred during the steps above,
-- we could issue a ROLLBACK instead to undo Alice's deduction.

Why this Matters

By using these boundaries, you guarantee that your data changes are:

Atomic: Either all steps happen, or none of them do. There is no middle ground.
Consistent: The database moves from one valid state to another.
Durable: Once you COMMIT, those changes are written safely to permanent storage, even if the system crashes immediately after.

Transactions ensure that your business logic remains reliable, preventing the data corruption that occurs when processes are interrupted mid-execution.

Key Takeaway: A transaction acts as an "all-or-nothing" safety net, ensuring that multiple related updates are treated as a single, atomic event that you can either finalize with a COMMIT or undo entirely with a ROLLBACK.