A number of limitations exist in MySQL Cluster with regard to the handling of transactions. These include the following:
Transaction isolation level.
The NDBCLUSTER
storage engine
supports only the READ
COMMITTED
transaction isolation level.
(InnoDB
, for example, supports
READ COMMITTED
,
READ UNCOMMITTED
,
REPEATABLE READ
, and
SERIALIZABLE
.) See
Section 15.5.3.4, “MySQL Cluster Backup Troubleshooting”,
for information on how this can affect backing up and
restoring Cluster databases.)
Transactions and BLOB
or
TEXT
columns.
NDBCLUSTER
stores only part
of a column value that uses any of MySQL's
BLOB
or
TEXT
data types in the
table visible to MySQL; the remainder of the
BLOB
or
TEXT
is stored in a
separate internal table that is not accessible to MySQL.
This gives rise to two related issues of which you should
be aware whenever executing
SELECT
statements on tables
that contain columns of these types:
For any SELECT
from a
MySQL Cluster table: If the
SELECT
includes a
BLOB
or
TEXT
column, the
READ COMMITTED
transaction isolation level is converted to a read with
read lock. This is done to guarantee consistency.
For any SELECT
which uses
a primary key lookup or unique key lookup to retrieve
any columns that use any of the
BLOB
or
TEXT
data types and that
is executed within a transaction, a shared read lock is
held on the table for the duration of the transaction
— that is, until the transaction is either
committed or aborted. This does not occur for queries
that use index or table scans.
For example, consider the table t
defined by the following CREATE
TABLE
statement:
CREATE TABLE t ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT NOT NULL, c INT NOT NULL, d TEXT, INDEX i(b), UNIQUE KEY u(c) ) ENGINE = NDB,
Either of the following queries on t
causes a shared read lock, because the first query uses
a primary key lookup and the second uses a unique key
lookup:
SELECT * FROM t WHERE a = 1; SELECT * FROM t WHERE c = 1;
However, none of the four queries shown here causes a shared read lock:
SELECT * FROM t WHERE b 1; SELECT * FROM t WHERE d = '1'; SELECT * FROM t; SELECT b,c WHERE a = 1;
This is because, of these four queries, the first uses
an index scan, the second and third use table scans, and
the fourth, while using a primary key lookup, does not
retrieve the value of any
BLOB
or
TEXT
columns.
You can help minimize issues with shared read locks by
avoiding queries that use primary key lookups or unique
key lookups to retrieve
BLOB
or
TEXT
columns, or, in
cases where such queries are not avoidable, by
committing transactions as soon as possible afterwards.
We are working on overcoming this limitation in a future MySQL Cluster release (see Bug#49190); however, we do not plan to backport any fix for this issue to MySQL 4.1 or MySQL 5.0.
Rollbacks.
There are no partial transactions, and no partial
rollbacks of transactions. A duplicate key or similar
error aborts the entire transaction, and subsequent
statements raise ERROR 1296 (HY000): Got error
4350 'Transaction already aborted' from
NDBCLUSTER. In such cases, you must issue an
explicit
ROLLBACK
and retry the entire transaction.
This behavior differs from that of other transactional
storage engines such as InnoDB
that may
roll back individual statements.
Transactions and memory usage. As noted elsewhere in this chapter, MySQL Cluster does not handle large transactions well; it is better to perform a number of small transactions with a few operations each than to attempt a single large transaction containing a great many operations. Among other considerations, large transactions require very large amounts of memory. Because of this, the transactional behavior of a number of MySQL statements is effected as described in the following list:
TRUNCATE TABLE
is not
transactional when used on
NDB
tables. If a
TRUNCATE TABLE
fails to
empty the table, then it must be re-run until it is
successful.
DELETE FROM
(even with no
WHERE
clause) is
transactional. For tables containing a great many rows,
you may find that performance is improved by using
several DELETE FROM ... LIMIT ...
statements to “chunk” the delete operation.
If your objective is to empty the table, then you may
wish to use TRUNCATE
TABLE
instead.
LOAD DATA
statements.
LOAD DATA
INFILE
is not transactional when used on
NDB
tables. LOAD
DATA FROM MASTER
is not supported in MySQL
Cluster.
When executing a
LOAD DATA
INFILE
statement, the
NDB
engine performs
commits at irregular intervals that enable better
utilization of the communication network. It is not
possible to know ahead of time when such commits take
place.
ALTER TABLE
and transactions.
When copying an NDB
table
as part of an ALTER
TABLE
, the creation of the copy is
nontransactional. (In any case, this operation is
rolled back when the copy is deleted.)
User Comments
Add your own comment.