Transactions & ACID

So far, every example on this site has run one statement at a time. But plenty of real operations in StreamHub genuinely need more than one statement to succeed together, or not at all. A transaction is how MySQL lets you group several statements into a single all-or-nothing unit - either every statement in the group takes effect, or none of them do. There's no in-between, half-applied state.

A Motivating Example: Adding a Review and Updating a Cached Count

Suppose StreamHub keeps a denormalized review_count column on movies purely so the site can display it instantly without re-counting reviews on every page load. Submitting a new review then genuinely requires two statements: insert the review itself, and increment the movie's cached count.

INSERT INTO reviews (user_id, movie_id, rating, comment, review_date)
VALUES (14, 5, 5, 'One of the best sci-fi films ever made.', '2026-08-16');

UPDATE movies
SET review_count = review_count + 1
WHERE id = 5;

Without a transaction, these are two entirely independent statements as far as MySQL is concerned. If the connection drops, the application crashes, or a constraint error happens between the two statements, the INSERT could succeed while the UPDATE never runs - leaving movies.review_count permanently wrong, silently out of sync with the real number of rows in reviews. Nobody would notice until the displayed count and the actual review list disagreed.

START TRANSACTION, COMMIT, and ROLLBACK

Wrapping both statements in a transaction closes that gap. START TRANSACTION (or the equivalent BEGIN) marks the beginning of the group; COMMIT makes every change in it permanent; ROLLBACK undoes every change made since the transaction started, as if none of it had happened.

The success path - both statements commit together:

START TRANSACTION;

INSERT INTO reviews (user_id, movie_id, rating, comment, review_date)
VALUES (14, 5, 5, 'One of the best sci-fi films ever made.', '2026-08-16');

UPDATE movies
SET review_count = review_count + 1
WHERE id = 5;

COMMIT;

The failure path - something goes wrong, so everything is undone:

START TRANSACTION;

INSERT INTO reviews (user_id, movie_id, rating, comment, review_date)
VALUES (14, 5, 5, 'One of the best sci-fi films ever made.', '2026-08-16');

-- Something goes wrong here - a constraint violation, an application error, anything

ROLLBACK;

After that ROLLBACK, it's as if the INSERT never ran at all - reviews is back to exactly how it looked before START TRANSACTION. The database is never left with a review that exists but was never counted; either both statements land, or neither does.

ACID - The Four Guarantees a Transaction Makes

"Transactions are all-or-nothing" is really shorthand for four separate guarantees, usually remembered by the acronym ACID:

Isolation Levels - Trading Strictness for Concurrency

Isolation isn't all-or-nothing on its own - MySQL lets you choose how strictly concurrent transactions are kept from seeing each other's in-progress work, called the isolation level. Stricter isolation prevents more subtle anomalies, but can also make transactions wait on each other more often, reducing how much work runs concurrently.

Most StreamHub-style applications never need to change this from InnoDB's default of REPEATABLE READ - it's a solid, well-tested middle ground. Reaching for SERIALIZABLE is a deliberate choice made for a specific, correctness-critical section of code, not a default setting.

SAVEPOINT - Rolling Back Part of a Transaction

Sometimes a transaction covers several logical steps, and you only want to undo the most recent one rather than the whole thing. SAVEPOINT marks a named point inside a transaction that you can roll back to without discarding everything that came before it:

START TRANSACTION;

INSERT INTO watchlist (user_id, movie_id, added_date) VALUES (14, 5, '2026-08-16');

SAVEPOINT after_watchlist_insert;

-- Suppose this next step fails or needs to be undone on its own
ROLLBACK TO after_watchlist_insert;

COMMIT;

The watchlist insert above the savepoint is kept when the final COMMIT runs - only the work done after SAVEPOINT after_watchlist_insert gets undone by the ROLLBACK TO. This is useful conceptual awareness for larger transactions; most simple StreamHub operations are small enough that a single top-level ROLLBACK is all you'll ever need.

Transactions Depend on the Storage Engine

Everything on this page assumes a storage engine that actually supports transactions. MySQL's default engine, InnoDB, does - but the older MyISAM engine does not; a ROLLBACK against MyISAM tables simply won't undo anything. If transactions matter to your application - and for most real applications they do - it's worth explicitly confirming your tables use InnoDB, either when creating them or by checking an existing table:

CREATE TABLE reviews (
  id INT PRIMARY KEY AUTO_INCREMENT,
  -- ... other columns ...
  rating TINYINT
) ENGINE=InnoDB;

SHOW TABLE STATUS WHERE Name = 'reviews';

SHOW TABLE STATUS returns a row describing the table, including an Engine column - if it says anything other than InnoDB, transactions on that table simply won't behave the way this page describes.