Using the MR_COUNTER datatype

• Using CRDT datatype in a multi-region table
• Create table using MR_COUNTER datatype

Using CRDT datatype in a multi-region table

Overview of the MR_COUNTER data type

MR_Counter data type is a counter CRDT. CRDT stands for Conflict-free Replicated Data Type. In a multi-region setup of an Oracle NoSQL Database, a CRDT is a data type that can be replicated across servers where regions can be updated independently and it is always possible to converge on a correct common state. Changes in the regions are concurrent and not synchronized with one another. In short, CRDTs provide a way for concurrent modifications to be merged across regions without user intervention. Oracle NoSQL Databasecurrently supports the counter CRDT type which is called MR_Counter. The MR_COUNTER datatype is a subtype of the INTEGER or LONG or NUMBER data type.

Why do you need MR_Counter in a multi-region table?

In a multi-region database configuration, copies of the same data need to be stored in multiple regions. This configuration needs to deal with the fact that the data may be concurrently modified in different regions.

Take an example of a multi-region table in three different regions (where data is stored in three different Oracle NoSQL Database stores). Concurrent updates of the same data in multiple regions, without coordination between the machines hosting the regions, can result in inconsistencies between the regions, which in the general case may not be resolvable. Restoring consistency and data integrity when there are conflicts between updates may require some or all of the updates to be entirely or partially dropped. For example, in the current configuration of a multi-region table in the Oracle NoSQL Database, if the same column ( a counter) of a multi-region table is updated across two regions at the same time with different values, a conflict arises.

Currently, the conflict resolution is that the latest write overwrites the value across regions. For example, Region 1 updates column1 with a value R1, and region2 updates column1 with a value R2, and if the region2 update happens after region1, the value of the column (counter) in both the regions becomes R2. This is not what is actually desired. Rather every region should update the column ( a counter) at their end and also the system internally needs to determine the sum of the column across regions.

One way to handle this conflict is making serializable/linearizable transactions( one transaction is completed and changes are synchronized in all regions and only then the next transaction happens). A significant problem of having serializable transactions is performance. This is where MR_COUNTER datatype comes in handy. With MR_COUNTER datatype, we don't need serializable transactions and the conflict resolution is taken care of. That is, MR_COUNTER datatype ensures that though data modifications can happen simultaneously on different regions, the data can always be merged into a consistent state. This merge is performed automatically by MR_COUNTER datatype, without requiring any special conflict resolution code or user intervention.

Use-case for MR_COUNTER datatype

Consider a Telecom provider providing different services and packages to its customers. One such service is a "Family Plan" option where a customer and their family share the Data Usage plan. The customer is allocated a free data usage limit for a month which your the customer's entire family collectively uses. When the total usage of customer's family reaches 90 percent of the data limit, the telecom provider sends the customer an alert. Say there are four members in customer's family plan who are spread across different physical regions. The customer needs to get an alert from the telecom provider once the total consumption of their family reaches 90 percent of the free usage. The data is replicated in different regions to cater to latency, throughput, and better performance. That means there are four regions and each has a kvstore containing the details of the customer's data usage. The usage of their family members needs to be updated in different regions and at any point in time, the total usage should be monitored and an alert should be sent if the data usage reaches the limit.

An MR_COUNTER data type is ideal in such a situation to do conflict-free tracking of the data usage across different regions. In the above example, an increment counter in every data region's data store will track the data usage in that region. The consolidated data usage for all regions can be determined by the system at any point without any user intervention. That is the total data usage at any point in time can be easily determined by the system using an MR_COUNTER datatype.

Types of MR_COUNTER Datatype

Currently, Oracle NoSQL Database supports only one type of MR_COUNTER data type. which is Positive-Negative (PN) counter.

Positive-Negative (PN) Counter

A PN counter can be incremented or decremented. Therefore, these can serve as a general-purpose counter. For example, you can use these counters to count the number of users active on a social media website at any point. When the users go offline you need to decrement the counter.

To create a multi-region table with an MR_COUNTER column, See Create multi-region table with an MR_COUNTER column section in the Administrator's Guide.

Create table using MR_COUNTER datatype

A table column can be declared as having MR_COUNTER datatype in a CREATE TABLE statement. This can be done in a multi-region table only.

Declare a column with MR_COUNTER data type. MR_COUNTER is a subtype of the INTEGER or LONG or NUMBER data type.

Syntax:

mr_counter_defintion := AS MR_COUNTER

Semantics:

Declares the type of a column to be the MR_COUNTER type.

MR_COUNTER column Characteristics:

• MR_COUNTER data type can be used for a multi-region table only. It cannot be used in regular tables.
• One table can have multiple columns defined as "MR_COUNTER".
• You create an MR_COUNTER column as part of a CREATE TABLE DDL statement or add an MR_COUNTER column to an existing table with an ALTER TABLE DDL statement.

Note:

MR_COUNTER cannot be a primary key or be part of a secondary index.

Example using MR_COUNTER data type - Create a PN counter data type in a multi-region table

CREATE Table myTable( name STRING,
                      count INTEGER AS MR_COUNTER,
                      PRIMARY KEY(name)) IN REGIONS DEN,LON;

In the above example, you create a PN counter data type in two regions DEN and LON.

While inserting data into the table, the system inserts the default value of 0 in the following two cases.

• If you specify the "DEFAULT" keyword in the INSERT clause:

  INSERT INTO myTable VALUES ("Bob", DEFAULT);

• If you skip the column in the INSERT clause:

  INSERT INTO myTable(name) VALUES ("Bob");