Transactions & ACID | Relational Databases

In this chapter

We'll fix the half-happened order for good — meeting BEGIN, COMMIT, and ROLLBACK, the four ACID guarantees, and a real SQLite gotcha: a failed statement doesn't automatically undo the rest of its own transaction.

16–19 min

The Problem in Real Life

Mike notices something stranger this time: a handful of orders show up in Sales with no matching drop in the product's stock count. The sale happened. The inventory never moved.

Sarah traces it back to a single moment: recording a sale is really two separate statements — one INSERT, one UPDATE — and something interrupted the process after the first one finished but before the second one ever ran.

The order half-happened. It's not that it failed — it's that it only partly succeeded.

Sarah

Two Separate Steps vs. One Unbreakable Unit

One business action, several separate statements

Recording a sale means an INSERT and an UPDATE — nothing forces them to succeed or fail together on their own.

A failure doesn't automatically undo what came before it

SQLite backs out only the statement that actually failed, leaving the transaction open unless something explicitly rolls it back.

Constraints are what actually caught the problem

Without the CHECK constraint on InStock, the bad update would have silently succeeded, sending stock negative instead of failing loudly.

Not every ACID guarantee is easy to see directly

Atomicity and Consistency show up clearly in a single Playground session; Durability and Isolation are promises about crashes and other transactions, harder to observe directly.

What Do Transactions and ACID Actually Guarantee?

A transaction groups several statements into one unbreakable unit — either every statement inside it takes effect, or none of them do. BEGIN TRANSACTION starts one, COMMIT makes every change inside it permanent at once, and ROLLBACK undoes every change inside it, as if none of it had ever run.

SQLite has a real, specific gotcha worth knowing here: if one statement inside a transaction fails — a CHECK constraint violation, say — SQLite does not automatically roll back everything that came before it in that same transaction. The failing statement backs out only its own change; the transaction is simply left open, holding whatever already succeeded, until something explicitly issues ROLLBACK or COMMIT. That's exactly how an order can half-happen: unless the application actually checks for the failure and rolls back, the earlier successful statement can end up committed right alongside the one that never took effect.

The Four Guarantees a Transaction Makes (ACID)

Atomicity

All statements inside succeed together, or none of them do

and

Consistency

Every constraint — keys, CHECK, NOT NULL — still holds once it finishes

and

Isolation

A transaction in progress stays invisible to others until it commits

and

Durability

Once committed, a change survives even a crash the very next instant

BEGIN TRANSACTION starts a unit of work; COMMIT makes every statement inside it permanent at once — this is Atomicity in practice.

BEGIN, COMMIT — Recording a Sale as One Unit

BEGIN TRANSACTION;

INSERT INTO Sales (SaleID, CustomerID, ProductID, SaleDate) VALUES (215, 108, 5, '2026-08-29');
UPDATE Products SET InStock = InStock - 1 WHERE ProductID = 5;

COMMIT;

SELECT * FROM Sales WHERE SaleID = 215;
SELECT InStock FROM Products WHERE ProductID = 5;

Both the new sale and the stock decrement land together — there's no moment where only one of them exists.

ROLLBACK undoes every statement inside the transaction — not just the most recent one — as if none of them had ever run.

ROLLBACK — Undoing an Entire Transaction at Once

BEGIN TRANSACTION;

INSERT INTO Sales (SaleID, CustomerID, ProductID, SaleDate) VALUES (216, 108, 5, '2026-08-29');
UPDATE Products SET InStock = InStock - 1 WHERE ProductID = 5;

ROLLBACK;

SELECT * FROM Sales WHERE SaleID = 216;
SELECT InStock FROM Products WHERE ProductID = 5;

Neither the new sale nor the stock change survive — the SELECT afterward finds no row for SaleID 216, and InStock is exactly what it was before this transaction started.

This is the real SQLite gotcha from the intro: when a statement inside a transaction fails, SQLite backs out only that statement — it does not automatically roll back whatever already succeeded earlier in the same transaction.

The Real Danger — a Failed Statement Doesn't Undo the Rest

-- This fails on purpose -- Bananas already has 0 in stock, and the CHECK
-- constraint on InStock won't allow it to go negative. In the Playground
-- below, this block is commented out by default -- uncomment it and run
-- it on its own to see the failure.

BEGIN TRANSACTION;

INSERT INTO Sales (SaleID, CustomerID, ProductID, SaleDate) VALUES (217, 110, 6, '2026-08-29');
UPDATE Products SET InStock = InStock - 1 WHERE ProductID = 6;

COMMIT;

The UPDATE fails here immediately. In a real application, whether the earlier INSERT ends up permanently committed depends entirely on whether the code explicitly catches this failure and calls ROLLBACK — silently moving on instead is exactly how an order half-happens.

Notice which of the four ACID guarantees this chapter actually proved: Atomicity, directly — both codeSnippets above show a transaction succeeding or failing as one whole unit, never partially. Consistency held throughout, since the CHECK constraint is exactly what stopped the bad update from ever landing. Durability is a promise about surviving a crash after COMMIT, which isn't something this Playground can simulate.

Isolation — what one transaction can and can't see about another one still in progress — is the one guarantee this chapter deliberately left alone. It's a genuinely different question from atomicity, and it's the entire subject of the next chapter.

Key Takeaway

A transaction makes several statements succeed or fail together as one unit — but that guarantee only holds if something actually checks for failure and calls ROLLBACK; SQLite won't do it automatically just because one statement inside the transaction failed.

Why This Matters

Every payment, every order, every multi-step update in a real system depends on transactions working exactly as advertised — and the specific gotcha in this chapter (a failed statement not auto-rolling-back the rest) is a genuine, commonly-hit source of silent data corruption in real production code that never explicitly checks for errors.

GreenMart's orders can no longer half-happen, as long as failures are actually checked and rolled back. The next chapter asks a related but different question: what can one transaction in progress actually see about another one that hasn't finished yet?