Skip navigation links
MySQL 5.0 Reference Manual :: 17 MySQL Cluster :: 17.1 MySQL Cluster Overview :: 17.1.5 Known Limitations of MySQL Cluster :: 17.1.5.3 Limits Relating to Transaction Handling in MySQL Cluster
17.1.5.4 MySQL Cluster Error Handling »
Section Navigation      [Toggle]

17.1.5.3. Limits Relating to Transaction Handling in MySQL Cluster

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 17.5.3.4, “MySQL Cluster Backup Troubleshooting”, for information on how this can affect backing up and restoring Cluster databases.)

    Important

    If a SELECT from a Cluster table 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, due to the fact that parts of the values stored in columns of these types are actually read from a separate table.

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

      Important

      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.

      LOAD DATA FROM MASTER is not supported in MySQL Cluster.

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