Transactions: Keeping Data Consistent

Normalized tables mean a single real-world action, like placing an order, now often requires writing to multiple tables at once: inserting into orders, then inserting rows into order_items, then decrementing stock in products. Transactions are how you make sure all of that happens together, or not at all.

Autocommit And Its Risks

By default, MySQL runs in autocommit mode, meaning each statement you write is immediately and permanently saved the moment it runs. That's fine for a single INSERT, but dangerous for a multi-step process. Imagine your application inserts the order row successfully, then crashes or loses its database connection before inserting the order_items rows. You're left with an order that has no items, a half-finished, inconsistent mess sitting permanently in your data.

Commit And Rollback

A transaction groups several statements into one all-or-nothing unit:

START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (1, 150.00);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (LAST_INSERT_ID(), 7, 2);
UPDATE products SET stock = stock - 2 WHERE id = 7;

COMMIT;

COMMIT makes everything inside the transaction permanent, all at once. If anything goes wrong partway through, instead of COMMIT, you run ROLLBACK, and MySQL undoes every change made since START TRANSACTION, as if none of it ever happened:

START TRANSACTION;

UPDATE products SET stock = stock - 2 WHERE id = 7;
-- something goes wrong here, insufficient stock detected in application logic
ROLLBACK;

After ROLLBACK, that UPDATE never took effect; stock is back to exactly what it was before START TRANSACTION. This "all or nothing" guarantee is formally called atomicity, one of the four properties (ACID: atomicity, consistency, isolation, durability) that make transactions trustworthy, though you mainly need the practical intuition rather than reciting the acronym.

LAST_INSERT_ID() in the example above is itself worth noting: it returns the auto-generated id from your most recent INSERT on this same connection, which is exactly how you link a freshly created order to its order_items without a separate lookup query, and critically, it's safe even with multiple users inserting simultaneously, because it's scoped to your own connection, not shared globally.

When Transactions Earn Their Keep

A common beginner mistake is wrapping a single, simple statement in a transaction out of habit, which adds no real benefit since one statement is already atomic on its own. Transactions earn their keep specifically when multiple related writes must succeed or fail together as one logical unit.

Another frequent mistake: starting a transaction, then leaving it open for a long time, perhaps waiting on slow application logic or an external API call, before committing or rolling back. A long-open transaction can hold locks on rows, blocking other users' queries from proceeding, and can seriously hurt performance under real traffic. Keep the time between START TRANSACTION and COMMIT (or ROLLBACK) as short as realistically possible; do slow work before starting the transaction, not inside it.

Isolation Between Transactions

Isolation, another ACID property, deals with what one transaction can see of another transaction's in-progress changes before they're committed. MySQL's default isolation level (REPEATABLE READ) generally prevents you from seeing another transaction's uncommitted changes at all, which protects you from reading data that might get rolled back a moment later. This is mostly invisible in everyday use, but worth knowing exists if you ever hit confusing concurrent-access bugs under real traffic.

A practical tip: in most frameworks (Laravel included, if that's your context), transactions are usually wrapped in a helper that automatically commits on success and rolls back if any exception is thrown, rather than you manually typing START TRANSACTION and COMMIT. Learning the raw SQL version first, as you just did, makes it far easier to understand exactly what that framework helper is doing underneath.

Try It Yourself

Put this into practice: write out, in raw SQL, a transaction that transfers money between two accounts, decreasing one account's balance and increasing another's by the same amount, and explain in plain words why this absolutely must be one transaction rather than two separate, independent UPDATE statements.