MySQL Transaction Gotchas & Good Parts

Tweet

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.

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Harro

    The reason postgresql is better.. they do support transactional schema changes.

    • Igor

      Yes, it’s an issue for mysql if they want to call themselves “enterprise level database” and a big plus for postgresql. I’m wondering if there are any limitations in postgresql with transactions. I did many times, many major modifications in data and structure of database in postgresql and ROLLBACK did his work every time well.

  • BuggyFunBunny

    The “statements” you listed *are not* SQL. These are, more or less common, database commands.

  • http://www.cemerson.co.uk Stormrider

    So then… I started a thread based on a question I had in the last one of these posts, but I haven’t had an answer yet.

    http://www.sitepoint.com/forums/showthread.php?t=718976

    Why doesn’t this work?

  • kaf

    Craig, even though like always, this article contains useful information, I’m starting to suspect that your motivation behind writing it has less to do with providing an informative piece and more to do with your personal crusade against MyISAM and is more in response to the feedback from your previous articles regarding transactions.

    Lets just have one great and informative article on transactions in MySQL and then move on. I’m sure your sage knowledge on other subjects will be of great use to many developers.

    • http://www.optimalworks.net/ Craig Buckler

      @kaf
      I’m sorry you think I have a personal crusade against MyISAM — I don’t. There will be cases when MyISAM is useful, but it’s dwarfed by the number of applications where InnoDB is a better choice.

      My previous article, “MySQL Transactions & Why You Can’t Emulate Them in PHP” was a response to developers commenting that transactions were unnecessary or could somehow be coded in PHP. This article, however, describes more advanced transactional issues.

      Anyway, you’ll be pleased to hear that this is my last MySQL article for a little while.

      • kaf

        Thanks Craig. I actually agree with you, the advantages InnoDB are stacking up with each new release. And it seems to be actively worked on whereas I haven’t seen much of a change in MyISAM for a while.
        Its just the last line of this piece: “Is there any reason to continue using MyISAM?” made me feel like I was being preached to.

        Looking forward to your next article.

  • Tania

    Very informative post. Useful for SQL beginners and thanks for sharing this to us and keep it up. The queries are seems to be DDL statements.

    http://godwinsblog.cdtech.in/2010/10/windows-nt-user-or-group-domainuser-not.html