How to Use Transactions in MySQL Database

I learned the importance of transactions the hard way — a payment script I wrote years ago deducted money from one account, then crashed before crediting the second account, because I hadn’t wrapped both operations in a transaction. That afternoon of manual data reconciliation is why I now treat transactions as non-negotiable for any operation that touches more than one row where partial completion would be worse than no completion at all. Let me walk through how transactions actually work in MySQL, not just the syntax.

What a Transaction Is

A transaction is a sequence of one or more SQL statements that MySQL treats as a single, indivisible unit of work. Either every statement in the transaction succeeds and is permanently saved (COMMIT), or none of them are (ROLLBACK) — there’s no in-between state where some statements applied and others didn’t.

The ACID Properties

Every serious discussion of transactions comes back to ACID, and understanding what each letter actually guarantees changed how I design schemas and application logic:

• Atomicity — the transaction is all-or-nothing.
• Consistency — the database moves from one valid state to another, respecting all constraints (foreign keys, unique indexes, check constraints).
• Isolation — concurrent transactions don’t see each other’s uncommitted changes (with tunable strictness, covered below).
• Durability — once committed, a transaction’s changes survive a crash, because InnoDB has already written them to its redo log.

InnoDB is the storage engine responsible for delivering ACID guarantees in MySQL — the older MyISAM engine does not support transactions at all, which is one of the main reasons InnoDB has been the default since MySQL 5.5.

Basic Transaction Syntax

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

COMMIT;

If both UPDATE statements succeed, COMMIT makes the changes permanent. If something goes wrong partway through, I explicitly roll back:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- Suppose an application-level check fails here
ROLLBACK;

After ROLLBACK, the first UPDATE is completely undone, as if it never ran.

Autocommit Mode

By default, MySQL runs in autocommit mode, meaning every individual statement is its own implicit transaction, committed immediately. I check and toggle this explicitly when I need multi-statement atomicity:

SHOW VARIABLES LIKE 'autocommit';

SET autocommit = 0;
-- run multiple statements
COMMIT;
SET autocommit = 1;

I generally prefer explicit START TRANSACTION ... COMMIT blocks over toggling autocommit globally, because it’s clearer in code review exactly which statements are grouped together.

SAVEPOINTs for Partial Rollback

Sometimes I want to undo part of a transaction without discarding everything. SAVEPOINT lets me mark a point I can roll back to.

START TRANSACTION;

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 10;
SAVEPOINT after_inventory_update;

UPDATE orders SET status = 'shipped' WHERE order_id = 55;
-- Suppose this second update needs to be undone, but not the first
ROLLBACK TO after_inventory_update;

COMMIT;

The inventory update remains staged for commit, but the order-status update is undone. I use savepoints most often in complex batch-processing scripts where one sub-step failing shouldn’t force reprocessing the entire batch.

Isolation Levels

Isolation level determines how much one transaction can “see” of another transaction’s uncommitted or concurrently-committed changes. MySQL’s InnoDB supports four standard levels:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;
-- ... other work ...
COMMIT;

MySQL’s default, REPEATABLE READ, is actually stronger than the SQL standard requires, because InnoDB uses MVCC (Multi-Version Concurrency Control) and gap locking to prevent most phantom reads too — something that pleasantly surprised me when I first dug into InnoDB’s internals, since many other databases default to the weaker READ COMMITTED.

How MVCC Works Internally

InnoDB doesn’t lock rows just to let other transactions read them. Instead, every row carries hidden metadata — a transaction ID and a pointer into the undo log. When a transaction reads a row under REPEATABLE READ, it sees a consistent snapshot as of when its transaction started, using the undo log to reconstruct earlier versions of rows that have since been modified by other, later-committing transactions.

sequenceDiagram
    participant T1 as Transaction 1
    participant Row as Row (balance=500)
    participant T2 as Transaction 2
    T1->>Row: BEGIN, reads balance=500
    T2->>Row: UPDATE balance=300, COMMIT
    T1->>Row: Reads again (still sees 500, MVCC snapshot)
    T1->>Row: COMMIT

This is exactly why two transactions running simultaneously don’t block each other on plain reads, even under a fairly strict isolation level — reads consult the undo log’s historical snapshot rather than waiting on a lock.

Locking: Explicit Row Locks

Sometimes I need stronger guarantees than MVCC alone provides — for example, preventing two concurrent transactions from both reading a row with the intent to update it, both computing a new value based on a stale read, and then both writing (a classic race condition).

START TRANSACTION;
SELECT quantity FROM inventory WHERE product_id = 10 FOR UPDATE;
-- other transactions attempting to SELECT ... FOR UPDATE on this row now wait
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 10;
COMMIT;

FOR UPDATE takes an exclusive lock on the selected rows, forcing other transactions requesting the same lock to wait until this transaction commits or rolls back. LOCK IN SHARE MODE (or FOR SHARE in newer syntax) takes a shared lock, allowing other reads but blocking writes.

Deadlocks

With locking comes the possibility of deadlocks — two transactions each holding a lock the other needs.

-- Transaction A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- waiting on account_id = 2, held by Transaction B

-- Transaction B (running concurrently)
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
-- waiting on account_id = 1, held by Transaction A

InnoDB detects this automatically and kills one transaction with Error 1213: Deadlock found when trying to get lock, rolling it back so the other can proceed. My application code always includes retry logic specifically for this error code, because a deadlock isn’t necessarily a bug — it’s an expected possibility under concurrent load that the application layer needs to handle gracefully.

Real-World DBA and Application Scenarios

• Financial transfers: the textbook example — debit one account, credit another, all-or-nothing.
• Order processing: reserve inventory, create an order record, and record a payment — if any step fails, none should stick.
• Batch data imports: wrapping large batches in transactions (with periodic commits every few thousand rows, not one transaction for millions of rows) balances atomicity with manageable transaction log size.
• Idempotent retries: combining transactions with unique constraints so that if a client retries a failed request, the database itself rejects the duplicate rather than relying purely on application logic.

Best Practices

• Keep transactions as short as possible — long-running transactions hold locks longer and increase the odds of blocking or deadlocking other work.
• Always handle deadlock errors with an automatic retry in application code.
• Use FOR UPDATE deliberately, only where a read-then-write race condition genuinely needs preventing, since it adds lock contention.
• Avoid mixing transactional (InnoDB) and non-transactional (MyISAM) tables within the same transaction — MyISAM changes cannot be rolled back.
• Never leave a transaction open across a slow external call (like an HTTP request to a third-party API) — that’s a common way accidental long-lived locks creep into production.

Troubleshooting Table

FAQs

What’s the difference between COMMIT and ROLLBACK? COMMIT makes all changes in the transaction permanent; ROLLBACK undoes all changes made since the transaction began (or since the referenced SAVEPOINT).

Does every MySQL storage engine support transactions? No — InnoDB does; MyISAM does not. This is one of the primary reasons InnoDB became the default engine.

What isolation level does MySQL use by default? REPEATABLE READ, which is stronger than the SQL standard’s minimum requirement thanks to InnoDB’s MVCC and gap-locking implementation.

Can a SELECT statement be part of a transaction? Yes — any statement inside a START TRANSACTION ... COMMIT block is part of that transaction, including reads, which matters for consistent-snapshot semantics.

What causes a deadlock, and is it something to worry about? A deadlock happens when two transactions each hold a lock the other is waiting for. It’s a normal, expected occurrence under concurrency and should be handled with retry logic, not treated as data corruption.

Interview Questions

1. What do the four ACID properties guarantee, individually?
2. What’s the difference between REPEATABLE READ and READ COMMITTED isolation levels?
3. How does InnoDB implement MVCC, and why does it reduce lock contention on reads?
4. What is a deadlock, and how does InnoDB resolve one automatically?
5. What’s the purpose of a SAVEPOINT, and how does it differ from a full ROLLBACK?
6. Why can’t MyISAM tables participate meaningfully in transactions?
7. How would you prevent a race condition where two transactions read and then update the same row based on stale data?

Optimization Tips

• Keep transaction scope tight — only include statements that genuinely need atomicity together.
• Batch large data operations into multiple smaller transactions rather than one giant transaction, to avoid huge undo logs and long lock durations.
• Use SHOW ENGINE INNODB STATUS to inspect current locks and the most recent deadlock when diagnosing contention issues.
• Prefer optimistic concurrency (a version column checked in the WHERE clause of an UPDATE) over pessimistic FOR UPDATE locking when conflicts are rare, since it avoids lock waits entirely in the common case.

Summary and Key Takeaways

Transactions are the guarantee that a group of related changes either all happen or none do, and MySQL’s InnoDB engine delivers that guarantee through ACID compliance, MVCC-based isolation, and a redo/undo log architecture that also enables crash recovery. Understanding isolation levels — and specifically why MySQL’s default REPEATABLE READ already prevents most of the anomalies people worry about — has made me much more deliberate about when I actually need FOR UPDATE locking versus when MVCC alone is sufficient. The habit that’s saved me the most pain is simply keeping transactions short and always building retry logic around deadlock errors rather than treating them as exceptional failures.

References

• MySQL 8.0 Reference Manual — InnoDB and the ACID Model
• MySQL 8.0 Reference Manual — InnoDB Locking
• MySQL 8.0 Reference Manual — InnoDB Transaction Isolation Levels
• MySQL 8.0 Reference Manual — Multi-Versioning