MySQL Server (version 3.23-max and all versions 4.0 and above)
supports transactions with the
InnoDB transactional storage
engine. In MySQL 5.5 and up, newly created tables use
InnoDB by default, as explained in
Section 184.108.40.206, “
InnoDB as the Default MySQL Storage Engine”. By default,
InnoDB provides full
ACID compliance; see Section 14.2.8, “MySQL and the ACID Model” for ways
that you can adjust settings to balance ACID compliance with
raw performance. For information about
InnoDB differences from standard SQL with
regard to treatment of transaction errors, see
Section 220.127.116.11, “
InnoDB Error Handling”.
The nontransactional storage engines in MySQL Server (such as
MyISAM) follow a different paradigm for
data integrity called “atomic operations”.
MyISAM tables effectively always operate in
autocommit = 1 mode. Because
changed data is written to disk one statement at a time, it is
harder to guarantee the consistency of a sequence of related
DML operations, which could be interrupted partway through.
Thus, this mode is suitable for read-mostly workloads. In
transactional terms, while each specific update is running, no
other user can interfere with it, there can never be an
automatic rollback, and there are no dirty reads. However,
these features apply to single operations, not related updates
that succeed or fail as a unit. Workarounds such as the
LOCK TABLES statement limit
concurrent write access to nontransactional tables.
You can choose which paradigm to use, even for different tables within the same application: transactional features for reliability combined with high performance, or atomic operations for non-critical, read-mostly data (for example, on replication slave servers).
Transactional storage engines such as
InnoDB offer many significant features to
support high reliability for heavy read/write workloads. As a
result, transactional tables can have higher memory and disk
space requirements, and more CPU overhead. 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
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 checks before updates and by running scripts that check the databases for inconsistencies and automatically repair or warn if such an inconsistency occurs. You can normally fix tables with no data integrity loss by using the MySQL log or even adding one extra log.
Sometimes, critical transactional updates can be rewritten
to be atomic. Multiple DML operations can be done with
LOCK TABLES or atomic
updates, ensuring that there are no deadlocks by limiting
concurrent write access. 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.
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 18.104.22.168, “
INSERT DELAYED Syntax”.
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.
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 22.214.171.124, “
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;