Transaction and Atomic Operation Differences
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 126.96.36.199, “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 188.8.131.52, “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 noncritical,
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.
... zobacz całą notatkę