MySQL Cookbook

Using Alternatives to Transactions

Problem

You need to perform transactional processing, but your MySQL server doesn’t support transactions.

Solution

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

Discussion

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

• Your server may not support transactions at all. (It may be too old or not configured with the appropriate table handlers, as discussed in Recipe 15.2). 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 may even result in a faster application.

Grouping Statements Using Locks

If your server doesn’t have transactional capabilities but you need to execute a group of queries without interference by other clients, you can do so by using LOCK TABLE and UNLOCK TABLE:^[58]

• Use LOCK TABLE to obtain locks for all the tables you intend to use. (Acquire write locks for tables you need to modify, and read locks for the others.) This prevents other clients from modifying the tables while you’re using them.

• Issue the queries ...