MySQL Cookbook, 2nd Edition

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