Transaction-Safe Tables Or Non-Transaction-Safe Tables

• Transaction-safe tables have several advantages over non-transaction-safe tables.
  • They are safer because they have automatic recovery plus a transaction log.
  • Many statements can be combined and accepted all at the same time with the COMMIT statement if autocommit is disabled.
  • You can execute ROLLBACK to ignore changes, if autocommit is disabled.
  • If an update fails, all changes are reverted back.
    • With non-transaction-safe tables, all changes that have taken place are permanent.
  • Transaction-safe storage engines provide better concurrency for tables that get many updates concurrently with reads.

• Although MySQL supports several transaction-safe storage engines, one should not mix different storage engines within a transaction with autocommit disabled.

• Non-transaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead:
  • Faster
  • Lower disk space requirements
  • Less memory required to perform updates

What are the characteristics of MyISAM tables?

---

---

© 2007 John Michael Pierobon

Notes