MySQL Database learning



Home / Table Types or Storage Engines / What are transaction-safe tables and not transaction-safe tables in MySQL?
Share this job:


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?

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

  1. 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.
  2. You can combine many statements and accept these all in one go with the COMMIT command.
  3. You can execute ROLLBACK to ignore your changes (if you are not running in auto-commit mode).
  4. If an update fails, all your changes will be restored.

Advantages of Not Transaction-Safe Tables

  1. Much faster as there is no transaction overhead.
  2. Will use less disk space as there is no overhead of transactions.
  3. 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.





Table Types or Storage Engines related other questions

What is default table type of MySQL?

You can check the default table type being used by MySQL to run one of the following queries:SELECT @@table_type; orSHOW VARIABLES LIKE \"table_type\"; MySQL Server uses MyISAM as its d ...

How to convert a table from one type to another in MySQL?

MySQL provides ALTER TABLE statement to alter the table structure. To convert a table from one type to another, you can use this statement. Take one of the following two examples, both do the same. However, T ...

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

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

How to get storage engines available on MySQL server?

MySQL provides SHOW ENGINES command to determine which engines are available to your MySQL server. Take an example:mysql>SHOW ENGINES; The above command will show you all available e ...

What is the fastest storage engine in MySQL?

MySQL has MEMORY storage engine. Since MEMORY tables are stored in memory, they offer an extremely fast response time. However, it is pertinent to note that anything stored in memory is highly volatile and go ...

How to change the default storage engine of MySQL?

The default storage engine can be changed at server startup or at runtime: The default storage engine can be specified at server startup with the --default-storage-engine option. For ...