89559

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:

1.    For any SELECT from a MySQL Cluster table: If the SELECT includes a BLOB or TEXT column,

the READ COMMITTED [1502] transaction isolation level is converted to a read with read lock.

This is done to guarantee consistency.

2.    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 seans, even against NDB tables

having BLOB or TEXT columns.

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-l;

SELECT * FROM t WHERE c - 1;

However, nonę of the four queries shown here causes a shared read lock