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

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?