PhpRiot
News Archive
PhpRiot Newsletter
Your Email Address:

More information

MySQL Transaction Gotchas & Good Parts

Note: This article was originally published at Planet PHP on 22 December 2010.
Planet PHP

In my previous post, aoMySQL Transactions & Why You Can't Emulate Them in PHPa 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 developera

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 a 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.