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
COMMITstatement (if autocommit is disabled).
You can execute
ROLLBACKto 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 13.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:
Lower disk space requirements
Less memory required to perform updates