Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

migrated from Apr 30 '12 at 14:24

This question came from our site for professional and enthusiast programmers.

What did you not understand in the documentation? – Oded Apr 29 '12 at 8:52
Really? You can't google for it yourself? It isn't hidden. – Oded Apr 29 '12 at 9:05
up vote 2 down vote accepted

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:


share|improve this answer

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`)
share|improve this answer
ok.Thanks. But how about writing sql query to create transactional or non-transactional table? What syntax difference is there? – drt Apr 29 '12 at 9:32
it's simple. MyISAM tables don't support transactions but InnoDB do. See my updated answer – heximal Apr 29 '12 at 9:43

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.