You need to perform transactional processing, but your application uses a nontransactional storage engine.
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.
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.
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]
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.
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.
3.145.71.115