Description:
I think that section 1.8.5.2 Transactions and Atomic Operations needs some attention:
1) the material belongs in the MyISAM storage engine section
2) the material should be updated to be more realistic about the failures that will occur and the cost of repairing them. myisamchk will run for a very long time on a very large file.
3) don't claim that MyISAM supports atomic operations
4) remove the suggestions that MyISAM is much faster unless you also provide details on the use case in which that is true.
Finally, don't let product management update the docs. They end up putting "very fast" in front of every feature description
I think the content of this section is useful, but the theme of the section should be changed from "you don't need transactions" to become a realistic guide on using MyISAM for transaction processing.
From http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-transactions.html
There is one disclaimer hidden near the end of the section. Maybe this should be moved to the front so that a weaker variant of "atomic operations" can be used to define what MyISAM provides.
>>>
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.
>>>
Paragraph 2 is incorrect. MyISAM does not offer atomic operations. Atomic means that a statement is either done or not done. When a long-running MyISAM insert, update, delete or replace statement is killed the statement is half-done.
>>>
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 mode. Atomic operations often offer comparable integrity with higher performance.
>>>
What script repairs the table after a insert, update, delete, replace statement is half-done and killed?
>>>
If you use nontransactional 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. 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, this is horrible advice. Generally speaking, statements are not atomic for MyISAM. This assumes the only problems to avoid are rollback requested by the app and deadlock.
>>>
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.
>>>
MyISAM does not support the atomic operations paradigm. It isn't 3X to 5X faster for transaction processing.
>>>
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.
>>>
This is nonsense, MyISAM doesn't offer this as statements are not atomic.
>>>
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 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 12.2.5.2, “INSERT DELAYED Syntax”.
>>>
How to repeat:
read the refman
Suggested fix:
Move this to the MyISAM section.
Provide realistic advice. The sales pitch is not needed.
Make statements atomic for MyISAM
