Recipe 15.9. 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:^[*]

^[*] LOCK TABLES and UNLOCK TABLES are synonyms for LOCK TABLE and UNLOCK TABLE.

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 statements that must be executed as a group.
Release the locks with UNLOCK TABLE. Other clients will regain access to the tables.

Locks obtained with LOCK TABLE remain in effect until you release them and thus can apply over the course of multiple statements. This gives you the same concurrency benefits as transactions. However, there is no rollback if errors occur, so table locking is not appropriate for all applications. For example, you might try performing an operation that transfers funds from Eve to Ida as follows:

LOCK TABLE money WRITE; UPDATE money SET amt = amt - 6 WHERE name = 'Eve'; UPDATE money SET amt = amt + 6 WHERE name = 'Ida'; UNLOCK TABLE;

Unfortunately, if the second update fails, the effect of the first update is not rolled back. Despite this caveat, there are certain types of situations where table locking may be sufficient for your purposes:

A set of statements consisting only of SELECT queries. If you want to run several SELECT statements and prevent other clients from modifying the tables while you're querying them, locking will do that. For example, if you need to run several summary queries on a set of tables, your summaries may appear to be based on different sets of data if other clients are allowed to change rows in between your summary queries. This will make the summaries inconsistent. To prevent that from happening, lock the tables while you're using them.
Locking also can be useful for a set of statements in which only the last statement is an update. In this case, the earlier statements don't make any changes and there is nothing that needs to be rolled back should the update fail.

Rewriting statements to avoid transactions

Sometimes applications use transactions unnecessarily. Suppose that you have a table meeting that records meeting and convention information (including the number of tickets left for each event), and that you're writing a Ruby application containing a get_ticket⁠(⁠ ⁠ ⁠) method that dispenses tickets. One way to implement the function is to check the ticket count, decrement it if it's positive, and return a status indicating whether a ticket was available. To prevent multiple clients from attempting to grab the last ticket at the same time, issue the statements within a transaction:

def get_ticket(dbh, meeting_id)   count = 0   begin     dbh['AutoCommit'] = false     # check the current ticket count     row = dbh.select_one("SELECT tix_left FROM meeting                           WHERE meeting_id = ?",                          meeting_id)     count = row[0]     # if there are tickets left, decrement the count     if count > 0       dbh.do("UPDATE meeting SET tix_left = tix_left-1               WHERE meeting_id = ?",              meeting_id)     end     dbh.commit     dbh['AutoCommit'] = true   rescue DBI::DatabaseError => e     count = 0       # if an error occurred, no tix available     begin           # empty exception handler in case rollback fails       dbh.rollback       dbh['AutoCommit'] = true     rescue     end   end   return count > 0 end

The method dispenses tickets properly, but involves a certain amount of unnecessary work. It's possible to do the same thing without using a transaction at all, if auto-commit mode is enabled. Decrement the ticket count only if the count is greater than zero, and then check whether the statement affected a row:

def get_ticket(dbh, meeting_id)   count = dbh.do("UPDATE meeting SET tix_left = tix_left-1                   WHERE meeting_id = ? AND tix_left > 0",                  meeting_id)   return count > 0 end

In MySQL, the row count returned by an UPDATE statement indicates the number of rows changed. This means that if there are no tickets left for an event, the UPDATE won't change the row and the count will be zero. This makes it easy to determine whether a ticket is available using a single statement rather than with the multiple statements required by the transactional approach. The lesson here is that although transactions are important and have their place, you may be able to avoid them and end up with a faster application as a result. The single-statement solution is an example of what the MySQL Reference Manual refers to as an "atomic operation." The manual discusses these as an efficient alternative to transactions.