How to Start, Commit, and Rollback Transactions in MySQL

This article provides a quick and practical guide on how to manage database transactions in MySQL. You will learn how to use explicit transaction control statements to group multiple SQL queries into a single unit of work, ensuring data integrity by either saving all changes permanently or reverting them if an error occurs.

By default, MySQL automatically commits every individual SQL statement you run. To group multiple statements together and control them manually, you must explicitly manage the transaction using three key commands: START TRANSACTION, COMMIT, and ROLLBACK.

Starting a Transaction

To begin a transaction and temporarily disable MySQL’s automatic committing behavior, use the START TRANSACTION statement. Alternatively, you can use the alias BEGIN.

START TRANSACTION;

Once this command is executed, any subsequent INSERT, UPDATE, or DELETE statements will not be permanently saved to the database until you explicitly choose to do so.

Committing a Transaction

To save all the changes made during the current transaction permanently to the database, use the COMMIT statement.

COMMIT;

After running COMMIT, the changes become visible to other database users, and the transaction is concluded.

Rolling Back a Transaction

If an error occurs, or if you decide to cancel the changes made during the transaction, you can revert the database to its pre-transaction state using the ROLLBACK statement.

ROLLBACK;

This discards all modifications made since the START TRANSACTION command was issued and ends the transaction.

Practical Example

Below is a complete SQL example demonstrating a typical bank transfer where money is moved from one account to another. Both updates must succeed, or neither should take effect.

-- Step 1: Begin the transaction
START TRANSACTION;

-- Step 2: Deduct money from Account A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Step 3: Add money to Account B
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- Step 4: Commit the changes if both updates succeeded
COMMIT;

If the second update fails (for example, due to a database connection loss or constraint violation), you would execute ROLLBACK; instead of COMMIT; to ensure Account A is not wrongfully debited.