Search A2Z 24

Question : What are transaction-safe tables and not transaction-safe tables in MySQL?


I am newbie to MySQL. I want to know about Transaction-Safe Tables (TST) and Not Transaction-Safe Tables (NTST). Can you help me to explain, what are Transaction-Safe Tables and Not Transaction-Safe Tables in MySQL?

asked June 11, 2015

1 Answers

Dispalying 1 to 1 of 1 |

MySQL supports two different kinds of tables: Transaction-Safe Tables (InnoDB and BDB etc) and Not Transaction-Safe Tables (HEAP, MERGE, and MyISAM etc).   MySQL also allows to combine Transaction-Safe Tables and Not Transaction-Safe Tables tables in the same database to get the best results.  However, each kind of tables has its own advantages. 

Advantages of Transaction-Safe Tables

  • Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup + the transaction log.
  • You can combine many statements and accept these all in one go with the COMMIT command.
  • You can execute ROLLBACK to ignore your changes (if you are not running in auto-commit mode).
  • If an update fails, all your changes will be restored.

Advantages of Not Transaction-Safe Tables

  • Much faster as there is no transaction overhead.
  • Will use less disk space as there is no overhead of transactions.
  • Will use less memory to perform updates.

With Not Transaction-Safe Tables: If an update fails, no change will be restored as changes that have taken place are permanent.

See comments (0) | Comment
Answered 3 Years ago
Dispalying 1 to 1 of 1 |

Write your Answer write code <pre class="brush: xml">---code---</pre>

Login as a member to access answering block !! click-here