1.9.5.3. Transactions and Atomic Operations
MySQL Server (version 3.23-max and all versions 4.0 and above)
supports transactions with the InnoDB and
BDB transactional storage engines.
InnoDB provides full
ACID compliance. See
Chapter 14, Storage Engines and Table Types. For information about
InnoDB differences from standard SQL with
regard to treatment of transaction errors, see
Section 14.2.15, “InnoDB Error Handling”.
The other non-transactional storage engines in MySQL Server
(such as MyISAM) follow a different
paradigm for data integrity called “atomic
operations.” In transactional terms,
MyISAM tables effectively always operate in
AUTOCOMMIT=1 mode. Atomic operations often
offer comparable integrity with higher performance.
Because MySQL Server supports both paradigms, you can decide
whether your applications are best served by the speed of
atomic operations or the use of transactional features. This
choice can be made on a per-table basis.
As noted, the trade-off for transactional versus
non-transactional storage engines lies mostly in performance.
Transactional tables have significantly higher memory and disk
space requirements, and more CPU overhead. On the other hand,
transactional storage engines such as
InnoDB also offer many significant
features. MySQL Server's modular design allows the concurrent
use of different storage engines to suit different
requirements and deliver optimum performance in all
situations.
But how do you use the features of MySQL Server to maintain
rigorous integrity even with the non-transactional
MyISAM tables, and how do these features
compare with the transactional storage engines?
-
If your applications are written in a way that is
dependent on being able to call
ROLLBACK rather than
COMMIT in critical situations,
transactions are more convenient. Transactions also ensure
that unfinished updates or corrupting activities are not
committed to the database; the server is given the
opportunity to do an automatic rollback and your database
is saved.
If you use non-transactional tables, MySQL Server in
almost all cases allows you to resolve potential problems
by including simple checks before updates and by running
simple scripts that check the databases for
inconsistencies and automatically repair or warn if such
an inconsistency occurs. Note that just by using the MySQL
log or even adding one extra log, you can normally fix
tables perfectly with no data integrity loss.
More often than not, critical transactional updates can be
rewritten to be atomic. Generally speaking, all integrity
problems that transactions solve can be done with
LOCK TABLES or atomic updates, ensuring
that there are no automatic aborts from the server, which
is a common problem with transactional database systems.
To be safe with MySQL Server, regardless of whether you
use transactional tables, you only need to have backups
and have binary logging turned on. When that is true, you
can recover from any situation that you could with any
other transactional database system. It is always good to
have backups, regardless of which database system you use.
The transactional paradigm has its benefits and its drawbacks.
Many users and application developers depend on the ease with
which they can code around problems where an abort appears to
be necessary, or is necessary. However, even if you are new to
the atomic operations paradigm, or more familiar with
transactions, do consider the speed benefit that
non-transactional tables can offer on the order of three to
five times the speed of the fastest and most optimally tuned
transactional tables.
In situations where integrity is of highest importance, MySQL
Server offers transaction-level reliability and integrity even
for non-transactional tables. If you lock tables with
LOCK TABLES, all updates stall until
integrity checks are made. If you obtain a READ
LOCAL lock (as opposed to a write lock) for a table
that allows concurrent inserts at the end of the table, reads
are allowed, as are inserts by other clients. The newly
inserted records are not be seen by the client that has the
read lock until it releases the lock. With INSERT
DELAYED, you can write inserts that go into a local
queue until the locks are released, without having the client
wait for the insert to complete. See
Section 7.3.3, “Concurrent Inserts”, and
Section 13.2.4.2, “INSERT DELAYED Syntax”.
“Atomic,” in the sense that we mean it, is
nothing magical. It only means that you can be sure that while
each specific update is running, no other user can interfere
with it, and there can never be an automatic rollback (which
can happen with transactional tables if you are not very
careful). MySQL Server also guarantees that there are no dirty
reads.
Following are some techniques for working with
non-transactional tables:
Loops that need transactions normally can be coded with
the help of LOCK TABLES, and you don't
need cursors to update records on the fly.
-
To avoid using ROLLBACK, you can employ
the following strategy:
Use LOCK TABLES to lock all the
tables you want to access.
Test the conditions that must be true before
performing the update.
Update if the conditions are satisfied.
Use UNLOCK TABLES to release your
locks.
This is usually a much faster method than using
transactions with possible rollbacks, although not always.
The only situation this solution doesn't handle is when
someone kills the threads in the middle of an update. In
that case, all locks are released but some of the updates
may not have been executed.
-
You can also use functions to update records in a single
operation. You can get a very efficient application by
using the following techniques:
For example, when we are updating customer information, we
update only the customer data that has changed and test
only that none of the changed data, or data that depends
on the changed data, has changed compared to the original
row. The test for changed data is done with the
WHERE clause in the
UPDATE statement. If the record wasn't
updated, we give the client a message: “Some of the
data you have changed has been changed by another
user.” Then we show the old row versus the new row
in a window so that the user can decide which version of
the customer record to use.
This gives us something that is similar to column locking
but is actually even better because we only update some of
the columns, using values that are relative to their
current values. This means that typical
UPDATE statements look something like
these:
UPDATE tablename SET pay_back=pay_back+125;
UPDATE customer
SET
customer_date='current_date',
address='new address',
phone='new phone',
money_owed_to_us=money_owed_to_us-125
WHERE
customer_id=id AND address='old address' AND phone='old phone';
This is very efficient and works even if another client
has changed the values in the pay_back
or money_owed_to_us columns.
-
In many cases, users have wanted LOCK
TABLES or ROLLBACK for the
purpose of managing unique identifiers. This can be
handled much more efficiently without locking or rolling
back by using an AUTO_INCREMENT column
and either the LAST_INSERT_ID() SQL
function or the mysql_insert_id() C API
function. See Section 12.10.3, “Information Functions”, and
Section 25.2.3.36, “mysql_insert_id()”.
You can generally code around the need for row-level
locking. Some situations really do need it, and
InnoDB tables support row-level
locking. Otherwise, with MyISAM tables,
you can use a flag column in the table and do something
like the following:
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
MySQL returns 1 for the number of
affected rows if the row was found and
row_flag wasn't 1 in
the original row. You can think of this as though MySQL
Server changed the preceding statement to:
UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag <> 1;
