MySQL Cookbook, 2nd Edition

Using Alternatives to Transactions

Problem

You need to perform transactional processing, but your application uses a nontransactional storage engine.

Solution

Some transaction-like operations are amenable to workarounds such as explicit table locking. In certain cases, you might not actually even need a transaction; by rewriting your statements, you can entirely eliminate the need for a transaction.

Discussion

Transactions are valuable, but sometimes they cannot or need not be used:

• Your application may use a storage engine that does not support transactions. For example, if you use MyISAM tables, you cannot use transactions because the MyISAM storage engine is nontransactional. Each update to a MyISAM table takes effect immediately without a commit and cannot be rolled back. In this case, you have no choice but to use some kind of workaround for transactions. One strategy that can be helpful in some situations is to use explicit table locking to prevent concurrency problems.

• Applications sometimes use transactions when they’re not really necessary. You may be able to eliminate the need for a transaction by rewriting statements. This might even result in a faster application.

Grouping statements using locks

If you’re using a nontransactional storage engine but you need to execute a group of statements without interference by other clients, you can do so by using LOCK TABLE and UNLOCK TABLE:^[19]

1. Use LOCK TABLE to obtain locks for all the tables you intend to use. (Acquire write locks for ...