> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL COMMIT and ROLLBACK > How START TRANSACTION, COMMIT and ROLLBACK make several MySQL statements succeed or fail together, and the statements that commit your work without asking. All examples on this page work on VillageSQL. Install Now → Moving money between 2 accounts takes 2 statements, and a failure between them leaves the money nowhere. A transaction groups statements so that either all of them count or none of them do. These examples use the `sakila_practice` database from [INSERT](/docs/tutorial/insert). Run the setup block there again before you start, then open the client with `mysql -u root -p sakila_practice`. ## How a transaction works ```sql theme={null} START TRANSACTION; FIRST_STATEMENT; SECOND_STATEMENT; COMMIT; ``` Replace `FIRST_STATEMENT` and `SECOND_STATEMENT` with the statements that have to succeed together. `COMMIT` makes the work permanent and visible to everyone else. `ROLLBACK` throws it away and puts the rows back as they were. Until you type one of the 2, the changes are yours alone: another session still sees the old values. Every statement outside a transaction commits by itself, because `autocommit` is on. ```sql theme={null} SELECT @@autocommit AS autocommit; ``` ```text theme={null} +------------+ | autocommit | +------------+ | 1 | +------------+ 1 row in set (0.00 sec) ``` That is why the earlier lessons needed no `COMMIT`: each `INSERT` and `UPDATE` was a transaction of 1 statement. `START TRANSACTION` suspends that until you finish. ## Examples ### 1) Undo the work with ROLLBACK ```sql theme={null} SELECT * FROM account; ``` ```text theme={null} +------------+------------------+---------+ | account_id | owner | balance | +------------+------------------+---------+ | 1 | Mary Smith | 500.00 | | 2 | Patricia Johnson | 250.00 | +------------+------------------+---------+ 2 rows in set (0.00 sec) ``` Move 100 from Mary to Patricia, look at the result, then change your mind. ```sql theme={null} START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE account_id = 1; UPDATE account SET balance = balance + 100 WHERE account_id = 2; SELECT * FROM account; ROLLBACK; ``` ```text theme={null} Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 +------------+------------------+---------+ | account_id | owner | balance | +------------+------------------+---------+ | 1 | Mary Smith | 400.00 | | 2 | Patricia Johnson | 350.00 | +------------+------------------+---------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) ``` The `SELECT` inside the transaction reports 400 and 350, because a session sees its own uncommitted work. The `ROLLBACK` then undoes both updates. ```sql theme={null} SELECT * FROM account; ``` ```text theme={null} +------------+------------------+---------+ | account_id | owner | balance | +------------+------------------+---------+ | 1 | Mary Smith | 500.00 | | 2 | Patricia Johnson | 250.00 | +------------+------------------+---------+ 2 rows in set (0.00 sec) ``` This is the only undo MySQL has. Once a statement outside a transaction has run, the old values are gone. ### 2) Keep the work with COMMIT ```sql theme={null} START TRANSACTION; UPDATE account SET balance = balance - 100 WHERE account_id = 1; UPDATE account SET balance = balance + 100 WHERE account_id = 2; COMMIT; ``` ```text theme={null} Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 0 rows affected (0.00 sec) ``` ```sql theme={null} SELECT * FROM account; ``` ```text theme={null} +------------+------------------+---------+ | account_id | owner | balance | +------------+------------------+---------+ | 1 | Mary Smith | 400.00 | | 2 | Patricia Johnson | 350.00 | +------------+------------------+---------+ 2 rows in set (0.00 sec) ``` The 2 updates landed together. No other session ever saw 400 and 250 at the same time, which is the whole reason for writing it this way. ## Some statements commit without being asked A statement that changes the shape of the database cannot be rolled back. Worse, running one commits whatever the open transaction had done up to that point. ```sql theme={null} START TRANSACTION; UPDATE account SET balance = 0 WHERE account_id = 1; CREATE TABLE note (id INT); ROLLBACK; ``` ```text theme={null} Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ``` Every line reports success, including the `ROLLBACK`. ```sql theme={null} SELECT * FROM account; ``` ```text theme={null} +------------+------------------+---------+ | account_id | owner | balance | +------------+------------------+---------+ | 1 | Mary Smith | 0.00 | | 2 | Patricia Johnson | 350.00 | +------------+------------------+---------+ 2 rows in set (0.00 sec) ``` The balance is 0. `CREATE TABLE` committed the update before it ran, so the `ROLLBACK` had nothing left to undo and still reported success. `ALTER TABLE`, `DROP TABLE`, `TRUNCATE TABLE` and `RENAME TABLE` behave the same way. Keep all 5 out of a transaction that is protecting data. Temporary tables are the exception. `CREATE TEMPORARY TABLE` and `DROP TEMPORARY TABLE` commit nothing, so the same test with a temporary table in the middle leaves the balance at 500.00. ## Summary * Write `START TRANSACTION`, then the statements, then `COMMIT` or `ROLLBACK`. * Expect every statement outside a transaction to commit by itself, because `autocommit` is on. * Expect your session to see its own uncommitted changes and no other session to see them. * Treat `ROLLBACK` as the only undo there is. * Keep `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`, `TRUNCATE TABLE` and `RENAME TABLE` out of a transaction, because each one commits it. * Expect the `TEMPORARY` forms of `CREATE` and `DROP` to commit nothing. ## See also * [UPDATE](/docs/tutorial/update) — the statement most worth wrapping in a transaction * [DELETE](/docs/tutorial/delete) — removing rows you may want back * [Transactions in MySQL](/docs/guides/transactions) — isolation levels, locking and what they cost * [Deadlocks in MySQL](/docs/guides/deadlocks) — what happens when 2 transactions want the same rows