[UPHPU] MySQL Locking Question

Brandon Stout bms at mscis.org
Sat Aug 11 22:14:16 MDT 2007


Chad Sollis wrote:
> Thank you.  What is the recommended storage engine for flexibility and
> performance?
>
> ~chad

Probably MyISAM, but it still depends on your needs.  A direct quote 
from MySQL documentation (reference follows the quote):

> Transaction-safe tables (TSTs) have several advantages over 
> non-transaction-safe tables (NTSTs):
>
>    *
>
>       They are safer. Even if MySQL crashes or you get hardware
>       problems, you can get your data back, either by automatic
>       recovery or from a backup plus the transaction log.
>
>    *
>
>       You can combine many statements and accept them all at the same
>       time with the |COMMIT| statement (if autocommit is disabled).
>
>    *
>
>       You can execute |ROLLBACK| to ignore your changes (if autocommit
>       is disabled).
>
>    *
>
>       If an update fails, all of your changes are reverted. (With
>       non-transaction-safe tables, all changes that have taken place
>       are permanent.)
>
>    *
>
>       Transaction-safe storage engines can provide better concurrency
>       for tables that get many updates concurrently with reads.
>
> You can combine transaction-safe and non-transaction-safe tables in 
> the same statements to get the best of both worlds. However, although 
> MySQL supports several transaction-safe storage engines, for best 
> results, you should not mix different storage engines within a 
> transaction with autocommit disabled. For example, if you do this, 
> changes to non-transaction-safe tables still are committed immediately 
> and cannot be rolled back. For information about this and other 
> problems that can occur in transactions that use mixed storage 
> engines, see Section 12.4.1, “|START TRANSACTION|, |COMMIT|, and 
> |ROLLBACK| Syntax” <http://dev.mysql.com/doc/refman/5.0/en/commit.html>.
>
> Non-transaction-safe tables have several advantages of their own, all 
> of which occur because there is no transaction overhead:
>
>    *
>
>       Much faster
>
>    *
>
>       Lower disk space requirements
>
>    *
>
>       Less memory required to perform updates
>

http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html
.
Brandon Stout
http://mscis.org






More information about the UPHPU mailing list