MySQL Server (version 3.23-max and all versions 4.0 and above)
supports transactions with the
transactional storage engine.
provides full ACID compliance. See
Chapter 14, Storage Engines. For information about
InnoDB differences from standard SQL with
regard to treatment of transaction errors, see
Section 188.8.131.52, “
InnoDB Error Handling”.
The other nontransactional storage engines in MySQL Server
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
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 tradeoff for transactional versus
nontransactional 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 enables the concurrent
use of different storage engines to suit different
requirements and deliver optimum performance in all
But how do you use the features of MySQL Server to maintain
integrity even with the nontransactional
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
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 nontransactional tables, you must resolve potential problems at the application level 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. You can normally fix tables perfectly with no data integrity loss just by using the MySQL log or even adding one extra log.
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
To be safe with MySQL Server, regardless of what kinds of tables you use, make regular backups and have binary logging turned on. It is always good to have backups, regardless of which database system you use.
The transactional paradigm has its advantages and disadvantages. 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 nontransactional 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 nontransactional tables. If you lock tables with
LOCK TABLES, all updates stall
until integrity checks are made. If you obtain a
LOCAL lock (as opposed to a write lock) for a table
that enables concurrent inserts at the end of the table, reads
are permitted, 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 8.10.3, “Concurrent Inserts”, and
Section 184.108.40.206, “
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 nontransactional tables:
Loops that need transactions normally can be coded with
the help of
and you don't need cursors to update records on the fly.
To avoid using
you can employ the following strategy:
This solution does not handle the situation 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, using the following techniques:
Modify columns relative to their current value. This makes the update correct even if another client has changed the column values in the meantime.
Update only those columns that actually have changed. This is a good database practice in general.
When managing unique identifiers, you can avoid statements
LOCK TABLES or
by using an
AUTO_INCREMENT column and
SQL function or the
mysql_insert_id() C API
function. See Section 12.14, “Information Functions”, and
Section 220.127.116.11, “
tbl_nameSET row_flag=1 WHERE id=ID;
1 for the number of
affected rows if the row was found and
the original row. You can think of this as though MySQL
Server changed the preceding statement to:
tbl_nameSET row_flag=1 WHERE id=ID AND row_flag <> 1;
Copyright © 1997, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices