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.