Transactions

Introduction

Most of the statements you have written so far have run on their own, one at a time. But real-world operations are often made up of several steps that must all succeed together, or not happen at all. Imagine transferring money between two bank accounts — if the debit from one account succeeds but the credit to the other fails, money simply vanishes.

This is the problem transactions solve. A transaction groups multiple SQL statements into a single all-or-nothing unit of work. This lesson introduces transactions, why they matter, and the ACID properties that describe the guarantees they provide.

What is a Transaction?

A transaction is a group of one or more SQL statements that are executed together as a single, indivisible unit. Either every statement in the transaction succeeds and is saved, or, if anything goes wrong, none of them are saved at all.

This "all or nothing" guarantee is what makes transactions so important for any operation where partial completion would leave your data in a broken or inconsistent state.

Why Transactions Matter

Consider transferring $1,000 from Alice’s account to Bob’s account. This requires two separate statements: subtract $1,000 from Alice, and add $1,000 to Bob.

UPDATE accounts SET balance = balance - 1000 WHERE name = 'Alice';

-- What if the server crashes right here?

UPDATE accounts SET balance = balance + 1000 WHERE name = 'Bob';

Wrapping both statements in a transaction guarantees that either both updates happen, or neither does — the money is never lost in between.

Starting a Transaction

A transaction begins with START TRANSACTION (or the equivalent BEGIN), after which every statement is part of that transaction until you explicitly COMMIT or ROLLBACK (covered fully in the next lesson).

START TRANSACTION;

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

COMMIT;

COMMIT permanently saves every change made since START TRANSACTION. Until COMMIT runs, the changes are only visible within this same database connection — other connections still see the old data.

The ACID Properties

Transactions are built around four guarantees, commonly remembered by the acronym ACID. You may recall this term briefly from the earlier DBMS vs RDBMS lesson — here is what each letter actually means in practice.

Worked Example: A Bank Transfer

Let’s put this together with a complete, runnable example.

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10,2)
);

INSERT INTO accounts VALUES
    (1, 'Alice', 5000.00),
    (2, 'Bob', 2000.00);

START TRANSACTION;

UPDATE accounts SET balance = balance - 1000.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000.00 WHERE id = 2;

COMMIT;

SELECT * FROM accounts;

+----+-------+----------+
| id | name  | balance  |
+----+-------+----------+
| 1  | Alice | 4000.00  |
| 2  | Bob   | 3000.00  |
+----+-------+----------+

Autocommit Mode

By default, MySQL runs in autocommit mode, meaning every single statement is automatically treated as its own tiny transaction and committed immediately. START TRANSACTION temporarily switches off autocommit until the next COMMIT or ROLLBACK.

SHOW VARIABLES LIKE 'autocommit';

SET autocommit = 0;

You rarely need to change autocommit manually — using START TRANSACTION explicitly, as shown above, is the normal and recommended approach.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Transactions let you treat a group of related SQL statements as one safe, indivisible operation, guaranteeing that your data never ends up in a broken, half-updated state.

In this lesson, you learned what a transaction is, why it matters using a bank transfer example, how to start one with START TRANSACTION, and what the ACID properties guarantee. Next, you will learn the full set of commands — COMMIT, ROLLBACK, and SAVEPOINT — used to control a transaction’s outcome.