MySQL Transaction Gotchas & Good Parts

In my previous post, “MySQL Transactions & Why You Can’t Emulate Them in PHP” we discussed why transactions are useful and how a few simple SQL commands can make your application more robust. But few things are that easy in the life of a web developer…

Statements you can’t ROLLBACK

Unfortunately, ROLLBACK isn’t a global undo for every database action. If you make a fundamental change to the schema, any existing transactions will be COMMIT-ed and the alteration will run within it’s own single transaction. The statements to watch out for are:

CREATE DATABASE
ALTER DATABASE
DROP DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
CREATE INDEX
DROP INDEX
CREATE EVENT
DROP EVENT
CREATE FUNCTION
DROP FUNCTION
CREATE PROCEDURE
DROP PROCEDURE

In essence, you cannot undo major database changes, e.g.:


START TRANSACTION; 

DROP TABLE MyImportantData;
-- existing (empty) transaction is COMMIT-ed
-- table is dropped permanently

ROLLBACK;
-- no chance, mate - your data's gone

note: TEMPORARY tables

It is possible to CREATE, ALTER, and DROP temporary tables without causing an implicit COMMIT. However, it’s not possible to ROLLBACK those actions either.

Savepoints

We’ve heard enough about the exceptions, so let’s look at another good parts. Savepoints are effectively named locations within your transaction. You can ROLLBACK to any SAVEPOINT without affecting earlier SQL updates … it’s a little like the History palette within Photoshop.

An example is the easiest way to demonstrate savepoints:


START TRANSACTION;

-- add record to tableA
INSERT INTO tableA VALUES (1,2,3);

-- create a savepoint
SAVEPOINT tableAupdated;

-- add record to tableB
INSERT INTO tableB VALUES (4,5,6);

-- Whoops! Let's undo the tableB updates...
ROLLBACK TO tableAupdated;

-- Only tableA is updated
COMMIT;

You can set and ROLLBACK TO any number of SAVEPOINT identifiers within your transaction.

Optionally, you can remove a savepoint using:


RELEASE SAVEPOINT savepointName;

All savepoints are removed once a COMMIT or ROLLBACK occurs on the transaction.

Transactions and savepoints are easy to use and protect your valuable InnoDB table data. Is there any reason to continue using MyISAM?

note:Want more?

If you want to read more from Craig, subscribe to our weekly tech geek newsletter, Tech Times.

Frequently Asked Questions (FAQs) about MySQL Transactions

What are the common pitfalls to avoid when using MySQL transactions?

When using MySQL transactions, there are several common pitfalls to avoid. One of the most common is forgetting to commit your transactions. Without committing, the changes you’ve made within the transaction won’t be saved to the database. Another common mistake is not handling errors properly. If an error occurs during a transaction, it’s important to rollback the transaction to avoid any unwanted changes to the database. Lastly, be aware of the isolation level of your transactions. Different isolation levels can lead to different results, so it’s important to choose the right one for your needs.

How does autocommit mode affect MySQL transactions?

Autocommit mode in MySQL is a setting that automatically commits every single SQL command as soon as it is executed. This means that if you’re running multiple SQL commands within a transaction, each command will be committed individually, rather than all at once when the transaction is committed. This can lead to inconsistencies in your data if an error occurs during the transaction. To avoid this, it’s often best to turn off autocommit mode when working with transactions.

How can I use the ROLLBACK command in MySQL transactions?

The ROLLBACK command in MySQL is used to undo all the changes made in the current transaction. It’s particularly useful when an error occurs during a transaction. By calling ROLLBACK, you can ensure that your database remains in a consistent state, even if something goes wrong. To use ROLLBACK, simply call it after the error occurs, like so: ROLLBACK;.

What is the difference between COMMIT and ROLLBACK in MySQL transactions?

COMMIT and ROLLBACK are two commands used in MySQL transactions to control the changes made to the database. COMMIT is used to save the changes made in the current transaction to the database. Once a transaction is committed, the changes are permanent and cannot be undone. On the other hand, ROLLBACK is used to undo the changes made in the current transaction. If an error occurs during a transaction, you can use ROLLBACK to revert the database back to its state before the transaction began.

How can I handle errors in MySQL transactions?

Handling errors in MySQL transactions is crucial to maintaining the integrity of your database. If an error occurs during a transaction, you should rollback the transaction to undo any changes made. You can do this by calling the ROLLBACK command. Additionally, it’s a good practice to log any errors that occur during a transaction. This can help you identify and fix any issues that may arise.

What are the benefits of using transactions in MySQL?

Transactions in MySQL provide several benefits. They ensure data integrity by making sure that all the changes made in a transaction are either fully completed or not done at all. This is known as atomicity. Transactions also provide isolation, meaning that each transaction is executed independently of others. This prevents conflicts when multiple transactions are executed concurrently. Lastly, transactions provide durability, ensuring that once a transaction is committed, the changes are permanent, even in the event of a system failure.

How can I set the isolation level in MySQL transactions?

The isolation level in MySQL transactions can be set using the SET TRANSACTION command. This command allows you to specify the level of isolation for your transactions, which can be READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, or SERIALIZABLE. Each level provides a different degree of isolation, with READ UNCOMMITTED providing the least isolation and SERIALIZABLE providing the most.

What is the default isolation level in MySQL transactions?

The default isolation level in MySQL transactions is REPEATABLE READ. This level ensures that all reads within a transaction return the same data, even if other transactions are modifying the data concurrently. However, this level can lead to phantom reads, where a transaction reads new rows that were inserted by another transaction after the first read. If this is a concern, you can set the isolation level to SERIALIZABLE, which prevents phantom reads.

How can I start a transaction in MySQL?

To start a transaction in MySQL, you can use the START TRANSACTION command. This command begins a new transaction and turns off autocommit mode for the duration of the transaction. Once the transaction is started, you can execute your SQL commands, and then commit the transaction using the COMMIT command.

Can I nest transactions in MySQL?

MySQL does not support nested transactions. Once you start a new transaction, any existing transaction is automatically committed. If you need to perform multiple independent transactions, you should commit each one before starting the next. If you start a new transaction before committing the previous one, the changes from the previous transaction will be committed automatically, which may not be the desired behavior.