InnoDB and Transactions

[thewiz03]

I have 3 Questions:

1) In order to do a transaction, must the table be of type "InnoDB"? For example, if I do:

CREATE TABLE t (f INT);
versus
CREATE TABLE t (f INT) TYPE=InnoDB ;

Will I be able to do a transaction if I don't use the "TYPE=InnoDB"?

2) What necessarily is the difference between an InnoDB table and a regular table.

3) If create a table of type innoDB, can/will all changes to the database be able to be rolled back even if I didn't use a "BEGIN;" statement? This is something I don't want to happen.

[Catharsis]

Have a look at the docs for mysql's INNODB

http://dev.mysql.com/doc/mysql/en/innodb-overview.html

InnoDB is hugely powerfull and unless your building a database that is using foreign keys explicitly and your looking to handle mutile queries, inserts and so on its probably overkill.

But look into why you need transactions 🙂

Oh and if you dont specify a table type mysql defaults to MyISM I think

Thirdly unless you specify transactions to be Always ON then yes you can make inserts/select from the DB without using a transaction.

You can set this within mysql itself or in a config file.

[Sxooter]

Note that for frequently updated tables, innodb will out perform myisam tables, since myisam tables are completely locked during updates.

The basic rule is that for mostly read tables where data integrity is no big deal, use myisam. For mixed read/write or for more heavily updated / inserted / deleted tables or where data integrity matters, use innodb. Or PostgreSQL or firebird.