Diff in Transactional and Non-transactional tables

Question

What is the difference between Transactional and Non-transactional tables? And How will the sql query go if I want to create them?

Also when to use transactional or non-transactional tables?.

Answer 1

Transactional table means, if data manipulation done with in transaction then rollback / commit will work.

For Non Transactional table, You need to rollback the changes with manual code. No Impact of rollBack and commit. These tables are useful for performing the statements with high performance.

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk.

If you are using a transaction-safe storage engine (such as InnoDB, BDB, or NDB Cluster), you can disable autocommit mode with the following statement:

SET AUTOCOMMIT=0;

Answer 2

The major difference between these types of tables is that Transactional tables support transactions

Creation asample:

CREATE TABLE  mytable (
  `pk_id` int(11) NOT NULL,
  `name` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`pk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;