79651

Workarounds for Reliability with Non-Transactional Tables

But how do you use the features of MySQL Server to maintain integrity even with the nontransactional

MylSAMtables, 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 cali rollback rather

than commit in critical situations, transactions are morę 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 atomie. Multiple DML operations

can be done with LOCK TABLES or atomie 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 loeks 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 Sen/er, 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 youuse.