NDB API Programmers' Guide

This guide assumes a basic familiarity with MySQL Cluster concepts found on http://dev.mysql.com/doc/mysql/en/NDBCluster.html . Some of the fundamental ones are also described in section MySQL Cluster Concepts.

The NDB API is a MySQL Cluster application interface that implements transactions. The NDB API consists of the following fundamental classes:

In addition, the NDB API defines a structure NdbError, which contains the specification for an error.

There are also some auxiliary classes, which are listed in the class hierarchy.

The main structure of an application program is as follows:

  1. Connect to a cluster using the Ndb_cluster_connection object.
  2. Initiate a database connection by constructing and initialising one or more Ndb objects.
  3. Define and execute transactions using the NdbTransaction class.
  4. Delete Ndb objects.
  5. Terminate the connection to the cluster (terminate instance of Ndb_cluster_connection).

The procedure for using transactions is as follows:

  1. Start transaction (instantiate an NdbTransaction object)
  2. Add and define operations associated with the transaction using instances of one or more of the NdbOperation, NdbScanOperation, NdbIndexOperation, and NdbIndexScanOperation classes
  3. Execute transaction (call NdbTransaction::execute())

The operation can be of two different types, Commit or NoCommit. If the operation is of type NoCommit, then the application program executes the operation part of a transaction, but without actually committing the transaction. After executing a NoCommit operation, the program can continue to add and define more operations to the transaction for later execution.

If the operation is of type Commit, then the transaction is immediately committed. The transaction must be closed after it has been commited (event if commit fails), and no further addition or definition of operations for this transaction is allowed.

Synchronous Transactions

Synchronous transactions are defined and executed as follows:

  1. Start (create) the transaction, which is referenced by an NdbTransaction object (typically created using Ndb::startTransaction()). At this point, the transaction is only being defined, and is not yet sent to the NDB kernel.
  2. Define operations and add them to the transaction, using one or more of
  3. Execute the transaction, using the NdbTransaction::execute() method.
  4. Close the transaction (call Ndb::closeTransaction()).

For an example of this process, see the program listing in ndbapi_simple.cpp.

To execute several parallel synchronous transactions, one can either use multiple Ndb objects in several threads, or start multiple application programs.

Operations

A NdbTransaction consists of a list of operations, each of which is represented by an instance of NdbOperation, NdbScanOperation, NdbIndexOperation, or NdbIndexScanOperation.

Single row operations

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

  1. Define the standard operation type, using NdbOperation::readTuple()
  2. Specify search conditions, using NdbOperation::equal()
  3. 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
     myOperation= myTransaction->getNdbOperation(myTable);
    
     // 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);
For additional examples of this sort, see ndbapi_simple.cpp.

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 ndbapi_simple_index.cpp.

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

Step 1: Define single row operation type

The following operation types are supported:

  1. NdbOperation::insertTuple() : inserts a non-existing tuple
  2. NdbOperation::writeTuple() : updates an existing tuple if is exists, otherwise inserts a new tuple
  3. NdbOperation::updateTuple() : updates an existing tuple
  4. NdbOperation::deleteTuple() : deletes an existing tuple
  5. NdbOperation::readTuple() : reads an existing tuple with specified lock mode

All of these operations operate on the unique tuple key. (When NdbIndexOperation is used then all of these operations operate 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.

Step 2: Specify Search Conditions

The search condition is used to select tuples. Search conditions are set using NdbOperation::equal().

Step 3: Specify Attribute Actions

Next, it is necessary to determine which attributes should be read or updated. It is important to remember that:

NdbOperation::getValue() returns an NdbRecAttr object containing the read value. To obtain the actual value, one of two methods can be used; the application can either

The NdbRecAttr object is released when Ndb::closeTransaction() is called. Thus, 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.

Scan Operations

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

Scan operations are characterised by the following:

After the operation is created using NdbTransaction::getNdbScanOperation() (or NdbTransaction::getNdbIndexScanOperation()), it is carried out in the following three steps:

  1. Define the standard operation type, using NdbScanOperation::readTuples()
  2. Specify search conditions, using NdbScanFilter and/or NdbIndexScanOperation::setBound()
  3. Specify attribute actions, using NdbOperation::getValue()
  4. Executing the transaction, using NdbTransaction::execute()
  5. Traversing the result set by means of succssive calls to NdbScanOperation::nextResult()

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

This first example performs a table scan, using an NdbScanOperation:

     // 1. Retrieve table object
     myTable= myDict->getTable("MYTABLENAME");
    
     // 2. Create
     myOperation= myTransaction->getNdbScanOperation(myTable);
    
     // 3. Define type of operation 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. Attribute Actions
     myRecAttr= myOperation->getValue("ATTR2", NULL);

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

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

     // 2. Create
     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. Attribute Actions 
     myRecAttr = MyOperation->getValue("ATTR2", NULL);

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

Step 1: 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.

Step 2: 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 only be used on index scans (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.

Step 3: 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 previously discussed (see Synchronous Transactions), the value read is returned as an NdbRecAttr object by the NdbOperation::getValue() method.

Using Scan to Update/Delete

Scanning can also be used to update or delete rows. This is performed by

  1. Scanning using exclusive locks (using NdbOperation::LM_Exclusive)
  2. When iterating through the result set, for each row optionally calling either NdbScanOperation::updateCurrentTuple() or NdbScanOperation::deleteCurrentTuple()
  3. (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.

Note:
The actual update or delete will not be performed until the next call to NdbTransaction::execute(), just as with single row operations. NdbTransaction::execute() also must be called before any locks are released; see Lock handling with scans for more information.

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, that is, unless sorted is set to true. It is also important to note that, when using NdbIndexScanOperation::BoundEQ on a partition key, only fragments containing rows will actually be scanned.

Note:
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 will be scanned simultaneously and in parallel in such cases.

Lock handling with scans

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

In order to allow the application to handle how locks are released, NdbScanOperation::nextResult() has a Boolean parameter fetch_allow. If NdbScanOperation::nextResult() is called with fetch_allow 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 handle 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 batch
         MyScanOperation->deleteCurrentTuple();
       } while((check = MyScanOperation->nextResult(false)) == 0);

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

See ndbapi_scan.cpp for a more complete example of a scan.

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:

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 will be 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, this means that the error occurred when the operations were executed. In this example, errorOperation will be a pointer to the theOperation object. The NdbTransaction::getNdbError() method returns an NdbError object providing information about the error.

Note:
Transactions are not automatically closed when an error occurs. Call Ndb::closeTransaction() to close the transaction.
One recommended way to handle a transaction failure (i.e. an error is reported) is to:
  1. Rollback transaction (call NdbTransaction::execute() with a special parameter)
  2. Close transaction (call NdbTransaction::closeTransaction())
  3. 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 has to go through all operations and query their NdbError objects to find out what really happened.

It is also important to note that errors can occur even when a commit is reported as successful. In order to handle such situations, the NDB API provides an additional NdbTransaction::commitStatus() method to check the transactions's commit status.


Documentation generated Thu Mar 15 15:09:55 2007 from mysql source files.
© 2003-2004 MySQL AB