Database Reference
In-Depth Information
mysql>
SELECT ENGINE FROM INFORMATION_SCHEMA.ENGINES
->
WHERE SUPPORT IN ('YES','DEFAULT') AND TRANSACTIONS='YES';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
If MySQL Cluster is enabled, you'll also see a line that says
ndbcluster
.
Transactional engines are those that have a
TRANSACTIONS
value of
YES
; those actually
usable have a
SUPPORT
value of
YES
or
DEFAULT
.
After determining which transactional storage engines are available, to create a table
that uses a given engine, add an
ENGINE
=
tbl_engine
clause to your
CREATE
TABLE
statement:
CREATE
TABLE
t
(
i
INT
)
ENGINE
=
InnoDB
;
If you need to modify an existing application to perform transactions, but it uses non‐
transactional tables, you can alter the tables to use a transactional storage engine. For
example, MyISAM tables are nontransactional and 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 that
t
is a MyISAM
table. To make it an InnoDB table, do this:
ALTER
TABLE
t
ENGINE
=
InnoDB
;
One thing to consider before altering a table is that changing it to use a transactional
storage engine may affect its behavior in other ways. For example, the MyISAM engine
provides more flexible handling of
AUTO_INCREMENT
columns than do other storage
engines. If you rely on MyISAM-only sequence features, changing the storage engine
will cause problems.
17.2. Performing Transactions Using SQL
Problem
A set of statements must succeed or fail as a unit—that is, you require a transaction.
Solution
Manipulate MySQL's auto-commit mode to enable multiple-statement transactions, and
then commit or roll back the statements depending on whether they succeed or fail.
