<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="mysql-cluster-limitations-transactions">
      </a>
      25.2.7.3 Limits Relating to Transaction Handling in NDB Cluster
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045123450400">
  </a>
  <p>
   A number of limitations exist in NDB Cluster with regard to the
        handling of transactions. These include the following:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <b>
       Transaction isolation level.
      </b>
      <a class="indexterm" name="idm46045123446960">
      </a>
      <a class="indexterm" name="idm46045123445456">
      </a>
      <a class="indexterm" name="idm46045123443952">
      </a>
      The
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDBCLUSTER
       </code>
      </a>
      storage engine
              supports only the
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
       <code class="literal">
        READ
              COMMITTED
       </code>
      </a>
      transaction isolation level.
              (
      <code class="literal">
       InnoDB
      </code>
      , for example, supports
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
       <code class="literal">
        READ COMMITTED
       </code>
      </a>
      ,
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-uncommitted">
       <code class="literal">
        READ UNCOMMITTED
       </code>
      </a>
      ,
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_repeatable-read">
       <code class="literal">
        REPEATABLE READ
       </code>
      </a>
      , and
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_serializable">
       <code class="literal">
        SERIALIZABLE
       </code>
      </a>
      .) You
              should keep in mind that
      <code class="literal">
       NDB
      </code>
      implements
      <code class="literal">
       READ COMMITTED
      </code>
      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.
     </p>
     <p>
      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
      <span class="quote">
       “
       <span class="quote">
        before
       </span>
       ”
      </span>
      values,
      <span class="quote">
       “
       <span class="quote">
        after
       </span>
       ”
      </span>
      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.
     </p>
     <p>
      To ensure that a given transaction reads only before or
            after values, you can impose row locks using
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT ... LOCK IN
            SHARE MODE
       </code>
      </a>
      . In such cases, the lock is held until
            the owning transaction is committed. Using row locks can
            also cause the following issues:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Increased frequency of lock wait timeout errors, and
                reduced concurrency
        </p>
       </li>
       <li class="listitem">
        <p>
         Increased transaction processing overhead due to reads
                requiring a commit phase
        </p>
       </li>
       <li class="listitem">
        <p>
         Possibility of exhausting the available number of
                concurrent locks, which is limited by
         <a class="link" href="mysql-cluster-ndbd-definition.html#ndbparam-ndbd-maxnoofconcurrentoperations">
          <code class="literal">
           MaxNoOfConcurrentOperations
          </code>
         </a>
        </p>
       </li>
      </ul>
     </div>
     <p>
      <code class="literal">
       NDB
      </code>
      uses
      <code class="literal">
       READ
            COMMITTED
      </code>
      for all reads unless a modifier such as
      <code class="literal">
       LOCK IN SHARE MODE
      </code>
      or
      <code class="literal">
       FOR
            UPDATE
      </code>
      is used.
      <code class="literal">
       LOCK IN SHARE
            MODE
      </code>
      causes shared row locks to be used;
      <code class="literal">
       FOR UPDATE
      </code>
      causes exclusive row locks to
            be used. Unique key reads have their locks upgraded
            automatically by
      <code class="literal">
       NDB
      </code>
      to ensure a
            self-consistent read;
      <code class="literal">
       BLOB
      </code>
      reads also
            employ extra locking for consistency.
     </p>
     <p>
      See
      <a class="xref" href="mysql-cluster-backup-troubleshooting.html" title="25.6.8.4 NDB Cluster Backup Troubleshooting">
       Section 25.6.8.4, “NDB Cluster Backup Troubleshooting”
      </a>
      ,
            for information on how NDB Cluster's implementation of
            transaction isolation level can affect backup and
            restoration of
      <code class="literal">
       NDB
      </code>
      databases.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Transactions and BLOB or TEXT columns.
      </b>
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDBCLUSTER
       </code>
      </a>
      stores only part
              of a column value that uses any of MySQL's
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      or
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      data types in the
              table visible to MySQL; the remainder of the
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        BLOB
       </code>
      </a>
      or
      <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
       <code class="literal">
        TEXT
       </code>
      </a>
      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
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statements on tables
              that contain columns of these types:
     </p>
     <div class="orderedlist">
      <ol class="orderedlist" type="1">
       <li class="listitem">
        <p>
         For any
         <a class="link" href="select.html" title="15.2.13 SELECT Statement">
          <code class="literal">
           SELECT
          </code>
         </a>
         from an
                NDB Cluster table: If the
         <a class="link" href="select.html" title="15.2.13 SELECT Statement">
          <code class="literal">
           SELECT
          </code>
         </a>
         includes a
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           BLOB
          </code>
         </a>
         or
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           TEXT
          </code>
         </a>
         column, the
         <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
          <code class="literal">
           READ COMMITTED
          </code>
         </a>
         transaction isolation level is converted to a read with
                read lock. This is done to guarantee consistency.
        </p>
       </li>
       <li class="listitem">
        <p>
         For any
         <a class="link" href="select.html" title="15.2.13 SELECT Statement">
          <code class="literal">
           SELECT
          </code>
         </a>
         which uses
                a unique key lookup to retrieve any columns that use any
                of the
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           BLOB
          </code>
         </a>
         or
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           TEXT
          </code>
         </a>
         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.
        </p>
        <p>
         This issue does not occur for queries that use index or
                table scans, even against
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         tables having
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           BLOB
          </code>
         </a>
         or
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           TEXT
          </code>
         </a>
         columns.
        </p>
        <p>
         For example, consider the table
         <code class="literal">
          t
         </code>
         defined by the following
         <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
          <code class="literal">
           CREATE
                TABLE
          </code>
         </a>
         statement:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa10347648"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>
    a <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    b <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    c <span class="token datatype">INT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    d <span class="token datatype">TEXT</span><span class="token punctuation">,</span>
    <span class="token keyword">INDEX</span> i<span class="token punctuation">(</span>b<span class="token punctuation">)</span><span class="token punctuation">,</span>
    <span class="token keyword">UNIQUE</span> <span class="token keyword">KEY</span> u<span class="token punctuation">(</span>c<span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span> <span class="token operator">=</span> <span class="token keyword">NDB</span><span class="token punctuation">,</span></code></pre>
        </div>
        <p>
         The following query on
         <code class="literal">
          t
         </code>
         causes a
                shared read lock, because it uses a unique key lookup:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa58830699"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> c <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
        </div>
        <p>
         However, none of the four queries shown here causes a
                shared read lock:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51658560"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t <span class="token keyword">WHERE</span> d <span class="token operator">=</span> <span class="token string">'1'</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> t<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> b<span class="token punctuation">,</span>c <span class="token keyword">WHERE</span> a <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
        </div>
        <p>
         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
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           BLOB
          </code>
         </a>
         or
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           TEXT
          </code>
         </a>
         columns.
        </p>
        <p>
         You can help minimize issues with shared read locks by
                avoiding queries that use unique key lookups that
                retrieve
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           BLOB
          </code>
         </a>
         or
         <a class="link" href="blob.html" title="13.3.4 The BLOB and TEXT Types">
          <code class="literal">
           TEXT
          </code>
         </a>
         columns, or, in
                cases where such queries are not avoidable, by
                committing transactions as soon as possible afterward.
        </p>
       </li>
      </ol>
     </div>
    </li>
    <li class="listitem">
     <p>
      <b>
       Unique key lookups and transaction isolation.
      </b>
      Unique indexes are implemented in
      <code class="literal">
       NDB
      </code>
      using a hidden index table which is maintained internally.
              When a user-created
      <code class="literal">
       NDB
      </code>
      table is
              accessed using a unique index, the hidden index table is
              first read to find the primary key that is then used to
              read the user-created table. To avoid modification of the
              index during this double-read operation, the row found in
              the hidden index table is locked. When a row referenced by
              a unique index in the user-created
      <code class="literal">
       NDB
      </code>
      table is updated, the hidden index table is subject to an
              exclusive lock by the transaction in which the update is
              performed. This means that any read operation on the same
              (user-created)
      <code class="literal">
       NDB
      </code>
      table must wait for
              the update to complete. This is true even when the
              transaction level of the read operation is
      <a class="link" href="innodb-transaction-isolation-levels.html#isolevel_read-committed">
       <code class="literal">
        READ COMMITTED
       </code>
      </a>
      .
     </p>
     <p>
      One workaround which can be used to bypass potentially
            blocking reads is to force the SQL node to ignore the unique
            index when performing the read. This can be done by using
            the
      <code class="literal">
       IGNORE INDEX
      </code>
      index hint as part of
            the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      statement reading
            the table (see
      <a class="xref" href="index-hints.html" title="10.9.4 Index Hints">
       Section 10.9.4, “Index Hints”
      </a>
      ). Because the
            MySQL server creates a shadowing ordered index for every
            unique index created in
      <code class="literal">
       NDB
      </code>
      , this lets
            the ordered index be read instead, and avoids unique index
            access locking. The resulting read is as consistent as a
            committed read by primary key, returning the last committed
            value at the time the row is read.
     </p>
     <p>
      Reading via an ordered index makes less efficient use of
            resources in the cluster, and may have higher latency.
     </p>
     <p>
      It is also possible to avoid using the unique index for
            access by querying for ranges rather than for unique values.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Rollbacks.
      </b>
      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.
     </p>
     <p>
      This behavior differs from that of other transactional
            storage engines such as
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      that may roll back individual statements.
     </p>
    </li>
    <li class="listitem">
     <p>
      <b>
       Transactions and memory usage.
      </b>
      <a class="indexterm" name="idm46045123358624">
      </a>
      <a class="indexterm" name="idm46045123357120">
      </a>
      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 affected as described in the following list:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
          <code class="literal">
           TRUNCATE TABLE
          </code>
         </a>
         is not
                transactional when used on
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         tables. If a
         <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
          <code class="literal">
           TRUNCATE TABLE
          </code>
         </a>
         fails to
                empty the table, then it must be re-run until it is
                successful.
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          DELETE FROM
         </code>
         (even with no
         <code class="literal">
          WHERE
         </code>
         clause)
         <span class="emphasis">
          <em>
           is
          </em>
         </span>
         transactional. For tables containing a great many rows,
                you may find that performance is improved by using
                several
         <code class="literal">
          DELETE FROM ... LIMIT ...
         </code>
         statements to
         <span class="quote">
          “
          <span class="quote">
           chunk
          </span>
          ”
         </span>
         the delete operation.
                If your objective is to empty the table, then you may
                wish to use
         <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
          <code class="literal">
           TRUNCATE
                TABLE
          </code>
         </a>
         instead.
        </p>
       </li>
       <li class="listitem">
        <p>
         <b>
          LOAD DATA statements.
         </b>
         <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
          <code class="literal">
           LOAD DATA
          </code>
         </a>
         is not
                  transactional when used on
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         tables.
        </p>
        <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
         <div class="admon-title">
          Important
         </div>
         <p>
          When executing a
          <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
           <code class="literal">
            LOAD
                  DATA
           </code>
          </a>
          statement, the
          <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
           <code class="literal">
            NDB
           </code>
          </a>
          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.
         </p>
        </div>
       </li>
       <li class="listitem">
        <p>
         <b>
          ALTER TABLE and transactions.
         </b>
         When copying an
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         table
                  as part of an
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER
                  TABLE
          </code>
         </a>
         , the creation of the copy is
                  nontransactional. (In any case, this operation is
                  rolled back when the copy is deleted.)
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      <b>
       Transactions and the COUNT() function.
      </b>
      When using NDB Cluster Replication, it is not possible to
              guarantee the transactional consistency of the
      <a class="link" href="aggregate-functions.html#function_count">
       <code class="literal">
        COUNT()
       </code>
      </a>
      function on the
              replica. In other words, when performing on the source a
              series of statements
              (
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      ,
      <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
       <code class="literal">
        DELETE
       </code>
      </a>
      , or both) that
              changes the number of rows in a table within a single
              transaction, executing
      <code class="literal">
       SELECT COUNT(*) FROM
       <em class="replaceable">
        <code>
         table
        </code>
       </em>
      </code>
      queries on the
              replica may yield intermediate results. This is due to the
              fact that
      <code class="literal">
       SELECT COUNT(...)
      </code>
      may perform
              dirty reads, and is not a bug in the
      <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
       <code class="literal">
        NDB
       </code>
      </a>
      storage engine. (See Bug
              #31321 for more information.)
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
