Implementing Transactions in MySQL with COMMIT and ROLLBACK

Database MySQL
Team CNC
3 February 2025
0
5 minutes read

Transactions in MySQL ensure data integrity by allowing multiple queries to be executed as a single unit of work. If all queries in the transaction are successful, the changes are committed; otherwise, they can be rolled back to maintain database consistency.

1. What is a Transaction?

A transaction is a sequence of one or more SQL statements that are executed together. Transactions follow the ACID principles:

Atomicity: Ensures that all operations in a transaction are completed; otherwise, none are executed.
Consistency: Ensures the database remains in a valid state before and after the transaction.
Isolation: Ensures transactions do not interfere with each other.
Durability: Ensures that once a transaction is committed, it remains permanently stored.

2. Enabling Transactions in MySQL

MySQL transactions work only with storage engines that support them, such as InnoDB. The default storage engine MyISAM does not support transactions.

To check the storage engine of a table:

SHOW CREATE TABLE users;

If the table uses InnoDB, transactions can be applied.

3. Using COMMIT and ROLLBACK

3.1 COMMIT

COMMIT saves all changes made during a transaction permanently in the database.

3.2 ROLLBACK

ROLLBACK undoes all changes made within a transaction, reverting the database to its previous state.

4. Implementing Transactions in MySQL

4.1 Basic Example of a Transaction

START TRANSACTION;
INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 500 WHERE user_id = 1;
COMMIT;

This ensures both the INSERT and UPDATE execute together. If successful, changes are committed.

4.2 Using ROLLBACK for Error Handling

If an error occurs, we can roll back changes to avoid data inconsistency:

START TRANSACTION;
INSERT INTO accounts (user_id, balance) VALUES (2, 2000);
UPDATE accounts SET balance = balance - 500 WHERE user_id = 2;
ROLLBACK;  -- Undoes changes if needed

After ROLLBACK, no changes are made to the database.

4.3 Using SAVEPOINTS for Partial Rollback

Savepoints allow rolling back part of a transaction instead of the entire operation.

START TRANSACTION;
INSERT INTO orders (order_id, user_id, amount) VALUES (1, 101, 500);
SAVEPOINT order_inserted;

UPDATE users SET balance = balance - 500 WHERE user_id = 101;

-- If an error occurs, rollback to the savepoint
ROLLBACK TO order_inserted;
COMMIT;

This approach preserves partial changes while undoing only specific operations.

5. Best Practices for Using Transactions

Use transactions for critical operations like financial transactions, inventory updates, and order processing.
Ensure tables use InnoDB storage engine to support transactions.
Always check for errors before committing changes.
Use savepoints for better control over rollback operations.
Minimize the scope of transactions to reduce lock contention and improve performance.

6. Conclusion

MySQL transactions with COMMIT and ROLLBACK provide a reliable way to manage data consistency and integrity. By using transactions effectively, developers can ensure that database operations execute safely and efficiently.