14.9.1.6 Preventing Lost Updates with ETAG

A duality view document's ETAG value prevents a stale update from overwriting newer changes.

Suppose you update the same action item through the JSON Relational Duality view, using the original JSON document you retrieved prior to Leo's update. When you try this, you see a valuable native behavior. For example, say you learn Chaz is busy, so you swap them with Usha using the block of PL/SQL below. As before, it uses a simple UPDATE statement to set the duality view's DATA column to the new JSON document.

declare
    l_json json := 
           -- Modified original JSON document
           -- to swap Chaz with Usha
           json(q'~
            {
              "_id" : 58,
              "name" : "Buy Talulah's Retirement Gift",
              "team" :
              [
                {
                  "role" : "MEMBER",
                  "name" : "Usha"
                },
                {
                  "team_assignment_id" : 128,
                  "role" : "LEAD",
                  "staff_id" : 18,
                  "name" : "Abigail"
                }
              ],
              "_metadata" :
              {
                "etag" : "48A8D3D393B6653A5D2B0359EDAD2D21",
                "asof" : "000025C52A6F11EE"
              }
            }
       ~');
begin
    update action_items_dv
       set data =  l_json
     where json_value(data,'$._id') = 58
    returning data into l_json;
    dbms_output.put_line(json_serialize(l_json pretty));
end;

Your attempted update fails with the following error:

ORA-42699: Cannot update JSON Relational Duality View 'ACTION_ITEMS_DV':
  The ETAG of document with ID 'FB03C13B00' in the database did not match
  the ETAG passed in.

The "_metadata" property in the original document contained the ETAG value shown below:

"_metadata" : {
   "etag" : "48A8D3D393B6653A5D2B0359EDAD2D21",
   "asof" : "000025C52A6F11EE"
}

The ETAG is a computed checksum the Duality View uses to prevent lost updates. When Leo changes the action item’s title using your APEX page, it alters the checksum for the row with _id = 58. Since your attempted update includes the old ETAG, it no longer matches the current value, so the update fails. To fix this, retrieve the latest version of the action item JSON, review the changes, and try the update again.

Tip:

When defining your duality view, by default all participating columns are included in the ETAG checksum computation. You can add @nocheck annotations to columns you don't want to participate.