- What Are Transactions?
- Transaction-Safe Table Types in MySQL
- Summary
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
Transaction-Safe Table Types in MySQL
To use transactions in MySQL, you must use a transaction-safe table type. The default MySQL table type, MyISAM, does not support transactions. BerkeleyDB and InnoDB are the transaction-safe table types available in the open source MySQL, version 3.23.34 and greater, whereas the Gemini table is used for transactions in NuSphere's Enhanced MySQL.
BerkeleyDB
BerkeleyDB is a product from Sleepycat Software (http://www.sleepycat.com/), which provides MySQL with a transaction-safe table type. Support for BerkeleyDB tables began with version 3.23.34 of the open source MySQL and requires a specific compilation parameter when compiling MySQL from source. Most users do not compile MySQL from source and instead rely on whatever has been installed by their Internet Service Provider. If this is true for you, there are two options: ask your Internet Service Provider to build a version of MySQL with support for BerkeleyDB table types, or download and install the MySQL-Max binary distribution for Windows or Linux/UNIX and work with the table type in a development environment.
If your MySQL installation supports BerkeleyDB tables, simply add a TYPE=BDB definition to the table creation statement. For example, the following code creates a BerkeleyDB table called test, with two fields:
mysql> CREATE TABLE test ( -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -> sample_text VARCHAR(25) -> ) TYPE=BDB;
The BerkeleyDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BerkeleyDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini table types.
You can learn more about the BerkeleyDB table type in the MySQL manual at http://www.mysql.com/doc/B/D/BDB.html.
InnoDB
InnoDB is the more popular and stable transaction-safe table type in open source MySQL and was designed specifically for high performance with large volumes of data, as well as overall CPU efficiency—two very important features in Web application development. As with the BerkeleyDB table type, InnoDB is not the default table type, and support for it did not appear until version 3.23.34 of the open source MySQL.
Support for InnoDB tables requires a specific compilation parameter when compiling MySQL from source. If your MySQL version does not have InnoDB support, ask your Internet Service Provider to build a version of MySQL with support for InnoDB table types, or download and install the MySQL-Max binary distribution for Windows or Linux/UNIX and work with the table type in a development environment.
If your MySQL installation supports InnoDB tables, simply add a TYPE=InnoDB definition to the table creation statement. For example, the following code creates an InnoDB table called test2, with two fields:
mysql> CREATE TABLE test2 ( -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -> sample_text VARCHAR(25) -> ) TYPE=InnoDB;
You can learn much more about the InnoDB table type in the MySQL manual at http://www.mysql.com/doc/I/n/InnoDB.html.
Gemini
The Gemini table type is available only in NuSphere's Enhanced MySQL product and not in the open source version of MySQL. If you have purchased Enhanced MySQL or have access to it through your Internet Service Provider, simply add a TYPE=Gemini definition to the table creation statement. For example, the following code creates a Gemini table called test2, with two fields:
mysql> CREATE TABLE test3 ( -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -> sample_text VARCHAR(25) -> ) TYPE=Gemini;
You can learn much more about the Gemini table type in the NuSphere Tech Library, at http://www.nusphere.com/products/tech_library.htm.