You want to use transactions, but don't know whether your MySQL server supports them.
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.
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:
CREATE TABLE t1 (i INT) TYPE = BDB; CREATE TABLE t2 (i INT) TYPE = INNODB;
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.
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References