MySQL :: MySQL NDB Cluster API Developer Guide

1.4.2.3.1 NDB Access Types

The data node process has a number of simple constructs which are used to access the data in an NDB Cluster. We have created a very simple benchmark to check the performance of each of these.

There are four access methods:

Primary key access. This is access of a record through its primary key. In the simplest case, only one record is accessed at a time, which means that the full cost of setting up a number of TCP/IP messages and a number of costs for context switching are borne by this single request. In the case where multiple primary key accesses are sent in one batch, those accesses share the cost of setting up the necessary TCP/IP messages and context switches. If the TCP/IP messages are for different destinations, additional TCP/IP messages need to be set up.
Unique key access. Unique key accesses are similar to primary key accesses, except that a unique key access is executed as a read on an index table followed by a primary key access on the table. However, only one request is sent from the MySQL Server, and the read of the index table is handled by the data node. Such requests also benefit from batching.
Full table scan. When no indexes exist for a lookup on a table, a full table scan is performed. This is sent as a single request to the ndbd process, which then divides the table scan into a set of parallel scans on all NDB data node processes.
Range scan using ordered index. When an ordered index is used, it performs a scan in the same manner as the full table scan, except that it scans only those records which are in the range used by the query transmitted by the MySQL server (SQL node). All partitions are scanned in parallel when all bound index attributes include all attributes in the partitioning key.

1.4.2.3.2 Single-row operations

After the operation is created using NdbTransaction::getNdbOperation() or NdbTransaction::getNdbIndexOperation(), it is defined in the following three steps:

Specify the standard operation type using NdbOperation::readTuple().
Specify search conditions using NdbOperation::equal().
Specify attribute actions using NdbOperation::getValue().

Here are two brief examples illustrating this process. For the sake of brevity, we omit error handling.

This first example uses an NdbOperation:

// 1. Retrieve table object
myTable= myDict->getTable("MYTABLENAME");

// 2. Create an NdbOperation on this table
myOperation= myTransaction->getNdbOperation(myTable);

// 3. Define the operation's type and lock mode
myOperation->readTuple(NdbOperation::LM_Read);

// 4. Specify search conditions
myOperation->equal("ATTR1", i);

// 5. Perform attribute retrieval
myRecAttr= myOperation->getValue("ATTR2", NULL);

For additional examples of this sort, see Section 2.5.2, “NDB API Example Using Synchronous Transactions”.

The second example uses an NdbIndexOperation:

// 1. Retrieve index object
myIndex= myDict->getIndex("MYINDEX", "MYTABLENAME");

// 2. Create
myOperation= myTransaction->getNdbIndexOperation(myIndex);

// 3. Define type of operation and lock mode
myOperation->readTuple(NdbOperation::LM_Read);

// 4. Specify Search Conditions
myOperation->equal("ATTR1", i);

// 5. Attribute Actions
myRecAttr = myOperation->getValue("ATTR2", NULL);

Another example of this second type can be found in Section 2.5.6, “NDB API Example: Using Secondary Indexes in Scans”.

We now discuss in somewhat greater detail each step involved in the creation and use of synchronous transactions.

Define single row operation type. The following operation types are supported:
- NdbOperation::insertTuple(): Inserts a nonexisting tuple.
- NdbOperation::writeTuple(): Updates a tuple if one exists, otherwise inserts a new tuple.
- NdbOperation::updateTuple(): Updates an existing tuple.
- NdbOperation::deleteTuple(): Deletes an existing tuple.
- NdbOperation::readTuple(): Reads an existing tuple using the specified lock mode.
All of these operations operate on the unique tuple key. When NdbIndexOperation is used, then each of these operations operates on a defined unique hash index.

Note

If you want to define multiple operations within the same transaction, then you need to call NdbTransaction::getNdbOperation() or NdbTransaction::getNdbIndexOperation() for each operation.
Specify Search Conditions. The search condition is used to select tuples. Search conditions are set using NdbOperation::equal().
Specify Attribute Actions. Next, it is necessary to determine which attributes should be read or updated. It is important to remember that:
- Deletes can neither read nor set values, but only delete them.
- Reads can only read values.
- Updates can only set values. Normally the attribute is identified by name, but it is also possible to use the attribute's identity to determine the attribute.
NdbOperation::getValue() returns an NdbRecAttr object containing the value as read. To obtain the actual value, one of two methods can be used; the application can either
- Use its own memory (passed through a pointer aValue) to NdbOperation::getValue(), or
- receive the attribute value in an NdbRecAttr object allocated by the NDB API.
The NdbRecAttr object is released when Ndb::closeTransaction() is called. For this reason, the application cannot reference this object following any subsequent call to Ndb::closeTransaction(). Attempting to read data from an NdbRecAttr object before calling NdbTransaction::execute() yields an undefined result.

1.4.2.3.3 Scan Operations

Scans are roughly the equivalent of SQL cursors, providing a means to perform high-speed row processing. A scan can be performed on either a table (using an NdbScanOperation) or an ordered index (by means of an NdbIndexScanOperation).

Scan operations have the following characteristics:

They can perform read operations which may be shared, exclusive, or dirty.
They can potentially work with multiple rows.
They can be used to update or delete multiple rows.
They can operate on several nodes in parallel.

After the operation is created using NdbTransaction::getNdbScanOperation() or NdbTransaction::getNdbIndexScanOperation(), it is carried out as follows:

Define the standard operation type, using NdbScanOperation::readTuples().

Note

See NdbScanOperation::readTuples(), for additional information about deadlocks which may occur when performing simultaneous, identical scans with exclusive locks.
Specify search conditions, using NdbScanFilter, NdbIndexScanOperation::setBound(), or both.
Specify attribute actions using NdbOperation::getValue().
Execute the transaction using NdbTransaction::execute().
Traverse the result set by means of successive calls to NdbScanOperation::nextResult().

Here are two brief examples illustrating this process. Once again, in order to keep things relatively short and simple, we forego any error handling.

This first example performs a table scan using an NdbScanOperation:

// 1. Retrieve a table object
myTable= myDict->getTable("MYTABLENAME");

// 2. Create a scan operation (NdbScanOperation) on this table
myOperation= myTransaction->getNdbScanOperation(myTable);

// 3. Define the operation's type and lock mode
myOperation->readTuples(NdbOperation::LM_Read);

// 4. Specify search conditions
NdbScanFilter sf(myOperation);
sf.begin(NdbScanFilter::OR);
sf.eq(0, i);   // Return rows with column 0 equal to i or
sf.eq(1, i+1); // column 1 equal to (i+1)
sf.end();

// 5. Retrieve attributes
myRecAttr= myOperation->getValue("ATTR2", NULL);

The second example uses an NdbIndexScanOperation to perform an index scan:

// 1. Retrieve index object
myIndex= myDict->getIndex("MYORDEREDINDEX", "MYTABLENAME");

// 2. Create an operation (NdbIndexScanOperation object)
myOperation= myTransaction->getNdbIndexScanOperation(myIndex);

// 3. Define type of operation and lock mode
myOperation->readTuples(NdbOperation::LM_Read);

// 4. Specify search conditions
// All rows with ATTR1 between i and (i+1)
myOperation->setBound("ATTR1", NdbIndexScanOperation::BoundGE, i);
myOperation->setBound("ATTR1", NdbIndexScanOperation::BoundLE, i+1);

// 5. Retrieve attributes
myRecAttr = MyOperation->getValue("ATTR2", NULL);

Some additional discussion of each step required to perform a scan follows:

Define Scan Operation Type. It is important to remember that only a single operation is supported for each scan operation (NdbScanOperation::readTuples() or NdbIndexScanOperation::readTuples()).

Note

If you want to define multiple scan operations within the same transaction, then you need to call NdbTransaction::getNdbScanOperation() or NdbTransaction::getNdbIndexScanOperation() separately for each operation.
Specify Search Conditions. The search condition is used to select tuples. If no search condition is specified, the scan will return all rows in the table. The search condition can be an NdbScanFilter (which can be used on both NdbScanOperation and NdbIndexScanOperation) or bounds (which can be used only on index scans - see NdbIndexScanOperation::setBound()). An index scan can use both NdbScanFilter and bounds.

Note

When NdbScanFilter is used, each row is examined, whether or not it is actually returned. However, when using bounds, only rows within the bounds will be examined.
Specify Attribute Actions. Next, it is necessary to define which attributes should be read. As with transaction attributes, scan attributes are defined by name, but it is also possible to use the attributes' identities to define attributes as well. As discussed elsewhere in this document (see Section 1.4.2.2, “Synchronous Transactions”), the value read is returned by the NdbOperation::getValue() method as an NdbRecAttr object.

1.4.2.3.4 Using Scans to Update or Delete Rows

Scanning can also be used to update or delete rows. This is performed as follows:

Scanning with exclusive locks using NdbOperation::LM_Exclusive.
(When iterating through the result set:) For each row, optionally calling either NdbScanOperation::updateCurrentTuple() or NdbScanOperation::deleteCurrentTuple().
(If performing NdbScanOperation::updateCurrentTuple():) Setting new values for records simply by using NdbOperation::setValue(). NdbOperation::equal() should not be called in such cases, as the primary key is retrieved from the scan.

Important

The update or delete is not actually performed until the next call to NdbTransaction::execute() is made, just as with single row operations. NdbTransaction::execute() also must be called before any locks are released; for more information, see Section 1.4.2.3.5, “Lock Handling with Scans”.

Features Specific to Index Scans. When performing an index scan, it is possible to scan only a subset of a table using NdbIndexScanOperation::setBound(). In addition, result sets can be sorted in either ascending or descending order, using NdbIndexScanOperation::readTuples(). Note that rows are returned unordered by default unless sorted is set to true.

It is also important to note that, when using NdbIndexScanOperation::BoundEQ (see NdbIndexScanOperation::BoundType) with a partition key, only fragments containing rows will actually be scanned. Finally, when performing a sorted scan, any value passed as the NdbIndexScanOperation::readTuples() method's parallel argument will be ignored and maximum parallelism will be used instead. In other words, all fragments which it is possible to scan are scanned simultaneously and in parallel in such cases.

1.4.2.3.5 Lock Handling with Scans

Performing scans on either a table or an index has the potential to return a great many records; however, Ndb locks only a predetermined number of rows per fragment at a time. The number of rows locked per fragment is controlled by the batch parameter passed to NdbScanOperation::readTuples().

In order to enable the application to handle how locks are released, NdbScanOperation::nextResult() has a Boolean parameter fetchAllowed. If NdbScanOperation::nextResult() is called with fetchAllowed equal to false, then no locks may be released as result of the function call. Otherwise the locks for the current batch may be released.

This next example shows a scan delete that handles locks in an efficient manner. For the sake of brevity, we omit error-handling.

int check;

// Outer loop for each batch of rows
while((check = MyScanOperation->nextResult(true)) == 0)
{
  do
  {
    // Inner loop for each row within the batch
    MyScanOperation->deleteCurrentTuple();
  }
  while((check = MyScanOperation->nextResult(false)) == 0);

  // When there are no more rows in the batch, execute all defined deletes
  MyTransaction->execute(NoCommit);
}

For a more complete example of a scan, see Section 2.5.5, “NDB API Basic Scanning Example”.

1.4.2.3.6 Error Handling

Errors can occur either when operations making up a transaction are being defined, or when the transaction is actually being executed. Catching and handling either sort of error requires testing the value returned by NdbTransaction::execute(), and then, if an error is indicated (that is, if this value is equal to -1), using the following two methods in order to identify the error's type and location:

NdbTransaction::getNdbErrorOperation() returns a reference to the operation causing the most recent error.
NdbTransaction::getNdbErrorLine() yields the method number of the erroneous method in the operation, starting with 1.

This short example illustrates how to detect an error and to use these two methods to identify it:

theTransaction = theNdb->startTransaction();
theOperation = theTransaction->getNdbOperation("TEST_TABLE");
if(theOperation == NULL)
  goto error;

theOperation->readTuple(NdbOperation::LM_Read);
theOperation->setValue("ATTR_1", at1);
theOperation->setValue("ATTR_2", at1);  //  Error occurs here
theOperation->setValue("ATTR_3", at1);
theOperation->setValue("ATTR_4", at1);

if(theTransaction->execute(Commit) == -1)
{
  errorLine = theTransaction->getNdbErrorLine();
  errorOperation = theTransaction->getNdbErrorOperation();
}

Here, errorLine is 3, as the error occurred in the third method called on the NdbOperation object (in this case, theOperation). If the result of NdbTransaction::getNdbErrorLine() is 0, then the error occurred when the operations were executed. In this example, errorOperation is a pointer to the object theOperation. The NdbTransaction::getNdbError() method returns an NdbError object providing information about the error.

Note

Transactions are not automatically closed when an error occurs. You must call Ndb::closeTransaction() or NdbTransaction::close() to close the transaction.

See Ndb::closeTransaction(), and NdbTransaction::close().

One recommended way to handle a transaction failure (that is, when an error is reported) is as shown here:

Roll back the transaction by calling NdbTransaction::execute() with a special ExecType value for the type parameter.

See NdbTransaction::execute() and NdbTransaction::ExecType, for more information about how this is done.
Close the transaction by calling NdbTransaction::close().
If the error was temporary, attempt to restart the transaction.

Several errors can occur when a transaction contains multiple operations which are simultaneously executed. In this case the application must go through all operations and query each of their NdbError objects to find out what really happened.