How to Use Transactions in MySQL: Syntax and Examples

In MySQL, use START TRANSACTION to begin a transaction, then execute your SQL statements. Use COMMIT to save changes or ROLLBACK to undo them if needed.

Syntax

Transactions in MySQL follow this pattern:

• START TRANSACTION; begins the transaction.
• Execute one or more SQL statements.
• COMMIT; saves all changes made during the transaction.
• ROLLBACK; cancels all changes made during the transaction.

This ensures your group of statements either all succeed or all fail together.

sql

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

Example

This example shows a simple money transfer between two accounts using a transaction to keep data consistent.

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, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)

Common Pitfalls

Common mistakes when using transactions include:

• Forgetting to COMMIT or ROLLBACK, leaving transactions open and locking tables.
• Running transactions on tables that do not support transactions (like MyISAM).
• Not handling errors properly, which can cause inconsistent data.

Always check for errors and ensure transactions are closed properly.

sql

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

/* Right: Commit or rollback to close transaction */
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
COMMIT;

Quick Reference

Command	Description
START TRANSACTION	Begin a new transaction
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

Always start a transaction with START TRANSACTION before your SQL statements.

Use COMMIT to save changes or ROLLBACK to undo if something goes wrong.

Ensure your tables use a transactional storage engine like InnoDB.

Never leave transactions open without COMMIT or ROLLBACK to avoid locks.

Use savepoints for partial rollbacks within a transaction if needed.