Advertisement: Jobs in UAE, Saudi Arabia, Qatar, Pakistan, Bahrain & other regions. Click here to find one for you.

MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
Powered by MySQL
 
Home / Table Types or Storage Engines / Question No: 162

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?

Answer No: 162

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.

Related MySQL FAQs to the Above FAQ

How-to-get-storage-engines-available-on-MySQL-server How to get storage engines available on MySQL server?

What-is-default-table-type-of-MySQL What is default table type of MySQL?

How-to-change-the-default-storage-engine-of-MySQL How to change the default storage engine of MySQL?

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

What-is-the-fastest-storage-engine-in-MySQL What is the fastest storage engine in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Sulata iSoft - Limitless Solutions | Limitless Job Opportunities - Careermidway.com

© 2007 - 2014  www.mysqlfaqs.net
All rights reserved. The site is owned and operated by Mr. Javed Bhatti - Pakistan.