18.1.6.3 Limits Relating to Transaction Handling in NDB Cluster

A number of limitations exist in NDB 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.) You
should keep in mind that NDB implements
READ COMMITTED on a per-row basis; when
a read request arrives at the data node storing the row,
what is returned is the last committed version of the row
at that time.

Uncommitted data is never returned, but when a transaction
modifying a number of rows commits concurrently with a
transaction reading the same rows, the transaction
performing the read can observe “before”
values, “after” values, or both, for different
rows among these, due to the fact that a given row read
request can be processed either before or after the commit
of the other transaction.

To ensure that a given transaction reads only before or
after values, you can impose row locks using
SELECT ... LOCK IN
SHARE MODE. In such cases, the lock is held until
the owning transaction is committed. Using row locks can
also cause the following issues:

NDB uses READ
COMMITTED for all reads unless a modifier such as
LOCK IN SHARE MODE or FOR
UPDATE is used. LOCK IN SHARE
MODE causes shared row locks to be used;
FOR UPDATE causes exclusive row locks to
be used. Unique key reads have their locks upgraded
automatically by NDB to ensure a
self-consistent read; BLOB reads also
employ extra locking for consistency.

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 an
NDB 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 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 issue does not occur for queries that use index or
table scans, even against
NDB tables having
BLOB or
TEXT columns.

For example, consider the table t
defined by the following CREATE
TABLE statement:

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 unique key lookups that
retrieve BLOB or
TEXT columns, or, in
cases where such queries are not avoidable, by
committing transactions as soon as possible afterward.

Rollbacks.
There are no partial transactions, and no partial
rollbacks of transactions. A duplicate key or similar
error causes the entire transaction to be rolled back.

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, NDB 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:

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.

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

Transactions and the COUNT() function.
When using NDB Cluster Replication, it is not possible to
guarantee the transactional consistency of the
COUNT() function on the slave. In other
words, when performing on the master a series of
statements (INSERT,
DELETE, or both) that
changes the number of rows in a table within a single
transaction, executing SELECT COUNT(*) FROM
table queries on the
slave may yield intermediate results. This is due to the
fact that SELECT COUNT(...) may perform
dirty reads, and is not a bug in the
NDB storage engine. (See Bug
#31321 for more information.)

Another transaction related limitation: A long running transaction holding some lock can block a node restart. A restarting node needs to do a "lock everything" for a very short period of time to complete start phase 5. When stopping a node ongoing transactions blocking the stop operation will get terminated after a 5 second wait period, a starting node will not terminate any active transactions but will gracefully and silently wait for all transactions to complete, so an endless transaction that does not run into an inactivity timeout itself can block a node restart forever ...