Introduction:

Oracle NoSQL Database is a multi-model, multi-region database, designed to provide a highly-available, scalable, performant, flexible, and reliable data management solution to meet the most demanding workloads. It can be deployed in on-premise data centers, or in private, public, hybrid, and multi-cloud. It is well-suited for high volume and velocity workloads, like the Internet of Things, customer 360, online contextual advertising, fraud detection, mobile application, user personalization, and online gaming.

In Part 1 (How to configure transactions in Oracle NoSQL Database – Part 1) of this blog series, we discussed how a transaction is treated as a logical, atomic unit of work that entails a single database operation as well as how to adjust consistency and durability.   In this blog I will cover how to perform operations on multiple rows in a single transaction.  If the transaction involves a number of write operations on rows that share the same shard key, all of these operations can be executed as a single atomic unit and will be either executed successfully, or none of them will. ACID properties will be enforced. 

Example of Online store using NoSQL database

Let’s consider a sample Java application for an online store selling different sports equipment. The online application has an Oracle NoSQL table itemInventory.  The examples use the Oracle NoSQL Database Direct Driver APIs. 

CREATE TABLE itemInventory (
     itemType STRING,
     itemCategory STRING,
     itemClass STRING,
     itemColor STRING,
     itemSize STRING,
     price DOUBLE,
     inventoryCount INTEGER,
     PRIMARY KEY (SHARD(itemType, itemCategory,         itemClass),itemColor,itemSize)
)

This table contains a description of various sports equipment that are sorted in different categories and an individual description of the item like color, size, price, etc. There is an inventory count which gives the count of items with same price, size color, class, category and type. Thousands of different sports equipment are sorted based on their type, category, and class and updated in the itemInventory table periodically.

CRUD Operations on itemInventory table:

In a typical scenario, a transaction can be inserting thousands of rows of data pertaining to information about different sports equipment. The following code snippet illustrates how Oracle NoSQL Database transactions can coordinate the multiple steps that are required to update the inventory in the store. Atomicity is ensured using a single all-or-nothing operation. If any part of the transaction fails, no actions in the transaction are executed and no changes are made to the table.

Additionally, shard keys are used to distribute data across the Oracle NoSQL Database cluster for scalability, and to position records that share the same shard key locally for easy reference and access. Records that share the same shard key are stored in the same physical location and can be accessed atomically and efficiently.

In the itemInventory table, the fields’ itemType, itemCategory and itemClass form the shard key. Multiple updates of rows that share the same shard key can be done in a single transaction. That is, all the individual write operations can be executed as a single atomic unit. So all of the operations will execute successfully, or none of them will.

As described in Transactions in Oracle NoSQL Database – Part 1, a user can configure consistency and durability to ensure the data changes survives any failure.

The following example uses Java direct driver API, but this can also be used with the language SDKs. There are a few limitations in using the SDKs, for example, you can configure durability using the durability class in Java direct driver API, where as the SDKs do not support this.

In this example, you use the TableOperationFactory class to create TableOperation class instances, each of which represents exactly one operation in the store. You obtain an instance of TableOperationFactory by using TableAPI.getTableOperationFactory().

//Obtain the necessary handles
TableAPI tableH = kvstore.getTableAPI();
Table myTable = tableH.getTable(“myTable”);
// You use TableOperationFactory to create items for
// your sequence.
TableOperationFactory tof = tableH.getTableOperationFactory();
// This ArrayList is used to contain each item
//in your sequence.
ArrayList< TableOperation > opList =                         new ArrayList< TableOperation >();

Every row is populated with the data for different columns and added to the array list in your sequence.

// Populate the row with different column values,
//but the same shard key to ensure they can
//participate in the same transaction
Row row = myTable.createRow();
row.put(“itemType”,”Hats”)
   .put(“itemCategory”,”baseball”)
   .put(“itemClass”,”longbill”)
   .put(“itemColor”,”red”)
   .put(“itemSize”,”small”)
   .put(“price”,13.07)
   .put(“inventoryCount”, 107);
//add the entry in the arraylist in our sequence
opList.add(tof.createPut(row, null, true));

// Populate another row
Row row = myTable.createRow();
row.put(“itemType”,”Hats”)
   .put(“itemCategory”,”baseball”)
   .put(“itemClass”,”longbill”)
   .put(“itemColor”,”red”)
   .put(“itemSize”,”medium”)
   .put(“price”,14.07)
   .put(“inventoryCount”, 198);
//add the entry in the arraylist in our sequence
opList.add(tof.createPut(row, null, true));
// Populate more rows and add them as needed

All the individual write operation happens as a single atomic unit using the execute function.

WriteOptions wrtPrm = new WriteOptions(“COMMIT_SYNC”,50,”seconds”);
//Execute all the thousand inserts as a single transaction
try {
  tof.execute(opList,wrtPrm);
} catch (OperationExecutionException oee) {
   // Some error occurred that prevented the sequence
   // from executing successfully. Use
   // oee.getFailedOperationIndex() to determine which
   // operation failed. Use oee.getFailedOperationResult()
   // to obtain an OperationResult object, which you can
   // use to troubleshoot the cause of the execution
   // exception.
} catch (IllegalArgumentException iae) {
   // An operation in the list was null or empty.
   // Or at least one operation operates on a row
   // with a shard key that is different than others.
   // Or more than one operation uses the same key.
} catch (DurabilityException de) {
   // The durability guarantee could not be met.
} catch (RequestTimeoutException rte) {
   // The operation was not completed inside of the
   // default request timeout limit.
} catch (FaultException fe) {
   // A generic error occurred
}

Note that if any of the above exceptions are thrown, then the entire sequence is aborted, and your data will be in the state it would have been in, if you had never executed the sequence at all.

Conclusion:

Over the course of these two blogs (How to configure transactions in Oracle NoSQL Database – Part 1 and How to configure transactions in Oracle NoSQL Database – Part 2), we covered a lot of information dealing with transactions. While many NoSQL databases have evolved over the last several years, a scant few have been able to deliver on the notion of truly flexible transaction models. Typically, a NoSQL database must trade-off the ability to offer the developer a consistent and isolated view of data for either performance, scale, ease of programming, or all the above. In contrast, the Oracle NoSQL database offers a hybrid ACID/BASE model with near zero impact on performance and scale while achieving the ease of programming that delights developers. Application architects and developers can decide when it is appropriate to relax transactional constraints or to tighten them up. With each access to the database, the appropriate level of consistency (for reads) and durability (for writes) can be chosen based on the specific needs of the application.

Want to learn more about configuring flexible transaction models?

Oracle NoSQL Database Enterprise Edition is now available to organizations that have purchased the license for it, or it is available free of cost as Oracle NoSQL Database Community Edition (CE). Download today.

For more information about configuring Consistency and durability properties in Oracle NoSQL database, see the documentation.