Workarounds for Reliability with Non

Workarounds for Reliability with Non-Transactional Tables
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 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, 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 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 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 13.2.5.2, “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.
... zobacz całą notatkę