Transactions and BLOB or TEXT columns-opracowanie

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 scans, 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 = 1;
SELECT * FROM t WHERE c = 1;
However, none of the four queries shown here causes a shared read lock
... zobacz całą notatkę