Recipe 15.1. Choosing a Transactional Storage Engine

Problem

You want to use transactions.

Solution

Check your MySQL server to determine which transactional storage engines it supports.

Discussion

MySQL supports several storage engines, but not all of them support transactions. To use transactions, you must use a transaction-safe storage engine. Currently, the transactional engines include InnoDB, NDB, and BDB, and others may become available. To see which of them your MySQL server supports, check the output from the SHOW ENGINES statement:

mysql> SHOW ENGINES\G *************************** 1. row ***************************  Engine: MyISAM Support: DEFAULT Comment: Default engine as of MySQL 3.23 with great performance *************************** 2. row ***************************  Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables *************************** 3. row ***************************  Engine: InnoDB Support: YES Comment: Supports transactions, row-level locking, and foreign keys *************************** 4. row ***************************  Engine: BerkeleyDB Support: YES Comment: Supports transactions and page-level locking ... 

The output shown is for MySQL 5.0. The transactional engines can be determined from the Comment values; those that actually are available have YES or DEFAULT as the Support value. In MySQL 5.1, SHOW ENGINES output includes a transaction column that indicates explicitly which engines support transactions.

After determining which transactional storage engines are available, you can create a table that uses a given engine by adding an ENGINE = tbl_engine clause to your CREATE TABLE statement:

CREATE TABLE t1 (i INT) ENGINE = InnoDB; CREATE TABLE t2 (i INT) ENGINE = BDB; 

If you have an existing application that uses nontransactional tables, but you need to modify it to perform transactions, you can alter the tables to use a transactional storage engine. For example, MyISAM tables are nontransactional and trying to use them for transactions will yield incorrect results because they do not support rollback. In this case, you can use ALTER TABLE to convert the tables to a transactional type. Suppose that t is a MyISAM table. To make it an InnoDB table, do this:

ALTER TABLE t ENGINE = InnoDB; 

One thing to consider before altering a table is that changing it to use a transactional storage engine may affect its behavior in other ways. For example, the MyISAM engine provides more flexible handling of AUTO_INCREMENT columns than do other storage engines. If you rely on MyISAM-only sequence features, changing the storage engine will cause problems. See Chapter 11 for more information.