Are MySQL transactions under-utilised?

Question

I have a vague understanding of transactions that it means you can help the server understand that a series of queries are related.

Scenario : Suppose I have a series of queries which creates a user - inserts a user info record, creates an empty profile record and initialises a few other records etc..

If for whatever reason the script were to fail half way through this, what do I need to do to ensure that everything to do with the "creating a user queries" gets undone?

From what I understand reading the MySQL documentation, it as simple as using START TRANSACTION and COMMIT statements.

If that is so, why do so many CMS's neglect to do so? I suppose a common argument against it might be that it is unnecessary for short scripts dealing with only a few record updates, but am I not right in thinking that it is also highly beneficial when you are dealing with replication environments too?

Answer 1

In MySQL's InnoDB, you may have to start using SavePoints.

SAVEPOINT

Sets a named transaction savepoint with a name of identifier. If the current transaction has a savepoint with the same name, the old savepoint is deleted and a new one is set.

ROLLBACK TO SAVEPOINT

Rolls back a transaction to the named savepoint without terminating the transaction. Modifications that the current transaction made to rows after the savepoint was set are undone in the rollback, but InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.) Savepoints that were set at a later time than the named savepoint are deleted.

RELEASE SAVEPOINT

If the ROLLBACK TO SAVEPOINT statement returns the following error, it means that no savepoint with the specified name exists:

ERROR 1305 (42000): SAVEPOINT identifier does not exist

The RELEASE SAVEPOINT statement removes the named savepoint from the set of savepoints of the current transaction. No commit or rollback occurs. It is an error if the savepoint does not exist.

CAVEAT

All savepoints of the current transaction are deleted if you execute a COMMIT, or a ROLLBACK that does not name a savepoint.

A new savepoint level is created when a stored function is invoked or a trigger is activated. The savepoints on previous levels become unavailable and thus do not conflict with savepoints on the new level. When the function or trigger terminates, any savepoints it created are released and the previous savepoint level is restored.

EPILOGUE

Given this explanation of using SavePoints, you answer may be as simple as issuing a SavePoint in appropriate places:

START TRANSACTION;
SQL to Add User
SAVEPOINT addedsuersalready
SQL to Add Other Things
SAVEPOINT addedsuersalready
SQL to Add Additional Things
COMMIT;

This may help mitigate some issues with ROLLBACKs.

Why do most CMS products not allow for such mechanisms? Most CMS products use the dreaded MyISAM storage engine.

For example, Drupal and WordPress usually install MyISAM and plugins usually expect MyISAM tables. I have written posts about recommending the switch to InnoDB.

• Apr 22, 2011 : Wordpress Database Slow - should I switch to InnoDB?
• Apr 15, 2011 : What would be the optimal MySQL configuration for a Drupal 7 site?

What's even worse is that more recent plugins for both WordPress and Drupal still make table changes and leave them as MyISAM. Any CMS that uses FullText Indexes cannot convert tables from MyISAM to InnoDB in MySQL 5.5 and prior. MySQL 5.6 just become GA on Feb 05, 2013 and does support FullText Indexing. Give MySQL 5.6 some maturation time before diving in.