MySQL Transactions Introduction

Imagine you are transferring money from one bank account to another. This involves two steps: deducting the amount from Account A and adding it to Account B. What happens if the server crashes after the deduction but before the addition? You would lose money — and the database would be left in a broken, half-finished state.

This is exactly the problem that transactions solve. A transaction groups multiple SQL statements together so they either all succeed or all fail — nothing in between.

---

What is a Transaction?

A transaction is a sequence of one or more SQL statements that are treated as a single, indivisible unit of work. MySQL either executes every statement in the transaction successfully and saves the result permanently, or it undoes everything if something goes wrong — leaving the database exactly as it was before the transaction started.

This "all or nothing" behaviour is what makes transactions so powerful for applications that handle critical data like payments, orders, or user registrations.

---

The ACID Properties

Every reliable database transaction must follow four core principles, known as ACID. These properties guarantee that your data stays accurate and consistent even when errors or system failures occur.

---

How a Transaction Works

A typical MySQL transaction follows this sequence of steps:

---

Syntax

The three statements that control a transaction are:

START TRANSACTION;   -- Begin the transaction

-- ... your SQL statements go here ...

COMMIT;              -- Save all changes permanently
-- OR
ROLLBACK;            -- Undo all changes since START TRANSACTION

---

A Practical Example

Let's walk through the bank transfer scenario. We have an accounts table and we want to transfer 500 from account 101 to account 202 safely:

START TRANSACTION;

-- Deduct 500 from account 101
UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;

-- Add 500 to account 202
UPDATE accounts SET balance = balance + 500 WHERE account_id = 202;

-- Everything succeeded — save both changes
COMMIT;

If an error occurs between the two UPDATE statements — for example, account 202 does not exist — you can undo everything:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 101;

-- Something went wrong — cancel everything
ROLLBACK;

After the ROLLBACK, account 101 still has its original balance. No money was lost and the database is clean.

---

Understanding Autocommit

By default, MySQL runs in autocommit mode. This means every individual SQL statement is automatically treated as its own transaction and committed immediately after it runs — without you needing to write COMMIT.

-- Check autocommit status (1 = ON, 0 = OFF)
SELECT @@autocommit;

When you write START TRANSACTION, MySQL automatically pauses autocommit for the duration of that transaction. Once you issue a COMMIT or ROLLBACK, autocommit resumes for subsequent statements.

You can also turn autocommit off entirely for your session:

SET autocommit = 0;   -- Turn off autocommit

-- Now every statement is part of a manual transaction
-- You must COMMIT yourself to save changes

---

When Should You Use Transactions?

Transactions are most valuable when:

• Multiple related statements must succeed together — such as transferring funds between two accounts
• You are inserting data into multiple tables at once — e.g., creating an order and its order items in a single operation
• You want to validate data before saving — run checks first, then COMMIT only if everything passes
• You are running bulk updates or deletes — wrap them in a transaction so you can ROLLBACK if the result is not what you expected

---

Key Points to Remember

• A transaction is a group of SQL statements that execute as one unit — all succeed or all fail.
• Use START TRANSACTION to begin, COMMIT to save, and ROLLBACK to undo.
• Transactions follow the ACID properties: Atomicity, Consistency, Isolation, and Durability.
• MySQL uses autocommit by default — writing START TRANSACTION temporarily overrides it.
• Transactions only work with the InnoDB storage engine (MySQL's default).