MySQL Cookbook

Verifying Transaction Support Requirements

Problem

You want to use transactions, but don’t know whether your MySQL server supports them.

Solution

Check your server version to be sure it’s recent enough, and determine what table types it supports. You can also try creating a table with a transactional type and see whether MySQL actually uses that type for the table definition.

Discussion

To use transactions in MySQL, you need a server that is recent enough to support transaction-safe table handlers, and your applications must use tables that have a transactional type. To check the version of your server, use the following query:

mysql> SELECT VERSION( );
+----------------+
| VERSION( )      |
+----------------+
| 4.0.4-beta-log |
+----------------+

Transaction support first appeared in MySQL 3.23.17 with the inclusion of the BDB (Berkeley DB) transactional table type. Since then, the InnoDB type has become available; as of MySQL 3.23.29, both types can be used. In general, I’d recommend using as recent a version of MySQL as possible. Transaction support (and MySQL itself) have improved a lot since Version 3.23.29.

Even if your server is recent enough to include transaction support, it may not actually have transactional capabilities. The handlers for the appropriate table types may not have been configured in when the server was compiled. It’s also possible for handlers to be present but disabled, if the server has been started with the --skip-bdb or --skip-innodb options. To check the availability ...