How to Use START TRANSACTION in MySQL: Syntax and Examples

In MySQL, use START TRANSACTION to begin a new transaction block where multiple SQL statements execute as a single unit. After starting, use COMMIT to save changes or ROLLBACK to undo them. This helps keep your data consistent and safe.

Syntax

The basic syntax to start a transaction in MySQL is:

• START TRANSACTION; - Begins a new transaction.
• COMMIT; - Saves all changes made during the transaction.
• ROLLBACK; - Undoes all changes made during the transaction.

Use these commands to group multiple SQL statements so they either all succeed or all fail together.

sql

START TRANSACTION;
-- SQL statements here
COMMIT; -- or ROLLBACK;

Example

This example shows how to transfer money between two accounts safely using a transaction. If any step fails, the changes are undone.

sql

START TRANSACTION;

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

COMMIT;

Output

Query OK, 2 rows affected (0.01 sec)

Common Pitfalls

Common mistakes when using START TRANSACTION include:

• Forgetting to COMMIT or ROLLBACK, leaving the transaction open and locking tables.
• Running START TRANSACTION inside another transaction without proper handling.
• Assuming transactions work with storage engines that do not support them (like MyISAM).

Always check your storage engine supports transactions (InnoDB does).

sql

/* Wrong: Missing COMMIT or ROLLBACK */
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- No COMMIT or ROLLBACK here, transaction stays open

/* Right: Always finish transaction */
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;

Quick Reference

Command	Purpose
START TRANSACTION;	Begin a new transaction block
COMMIT;	Save all changes made in the transaction
ROLLBACK;	Undo all changes made in the transaction
SAVEPOINT name;	Set a savepoint within a transaction
RELEASE SAVEPOINT name;	Remove a savepoint
ROLLBACK TO SAVEPOINT name;	Undo changes to a savepoint

Key Takeaways

Use START TRANSACTION to begin a group of SQL statements that should succeed or fail together.

Always finish a transaction with COMMIT to save changes or ROLLBACK to undo them.

Ensure your tables use a storage engine that supports transactions, like InnoDB.

Avoid leaving transactions open by forgetting COMMIT or ROLLBACK, which can lock your database.

Use savepoints for partial rollbacks inside complex transactions.