00:00

Help Us Improve

We're sorry this tutorial wasn't helpful. Please tell us what we can do better.

MySQL Transaction Management

Last updated:

A transaction is a group of SQL statements that MySQL treats as a single unit: either all of them take effect, or none do. Transferring money, placing an order with several items, or moving stock between warehouses all need transactions, so a failure halfway through never leaves the data in a broken state.

1. ACID: What Transactions Guarantee

A money transfer as one transactionSTART TRANSACTION, debit 2000 from account A, credit 2000 to account B, then COMMIT makes both changes permanent. If anything fails before COMMIT, ROLLBACK undoes both, so money is never lost or created.STARTTRANSACTIONUPDATE Abalance − 2000UPDATE Bbalance + 2000COMMITboth changes savedROLLBACKboth changes undoneall or nothing: never just the debit without the credit
Figure 1: a transaction groups statements so they succeed or fail together.
PropertyMeaningIn the transfer example
AtomicityAll or nothingDebit and credit both happen, or neither
ConsistencyRules always holdThe CHECK keeps balances from going negative
IsolationConcurrent transactions don't see each other's half-done workOthers never see money "in flight"
DurabilityCommitted changes survive crashesAfter COMMIT the transfer is safe on disk
Transactions need a transactional storage engine. InnoDB (the default) supports them; MyISAM doesn't.

2. START TRANSACTION and COMMIT

Table: accounts (3 rows)

acc_noholderbalance
101Asha10000.00
102Bilal3000.00
103Chen500.00
transfer.sql 
START TRANSACTION;
UPDATE accounts SET balance = balance - 2000 WHERE acc_no = 101;
UPDATE accounts SET balance = balance + 2000 WHERE acc_no = 102;
INSERT INTO transfers (from_acc, to_acc, amount) VALUES (101, 102, 2000);
COMMIT;

SELECT acc_no, holder, balance FROM accounts;

Result: 3 rows

acc_noholderbalance
101Asha8000.00
102Bilal5000.00
103Chen500.00

BEGIN and BEGIN WORK are synonyms for START TRANSACTION.

3. ROLLBACK

Chen tries to send ₹800 but has only ₹500. The CHECK constraint rejects the debit, so the application rolls back the whole transfer:

failed-transfer.sql 
START TRANSACTION;
UPDATE accounts SET balance = balance + 800 WHERE acc_no = 101;   -- credit first
UPDATE accounts SET balance = balance - 800 WHERE acc_no = 103;   -- fails: would go negative
Output 
Query OK, 0 rows affected
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
ERROR 3819 (HY000): Check constraint 'accounts_chk_1' is violated.
rollback.sql 
ROLLBACK;
SELECT acc_no, balance FROM accounts;

Result: 3 rows

acc_nobalance
1018000.00
1025000.00
103500.00
A failing statement doesn't end the transaction by itself: the successful credit was still pending until ROLLBACK. Your application must decide to roll back (or commit) after an error.

4. Autocommit Mode

By default MySQL runs in autocommit mode: every statement outside an explicit transaction is committed immediately. START TRANSACTION suspends autocommit until COMMIT or ROLLBACK. You can also turn it off for the session:

autocommit.sql 
SELECT @@autocommit AS autocommit_on;
SET autocommit = 0;
UPDATE accounts SET balance = balance + 1 WHERE acc_no = 103;
ROLLBACK;                          -- works: the update wasn't committed yet
SELECT balance FROM accounts WHERE acc_no = 103;
SET autocommit = 1;

Default

autocommit_on
1

After ROLLBACK

balance
500.00

5. Savepoints: Partial Rollback

A savepoint marks a point inside a transaction you can roll back to, without abandoning everything:

savepoints.sql 
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE acc_no = 101;   -- fee
SAVEPOINT after_fee;
UPDATE accounts SET balance = balance + 5000 WHERE acc_no = 103;  -- wrong bonus
ROLLBACK TO SAVEPOINT after_fee;                                   -- undo only the bonus
COMMIT;
SELECT acc_no, balance FROM accounts;

Result: 3 rows

acc_nobalance
1017900.00
1025000.00
103500.00

6. Statements That Commit Implicitly

DDL statements (CREATE, ALTER, DROP, TRUNCATE, RENAME) and some others (LOCK TABLES, START TRANSACTION itself) commit any open transaction first. They can't be rolled back:

implicit-commit.sql 
START TRANSACTION;
UPDATE accounts SET balance = balance + 999 WHERE acc_no = 102;
CREATE TABLE audit_note (txt VARCHAR(20));   -- implicit COMMIT of the update!
ROLLBACK;
SELECT balance FROM accounts WHERE acc_no = 102;

Result: 1 row

balance
5999.00

The ₹999 stayed, because the CREATE TABLE committed it before the ROLLBACK.

7. Isolation: What Other Sessions See

LevelDirty readsNon-repeatable readsPhantomsNotes
READ UNCOMMITTEDPossiblePossiblePossibleRarely used
READ COMMITTEDNoPossiblePossibleEach statement sees the latest committed data
REPEATABLE READ (default)NoNoMostly prevented in InnoDBThe whole transaction sees one snapshot
SERIALIZABLENoNoNoPlain SELECTs take shared locks; least concurrency

Watch REPEATABLE READ with two real sessions. Session A starts a transaction and reads Bilal's balance:

Session A 
START TRANSACTION;
SELECT balance FROM accounts WHERE acc_no = 102;

Result: 1 row

balance
5999.00

Session B (autocommit) changes it:

Session B 
UPDATE accounts SET balance = balance + 1000 WHERE acc_no = 102;
Output 
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

Session A still sees its original snapshot until it commits:

Session A 
SELECT balance FROM accounts WHERE acc_no = 102;
COMMIT;
SELECT balance FROM accounts WHERE acc_no = 102;

Inside the transaction: unchanged

balance
5999.00

After COMMIT: Session B's change is visible

balance
6999.00

Change the level per session with SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;.

8. Locks and Waiting

Writes lock the rows they change until the transaction ends. Another session that wants the same row waits (here it gives up after 2 seconds):

Session A 
START TRANSACTION;
UPDATE accounts SET balance = balance - 1 WHERE acc_no = 101;
Output 
Query OK, 0 rows affected
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0
Session B 
UPDATE accounts SET balance = balance + 1 WHERE acc_no = 101;
Output 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Session A 
ROLLBACK;
Output 
Query OK, 0 rows affected
Keep transactions short: don't wait for user input or call slow web services while holding locks. See MySQL Table Locking for deadlocks and SELECT … FOR UPDATE.

9. Transactions from Java

TransferService.java 
try (Connection con = DriverManager.getConnection(url, user, pass)) {
    con.setAutoCommit(false);                       // START TRANSACTION
    try (PreparedStatement debit  = con.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE acc_no = ?");
         PreparedStatement credit = con.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE acc_no = ?")) {
        debit.setBigDecimal(1, amount);  debit.setInt(2, fromAcc);  debit.executeUpdate();
        credit.setBigDecimal(1, amount); credit.setInt(2, toAcc);   credit.executeUpdate();
        con.commit();                               // COMMIT
    } catch (SQLException e) {
        con.rollback();                             // ROLLBACK
        throw e;
    }
}
// Spring: annotate the service method with @Transactional and Spring does the same for you.

10. Summary

  • A transaction makes several statements all-or-nothing (ACID); InnoDB supports it.
  • START TRANSACTION … COMMIT saves; ROLLBACK undoes; SAVEPOINT allows partial undo.
  • Autocommit is on by default; DDL commits implicitly.
  • REPEATABLE READ (default) gives each transaction a consistent snapshot.
  • Keep transactions short; in Java use setAutoCommit(false) or Spring's @Transactional.