Skip navigation links
MySQL 5.5 Reference Manual :: 13 Storage Engines :: 13.1 Comparing Transaction and Nontransaction Engines
13.2 Other Storage Engines »
Section Navigation      [Toggle]

13.1. Comparing Transaction and Nontransaction Engines

Transaction-safe tables (TSTs) have several advantages over nontransaction-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 nontransaction-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 nontransaction-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 nontransaction-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.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.

Nontransaction-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