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