Rechercher dans le manuel MySQL
22.1.7.3 Limits Relating to Transaction Handling in NDB Cluster
A number of limitations exist in NDB Cluster with regard to the handling of transactions. These include the following:
Transaction isolation level. The
NDBCLUSTER
storage engine supports only theREAD COMMITTED
transaction isolation level. (InnoDB
, for example, supportsREAD COMMITTED
,READ UNCOMMITTED
,REPEATABLE READ
, andSERIALIZABLE
.) You should keep in mind thatNDB
implementsREAD COMMITTED
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.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 “before” values, “after” 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.
To ensure that a given transaction reads only before or after values, you can impose row locks using
SELECT ... LOCK IN SHARE MODE
. In such cases, the lock is held until the owning transaction is committed. Using row locks can also cause the following issues:Increased frequency of lock wait timeout errors, and reduced concurrency
Increased transaction processing overhead due to reads requiring a commit phase
Possibility of exhausting the available number of concurrent locks, which is limited by
MaxNoOfConcurrentOperations
NDB
usesREAD COMMITTED
for all reads unless a modifier such asLOCK IN SHARE MODE
orFOR UPDATE
is used.LOCK IN SHARE MODE
causes shared row locks to be used;FOR UPDATE
causes exclusive row locks to be used. Unique key reads have their locks upgraded automatically byNDB
to ensure a self-consistent read;BLOB
reads also employ extra locking for consistency.See Section 22.5.3.4, “NDB Cluster Backup Troubleshooting”, for information on how NDB Cluster's implementation of transaction isolation level can affect backup and restoration of
NDB
databases.Transactions and BLOB or TEXT columns.
NDBCLUSTER
stores only part of a column value that uses any of MySQL'sBLOB
orTEXT
data types in the table visible to MySQL; the remainder of theBLOB
orTEXT
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 executingSELECT
statements on tables that contain columns of these types:For any
SELECT
from an NDB Cluster table: If theSELECT
includes aBLOB
orTEXT
column, theREAD COMMITTED
transaction isolation level is converted to a read with read lock. This is done to guarantee consistency.For any
SELECT
which uses a unique key lookup to retrieve any columns that use any of theBLOB
orTEXT
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 havingBLOB
orTEXT
columns.For example, consider the table
t
defined by the followingCREATE TABLE
statement:- d TEXT,
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:However, none of the four queries shown here causes a shared read lock:
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
orTEXT
columns.You can help minimize issues with shared read locks by avoiding queries that use unique key lookups that retrieve
BLOB
orTEXT
columns, or, in cases where such queries are not avoidable, by committing transactions as soon as possible afterward.
Rollbacks. 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.
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, 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:
TRUNCATE TABLE
is not transactional when used onNDB
tables. If aTRUNCATE TABLE
fails to empty the table, then it must be re-run until it is successful.DELETE FROM
(even with noWHERE
clause) is transactional. For tables containing a great many rows, you may find that performance is improved by using severalDELETE FROM ... LIMIT ...
statements to “chunk” the delete operation. If your objective is to empty the table, then you may wish to useTRUNCATE TABLE
instead.LOAD DATA statements.
LOAD DATA
is not transactional when used onNDB
tables.ALTER TABLE and transactions. When copying an
NDB
table as part of anALTER TABLE
, the creation of the copy is nontransactional. (In any case, this operation is rolled back when the copy is deleted.)
Transactions and the COUNT() function. When using NDB Cluster Replication, it is not possible to guarantee the transactional consistency of the
COUNT()
function on the slave. In other words, when performing on the master a series of statements (INSERT
,DELETE
, or both) that changes the number of rows in a table within a single transaction, executingSELECT COUNT(*) FROM
queries on the slave may yield intermediate results. This is due to the fact thattable
SELECT COUNT(...)
may perform dirty reads, and is not a bug in theNDB
storage engine. (See Bug #31321 for more information.)
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-mysql-cluster-limitations-transactions.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.