O'Reilly logo

MySQL Cookbook by Paul DuBois

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required