Transactions in NoSQL database

In Oracle NoSQL Database, a transaction is treated as a logical, atomic unit of work that entails a single database operation.

Every data modification in the database takes place in a single transaction, managed by the system. Database developers do not have the ability to group multiple operations into a single transaction because there isn’t the notion of begin/end transactions. In a database, transactional semantics are often described in terms of ACID properties.

ACID properties

In Oracle NoSQL Database, transactions maintain all the following properties and developers can control some of them.

Atomicity: Transaction either completes or fails in its entirety. There is no in-between state or no partial transactions.
Consistency: Transaction leaves the database in a valid state.
Isolation: No two transactions mingle or interfere with each other. Developers get the same result when the two transactions are executed in sequence or executed in parallel.
Durability: Changes in a transaction are saved and the changes survive any type of failure (network, disk, CPU, or a power failure).

Developers can define a wide range of consistency levels depending on the application’s needs with the Oracle NoSQL Database Direct Driver. In addition, the Oracle NoSQL Database Drivers (commonly called the SDKs) support eventual and absolute consistency.

Developers can also configure durability such that updated rows in the database survive any failure with the Oracle NoSQL Database Direct Driver. Durability is not configurable in the SDKs.

Atomicity and Isolation are not configurable but Oracle NoSQL Database allows you to control consistency and durability policies in order to trade-off the performance for application needs. Some NoSQL databases only support eventual consistency but have no mechanism for absolute consistency.

Shard keys play an important role in achieving the ACID properties in the Oracle NoSQL database. For instance, when records share shard keys, you can delete multiple table rows in an atomic operation, or retrieve a subset of rows in your table in a single atomic operation. In addition to enabling scalability, well-designed shard keys can improve performance by requiring fewer cycles to put data on, or get data from, a single shard.

The NoSQL table hierarchy is an ideal data model for applications that need some data normalization, but also require predictable, low latency at scale. The hierarchy links different tables to enable left outer joins, combining rows from two or more tables based on related columns between them. Such joins execute efficiently since rows from the parent-child tables are co-located in the same shards. Also, writes to multiple tables in a table hierarchy obey transactional ACID properties since the records residing in each table of the hierarchy share the same shard key. All write operations perform as a single atomic unit. So all of the write operations will execute successfully, or none of them will.

Using Parent-Child tables in the Oracle NoSQL database

The Oracle NoSQL Database enables tables to exist in a parent-child relationship. This is known as table hierarchies.

Many NoSQL databases support data types like arrays and maps. When modeling a data relationship, application developers may find it easier to have each parent row store its child rows inside an array or a map in a nested structure. By doing so, not only is the data relationship denormalized but it has the potential for creating large parent rows, especially when the hierarchy is heavily nested, resulting in inefficient storage and poor performance. Oracle NoSQL Database’s table hierarchy is the ideal data model to avoid issues associated with arrays and maps. One of the biggest benefits of using child tables over embedded arrays is for those workloads that have a high velocity of write operations. When using embedded arrays, the write operations become updates, but when they are modeled as child tables, those operations become inserts. Inserts in a log-structured, append-only storage architecture are much more optimal than updates. Utilizing a table hierarchy should be considered when building data relationships in Oracle NoSQL Database.

The Benefits of a Table Hierarchy

Oracle NoSQL Database table hierarchy comes with the following benefits:

Highly efficient for storing data in a parent-child hierarchy- Parent and child rows are stored in separate NoSQL tables, reducing the size of parent rows compared with the single parent with child rows in nested arrays or maps. Write operations on parent or child tables create new versions of smaller rows and store these changes efficiently, given the append-only architecture of Oracle NoSQL Database.
Highly performant for read and write workloads - Parent and child rows reside in the same local shard, enabling write and read operations to achieve high performance since all records in the hierarchy can be read or written in a single network call.
Highly flexible for fine-grained authorization - Access rights to a parent or child table can be configured individually based on conditions at run-time, offering granular and flexible authorization.
Scalable ACID transactions - Uniquely balance the goals of scalability, low latency, and ACID by co-locating parent and child data on the same shard.
Table joins- Data can be queried using the nested table clause or left outer joins.

Characteristics of parent-child tables:

A child table inherits the primary key columns of its parent table.
All tables in the hierarchy have the same shard key columns, which are specified in the create table statement of the root table.
A parent table cannot be dropped before its children are dropped.
A referential integrity constraint is not enforced in a parent-child table.

A NoSQL table hierarchy not only captures the relationship between data entities but also takes advantage of the co-location of the parent-child rows to offer highly performant retrievals and superior scalability. The table hierarchy enables applications to implement ACID transactions. All data in the same parent-child rows are stored in the same shard and can be committed as a single database operation to ensure atomicity, consistency, isolation, durability.