Using MR_COUNTERs

Learn to use SQL statements to create and manage MR_COUNTERs in Multi-Region tables.

The MR_COUNTER data type is a Conflict-free Replicated Data Type (CRDT) counter. CRDTs provide a way for concurrent modifications to be merged across regions without user intervention.

In a Multi-Region setup of an Oracle NoSQL Database, copies of the same data must be stored in multiple regions and data may be concurrently modified in different regions. The MR_COUNTER data type ensures that though data modifications happen simultaneously on different regions, data always gets automatically merged into a consistent state.

Currently, Oracle NoSQL Database supports only Positive-Negative (PN) MR_COUNTER data type. The PN counters are suitable for increment and decrement operations. For example, you can use these counters to count the number of viewers live streaming a football match from a website at any point. When the viewers go offline, you need to decrement the counter.

You can only define MR_COUNTERs while creating a table or while modifying a table.

Create table using MR_COUNTER data type

You can declare a table column of the MR_COUNTER data type in a CREATE TABLE statement. MR_COUNTER is a subtype of one of the following data types: INTEGER, LONG, NUMBER.
CREATE TABLE Users (
  id integer,
  firstname string,
  lastname string,
  age integer,
  income integer,
  count integer AS MR_COUNTER,
  primary key (id)
) IN REGIONS FRA,LON;

You can use the MR_COUNTER data type for a Multi-Region table only. You can't use it in regular tables. In the statement above, you create a Multi-Region table in FRA and LON regions with count as an INTEGER MR_COUNTER data type. You can define multiple columns as MR_COUNTER data type in a Multi-Region table.

You can also declare a field in a JSON document as MR_COUNTER. 
CREATE TABLE IF NOT EXISTS JSONPersons (
  id integer,
  person JSON (counter as INTEGER MR_COUNTER,
               books.count as LONG MR_COUNTER),
  primary key (id)
) IN REGIONS FRA,LON;

In the statement above, you are identifying two of the fields in the JSON document person as MR_COUNTERs. The first field counter is an INTEGER MR_COUNTER data type. The second field count is within a nested JSON document books. The count field is of LONG MR_COUNTER data type.

Insert rows into a Multi-Region table

You can use the INSERT statement to insert data into a Multi-Region table with the MR_COUNTER column. You can add rows using one of the following options. Both the options insert a default value of zero to the MR_COUNTER column.

  1. Option 1: Supply the keyword DEFAULT to the MR_COUNTER column.
    INSERT INTO Users VALUES (10, "David", "Morrison", 25, 100000,
    DEFAULT);
    In the statement above, you supply a value DEFAULT to the count MR_COUNTER.
    SELECT * FROM Users;
    Output:
    {"id":10,"firstname":"David","lastname":"Morrison","age":25,"income":100000,"count":0}
  2. Option 2: Skip the MR_COUNTER column value by including only the required column values in the INSERT statement.
    INSERT INTO Users(id, firstname, lastname) VALUES (20, "John", "Anderson");
    In the statement above, you supply values to specific columns. The SQL engine inserts the values to the corresponding columns, a default value zero to the MR_COUNTER, and a null value to all the other columns.
    SELECT * FROM Users WHERE id = 20;
    Output:
    {"id":20,"firstname":"John","lastname":"Anderson","age":null,"income":null,"count":0}

If an MR_COUNTER is a part of the JSON document, you must supply a zero value explicitly to the MR_COUNTER.

Note:

  • You can't supply the keyword DEFAULT while inserting a JSON MR_COUNTER.
  • The system will return an error if you try to insert data into an MR table without supplying a value to the declared JSON MR_COUNTER field or using the keyword DEFAULT.

In the sample below, you insert a row into JSONPersons table. As it includes JSON MR_COUNTERs counter and count in the people document, you supply a zero value explicitly to these MR_COUNTERs. 

INSERT INTO JSONPersons VALUES (
   1,
  {
      "firstname":"David",
      "lastname":"Morrison",
      "age":25,
      "income":100000,
      "counter": 0,
      "books" : {
        "Title1" : "Gone with the wind",
        "Title2" : "Oliver Twist",
        "count" : 0
      }      
    }
);
The SELECT statement displays the following result:
{"id":1,"person":{"age":25,"books":{"Title1":"Gone with the wind","Title2":"Oliver Twist","count":0},"counter":0,"firstname":"David","income":100000,"lastname":"Morrison"}};

Update MR_COUNTER

You can use the SET clause of the UPDATE statement to update MR_COUNTER in a Multi-Region table. You must only use the standard arithmetic computations to increment or decrement the value of MR_COUNTER. You can't use the UPDATE clauses to explicitly supply a value to MR_COUNTER or remove one from the table.
UPDATE Users SET count = count + 10 WHERE id = 10 RETURNING *;
In the statement above, you increment the count value in the Users table by 10. The RETURNING clause fetches the following output:
{"id":10,"firstname":"David","lastname":"Morrison","age":25,"income":100000,"count":10}
Similarly, you can update MR_COUNTER in a JSON document by incrementing or decrementing its value. You can access MR_COUNTER using its path expression as follows:
UPDATE JSONPersons p SET p.person.books.count = p.person.books.count + 1 WHERE id = 1 RETURNING *;
In the statement above, you increment the MR_COUNTER count in the nested books document by one.
{"id":1,"person":{"age":25,"books":{"Title1":"Gone with the wind","Title2":"Oliver Twist","count":1},"counter":0,"firstname":"David","income":100000,"lastname":"Morrison"}}

How system uses MR_COUNTER to handle concurrent modifications

When you create a Multi-Region table in different regions, it has the same definition. This implies, if you define any MR_COUNTER data type, it exists in both the remote and local regions. Every region can update the MR_COUNTER concurrently at its end. As all the Multi-Region tables in the participating regions are synchronized, the system automatically performs a merge on these concurrent modifications to reflect the latest updates of the MR_COUNTER without any user intervention.

Modify table to add or remove MR_COUNTER

You can use an ALTER TABLE statement to add or remove MR_COUNTER.

Adding MR_COUNTER

To add MR_COUNTER, use the ADD clause in the ALTER TABLE statement.
ALTER TABLE Users (ADD countTwo INTEGER AS MR_COUNTER);

The statement above adds countTwo field as MR_COUNTER with a default value zero to the Users table.

The SELECT statement displays the following result:
{"id":10,"firstname":"David","lastname":"Morrison","age":25,"income":100000,"count":10,"countTwo":0}
{"id":20,"firstname":"John","lastname":"Anderson","age":null,"income":null,"count":0,"countTwo":0}
You can add MR_COUNTER to a JSON column as follows:
ALTER TABLE JSONPersons (ADD JsonTwo JSON(counterTwo AS NUMBER MR_COUNTER));
The statement above adds a JsonTwo nested JSON document to the JSONPersons table and includes counterTwo field as MR_COUNTER with zero value:
{
  "id" : 1,
  "person" : {
    "age" : 25,
    "books" : {
      "Title1" : "Gone with the wind",
      "Title2" : "Oliver Twist",
      "count" : 1
    },
    "counter" : 0,
    "firstname" : "David",
    "income" : 100000,
    "lastname" : "Morrison"
  },
  "JsonTwo" : {
    "counterTwo" : 0
  }
}

Removing MR_COUNTER

To remove MR_COUNTER, use the DROP clause in the ALTER TABLE statement.
ALTER TABLE Users (DROP countTwo);

The statement above removes countTwo MR_COUNTER from the Users table.

The SELECT statement displays the following result:
{"id":10,"firstname":"David","lastname":"Morrison","age":25,"income":100000,"count":10}
{"id":20,"firstname":"John","lastname":"Anderson","age":null,"income":null,"count":0}
You can remove a JSON document and its MR_COUNTER as follows:
ALTER TABLE JSONPersons (DROP JsonTwo);
The statement above removes the JSONTwo nested JSON document from the JSONPersons table.
{
  "id" : 1,
  "person" : {
    "age" : 25,
    "books" : {
      "Title1" : "Gone with the wind",
      "Title2" : "Oliver Twist",
      "count" : 1
    },
    "counter" : 0,
    "firstname" : "David",
    "income" : 100000,
    "lastname" : "Morrison"
  }
}