You can use optimistic/lock-free concurrency control with duality views, writing JSON documents or committing their updates only when other sessions haven't modified them concurrently.

Optimistic concurrency control at the document level uses embedded ETAG values in field etag, which is in the object that is the value of field _metadata.

Document-centric applications sometimes use optimistic concurrency control to prevent lost updates, that is, to manage the problem of multiple database sessions interfering with each other by modifying data they use commonly.

Optimistic concurrency for documents is based on the idea that, when trying to persist (write) a modified document, the currently persisted document content is checked against the content to which the desired modification was applied (locally). That is, the current persistent state/version of the content is compared with the app's record of the persisted content as last read.

If the two differ, that means that the content last read is stale. The application then retrieves the last-persisted content, uses that as the new starting point for modification — and tries to write the newly modified document. Writing succeeds only when the content last read by the app is the same as the currently persisted content.

This approach generally provides for high levels of concurrency, with advantages for interactive applications (no human wait time), mobile disconnected apps (write attempts using stale documents are canceled), and document caching (write attempts using stale caches are canceled).

The lower the likelihood of concurrent database operations on the same data, the greater the efficacy of optimistic concurrency. If there is a great deal of contention for the same data then you might need to use a different concurrency-control technique.

In a nutshell, this is the general technique you use in application code to implement optimistic concurrency:

1. Read some data to be modified. From that read, record a local representation of the unmodified state of the data (its persistent, last-committed state).

2. Modify the local copy of the data.

3. Write (persist) the modified data only if the now-current persistent state is the same as the state that was recorded.

In other words: you ensure that the data is still unmodified, before persisting the modification. If the data was modified since the last read then you try again, repeating steps 1–3.

For a JSON document supported by a duality view, you do this by checking the document's etag field, which is in the object that is the value of top-level field _metadata.

The ETAG value in field etag records the document content that you want checked for optimistic concurrency control.

By default, it includes all of the document content per se, that is, the document payload. Field _metadata (whose value includes field etag) is not part of the payload; it is always excluded from the ETAG calculation.

In addition to field metadata, you can exclude selected payload fields from ETAG calculation — data whose modification you decide is unimportant to concurrency control. Changes to that data since it was last read by your app then won't prevent an updating operation. (In relational terms this is like not locking specific columns within a row that is otherwise locked.)

Document content that corresponds to columns governed by a NOCHECK annotation in a duality-view definition does not participate in the calculation of the ETAG value of documents supported by that view. All other content participates in the calculation. The ETAG value is based only on the underlying table columns that are (implicitly or explicitly) marked CHECK. See Annotation (NO)CHECK, To Include/Exclude Fields for ETAG Calculation.

Here's an example of a race document, showing field _metadata, with its etag field, followed by the document payload. See Creating Duality Views for more information about document metadata.

{"_metadata" : {"etag" : "E43B9872FC26C6BB74922C74F7EF73DC",
                         "asof" : "00000000000C20BA"},
 "_id" : 201, "name" : "Bahrain Grand Prix", ...}

Oracle ETAG concurrency control is thus value-based, or content-based. Conflicting updates are detected by examining, in effect, the content of the data itself.

• Read/get operations automatically update field etag, which records the current persistent state of the CHECKable document content as an HTTP ETAG hash value.

• Write/put operations automatically reject a document if its etag value doesn't match that of the current persistent (last-committed) data. That is, Oracle Database raises an error if the data has been modified since your last read, so your application need only check for a write error to decide whether to repeat steps 1–3.

Figure 5-1 illustrates the process.

Figure 5-1 Optimistic Concurrency Control Process

Description of "Figure 5-1 Optimistic Concurrency Control Process"

Basing concurrency control on the actual persisted data/content is more powerful and more reliable than using locks or surrogate information such as document version numbers and timestamps.

Because they are value-based, Oracle ETAGs automatically synchronize updates to data in different documents. And they automatically ensure consistency between document updates and direct updates to underlying tables — document APIs and SQL applications can update the same data concurrently.

Steps 2 (modify locally) and 3 (write) are actually combined. When you provide the modified document for an update operation you include the ETAG value returned by a read operation, as the value of modified document's etag field.

An attempted update operation fails if the current content of the document in the database is different from that etag field value, because it means that something has changed the document in the database since you last read it. If the operation fails, then you try again: read again to get the latest ETAG value, then try again to update using that ETAG value in field etag.

For example, suppose that two different database sessions, S1 and S2, update the same document, perhaps concurrently, for the race named Bahrain Grand Prix (_id=201), as follows:

• Session S1 performs the update of Example 5-8 or Example 5-9, filling in the race results (fields laps, date, podium and results).

• Session S2 performs the update of Example 5-10, which renames the race to Blue Air Bahrain Grand Prix.

Each session can use optimistic concurrency for its update operations, to ensure that what it modifies is the latest document content, by repeating the following two steps until the update operation (step 2) succeeds, and then COMMIT the change.

1. Read (select) the document. The value of field etag of the retrieved document encodes the current (CHECKable) content of the document in the database.

   Example 5-15 and Example 5-16 illustrate this.

2. Try to update the document, using the modified content but with field etag as retrieved in step 1.

   For session S1, the update operation is Example 5-8 or Example 5-9. For session S2, it is Example 5-10.

Failure of an update operation because the ETAG value doesn't match the current persistent (last-committed) state of the document raises an error.

Here is an example of such an error from SQL:

UPDATE race_dv
*
ERROR at line 1:
ORA-42699: Cannot update JSON Relational Duality View 'RACE_DV': The ETAG of
document with ID 'FB03C2030200' in the database did not match the ETAG passed
in.

Here is an example of such an error from REST. The ETAG value provided in the If-Match header was not the same as what is in the race document.

Response: 412 Precondition Failed

{"code"     : "PredconditionFailed",
 "message"  : "Predcondition Failed",
 "type"     : "tag:oracle.com,2020:error/PredconditionFailed",
 "instance" : "tag:oracle.com,2020:ecid/y2TAT5WW9pLZDNu1icwHKA"}

If multiple operations act concurrently on two documents that have content corresponding to the same underlying table data, and if that content participates in the ETAG calculation for its document, then at most one of the operations can succeed. Because of this an error is raised whenever an attempt to concurrently modify the same underlying data is detected. The error message tells you that a conflicting operation was detected, and if possible it tells you the document field for which the conflict was detected.

JSON-relational duality means you can also use ETAGs with table data, for lock-free row updates using SQL. To do that, use function SYS_ROW_ETAG, to obtain the current state of a given set of columns in a table row as an ETAG hash value.

Function SYS_ROW_ETAG calculates the ETAG value for a row using only the values of specified columns in the row: you pass it the names of all columns that you want to be sure no other session tries to update concurrently. This includes the columns that the current session intends to update, but also any other columns on whose value that updating operation logically depends for your application. (The order in which you pass the columns to SYS_ROW_ETAG as arguments is irrelevant.)

The example here supposes that two different database sessions, S3 and S4, update the same race table data, perhaps concurrently, for the race whose _id is 201, as follows:

• Session S3 tries to update column podium, to publish the podium values for the race.

• Session S4 tries to update column name, to rename the race to Blue Air Bahrain Grand Prix.

Each of the sessions could use optimistic concurrency control to ensure that it updates the given row without interference. For that, each would (1) obtain the current ETAG value for the row it wants to update, and then (2) attempt the update, passing that ETAG value. If the operation failed then it would repeat those steps — it would try again with a fresh ETAG value, until the update succeeded (at which point it would commit the update).

SELECT json_serialize(DATA PRETTY)
  FROM race_dv WHERE json_value(DATA, '$._id.numberOnly()') = 201;

JSON_SERIALIZE(DATAPRETTY)
--------------------------
{ 
  "_metadata" :
  { "etag" : "E43B9872FC26C6BB74922C74F7EF73DC",
    "asof" : "00000000000C20BA"
  },
  "_id" : 201,
  "name" : "Bahrain Grand Prix",
  "laps" : 57,
  "date" : "2022-03-20T00:00:00",
  "podium" :
  {
  },
  "result" :
  [
  ]
}
1 row selected.

curl --request GET \
  --url http://localhost:8080/ords/janus/race_dv/201

Response:

{"_id"    : 201,
 "name"      : "Bahrain Grand Prix",
 "laps"      : 57,
 "date"      : "2022-03-20T00:00:00",
 ...
 "_metadata" : {"etag": "20F7D9F0C69AC5F959DCA819F9116848",
                "asof": "0000000000000000"},
 "links"     : [ {"rel": "self",
                  "href": "http://localhost:8080/ords/janus/race_dv/201"},
                 {"rel": "describedby",
                  "href": "http://localhost:8080/ords/janus/metadata-catalog/race_dv/item"},
                 {"rel": "collection",
                  "href": "http://localhost:8080/ords/janus/race_dv/"} ]}

cache.metadata.enabled = true
cache.metadata.timeout = 1

-- S3 gets ETAG based on columns name, race_date, and podium.
SELECT SYS_ROW_ETAG(name, race_date, podium)
  FROM race WHERE race_id = 201;

SYS_ROW_ETAG(NAME,RACE_DATE,PODIUM)
-----------------------------------
201FC3BA2EA5E94AA7D44D958873039D

-- S4 successfully updates column name of the same row.
UPDATE race SET name = 'Blue Air Bahrain Grand Prix'
  WHERE race_id = 201;

1 row updated.

-- S3 unsuccessfully tries to update column podium.
--    It passes the ETAG value, to ensure it's OK to update.
UPDATE race SET podium = 
                '{"winner"         : {"name" : "Charles Leclerc",
                                      "time" : "01:37:33.584"},
                  "firstRunnerUp"  : {"name" : "Carlos Sainz Jr",
                                      "time" : "01:37:39.182"},
                  "secondRunnerUp" : {"name" : "Lewis Hamilton",
                                      "time" : "01:37:43.259"}}'
  WHERE race_id = 201
    AND SYS_ROW_ETAG(name, race_date, podium) =
          '201FC3BA2EA5E94AA7D44D958873039D';

0 rows updated.

-- S4 commits its update.
COMMIT;

Commit complete.

-- S3 gets a fresh ETAG value, and then tries again to update.
SELECT SYS_ROW_ETAG(name, race_date, podium)
  FROM race WHERE race_id = 201;

SYS_ROW_ETAG(NAME,RACE_DATE,PODIUM)
-----------------------------------
E847D5225C7F7024A25A0B53A275642A

UPDATE race SET podium = 
                '{"winner"         : {"name" : "Charles Leclerc",
                                      "time" : "01:37:33.584"},
                  "firstRunnerUp"  : {"name" : "Carlos Sainz Jr",
                                      "time" : "01:37:39.182"},
                  "secondRunnerUp" : {"name" : "Lewis Hamilton",
                                      "time" : "01:37:43.259"}}'
  WHERE race_id = 201
    AND SYS_ROW_ETAG(name, race_date, podium) =
          'E847D5225C7F7024A25A0B53A275642A';

1 row updated.

COMMIT;

Commit complete.

-- The data now reflects S4's name update and S3's podium update.
SELECT name, race_date, podium FROM race WHERE race_id = 201;

NAME   RACE_DATE   PODIUM
-------------------------
Blue Air Bahrain Grand Prix
20-MAR-22
{"winner":{"name":"Charles Leclerc","time":"01:37:33.584"},"firstRunnerUp":{"nam
e":"Carlos Sainz Jr","time":"01:37:39.182"},"secondRunnerUp":{"name":"Lewis Hami
lton","time":"01:37:43.259"}}

1 row selected.

_________________________________________________________

• Using Duality-View Transactions
  You can use a special kind of transaction that's specific to duality views to achieve optimistic concurrency control over multiple successive updating (DML) operations on JSON documents. You commit the series of updates only if other sessions have not modified the same documents concurrently.