Verifying Transaction Support Requirements

15.2.1 Problem

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

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

15.2.3 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 and status of the transactional table handlers, use SHOW VARIABLES:

mysql> SHOW VARIABLES LIKE 'have_bdb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_bdb | YES |
+---------------+-------+
mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb | YES |
+---------------+-------+

The query output shown here indicates that BDB and InnoDB tables both can be used. If either of these queries produces no output or the Value column says something other than YES (such as NO or DISABLED), the corresponding table type cannot be used.

For programmatic methods of checking the server version and the set of table types that the server supports, see Recipe 9.14 and Recipe 9.18.

Another way to check the availability of a specific table type is to try creating a table with that type. Then issue a SHOW CREATE TABLE statement to see what type MySQL actually uses. For example, try creating t as an InnoDB table by executing the following statements:

mysql> CREATE TABLE t (i INT) TYPE = InnoDB;
mysql> SHOW CREATE TABLE tG
*************************** 1. row ***************************
 Table: t
Create Table: CREATE TABLE `t` (
 `i` int(11) default NULL
) TYPE=InnoDB

If the InnoDB type is available, the last part of the SHOW statement will say TYPE=InnoDB. If not, MySQL will create the table using MyISAM (the default table type), and the last part of the statement will say TYPE=MyISAM instead. (You can also use SHOW TABLE STATUS to check the type of a table.)

In the event that your MySQL server doesn't include the transaction-safe table handlers you want to use, you'll need to replace it with one that does. If you install MySQL from a source distribution, the installation instructions indicate which configuration flags to use to enable the desired handlers. If you prefer binaries, be sure to install a distribution that was built to include BDB or InnoDB handlers.

After you've verified that your server supports the appropriate transactional table types, your applications can go ahead and use them:

• If you're writing a new application, you can create its tables to have a transactional type right from the beginning. All that's necessary to create such a table is to add TYPE = tbl_type to the end of the CREATE TABLE statement:

  CREATE TABLE t1 (i INT) TYPE = BDB;
  CREATE TABLE t2 (i INT) TYPE = INNODB;

• If you modify an existing application in such a way that it becomes necessary to perform transactions with existing tables that were not originally created with transactions in mind, you can change the tables to have a different type. For example, the ISAM and MyISAM types are non-transactional. 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 t is a MyISAM table. To make it an InnoDB table, do this:

  ALTER TABLE t TYPE = INNODB;

  Note that changing a table's type to support transactions may affect its behavior in other ways. For example, MyISAM tables provide more flexible handling of AUTO_INCREMENT columns than do other table types. If you rely on MyISAM-only sequence features, changing the table type will cause problems. See Chapter 11 for more information.

If your server does not support transactions and you cannot replace it with one that does, you may be able to achieve somewhat the same effect in other ways. Sometimes it's possible to lock your tables across multiple statements using LOCK and UNLOCK. This prevents other clients from interfering, although there is no rollback if any of the statements fail. Another alternative may be to rewrite queries so that they don't require transactions. See Recipe 15.9 for information about both types of workarounds.