Oracle 5.0, Reference Manual

Page: 44 / 3232

MySQL Differences from Standard SQL
24
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
1.8.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 . For information about
InnoDB
differences from standard SQL with
regard to treatment of transaction errors, see Section 14.2.11, “
InnoDB
Error Handling” .
The other nontransactional 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
[436] 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 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 situations.
But how do you use the features of MySQL Server to maintain rigorous 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
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 nontransactional tables, MySQL Server in almost all cases enables 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. 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 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 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
READ 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