Using Alternatives to Transactions

15.9.1 Problem

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

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

15.9.3 Discussion

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

15.9.4 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:[1]

[1] LOCK TABLES and UNLOCK TABLES are synonyms for LOCK TABLE and UNLOCK TABLE.

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 like this:

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:

15.9.5 Rewriting Queries to Avoid Transactions

Sometimes applications use transactions unnecessarily. Suppose 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 Perl application containing a function get_ticket( ) 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 queries within a transaction:[2]

[2] The transact_init( ) and transact_finish( ) functions are discussed in Recipe 15.5.

sub get_ticket { my ($dbh, $meeting_id) = @_; my $ref = transact_init ($dbh); my $count = 0; eval { # check the current ticket count $count = $dbh->selectrow_array ( "SELECT tix_left FROM meeting WHERE meeting_id = ?", undef, $meeting_id); # if there are tickets left, decrement the count if ($count > 0) { $dbh->do ( "UPDATE meeting SET tix_left = tix_left-1 WHERE meeting_id = ?", undef, $meeting_id); } $dbh->commit ( ); }; $count = 0 if $@; # if an error occurred, no tix available transact_finish ($dbh, $ref, $@); return ($count > 0) }

The function 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. Decrement the ticket count only if the count is greater than zero, then check whether the statement affected a row:

sub get_ticket { my ($dbh, $meeting_id) = @_; my $count = $dbh->do ("UPDATE meeting SET tix_left = tix_left-1 WHERE meeting_id = ? AND tix_left > 0", undef, $meeting_id); return ($count > 0); }

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 query rather than with the multiple queries 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-query 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.)

Категории