JSON Duality Views in Oracle AI Database 26ai: Two Apps, One Account, No Lost Updates

 

Tested on: Oracle AI Database 26ai (23.26.3) on Oracle Autonomous AI Database, Transaction Processing, 2 ECPU | Level: Intermediate

In a bank, the same customer data is often changed by two kinds of applications: a core system that works with relational tables and SQL, and a mobile or API layer that prefers JSON documents. When both update the same account at nearly the same time, one change can silently overwrite the other. In this lab on Oracle AI Database 26ai, plain SQL lost a 1,000 deposit and ended with a balance of 4,800 instead of 5,800. The same scenario through a JSON Relational Duality View was rejected with ORA-42699, the application retried, and the balance ended correct at 5,800.

Results at a glance

Test	Result
Customer and accounts read as one JSON document	Works, with an etag in _metadata
One JSON insert	Created 1 customer row and 2 account rows
Stale write, plain SQL	Accepted, deposit lost (4,800)
Stale write, Duality View	Rejected with ORA-42699, retry gave 5,800
Update a field marked WITH NOUPDATE	Rejected with ORA-40940
Remove an account from the JSON array	Account row deleted from the table

What a Duality View is

A JSON Relational Duality View presents rows from normal relational tables as JSON documents. The data is stored once, in the tables. SQL applications keep using the tables, and document-style applications read and write JSON through the view. Oracle maps every JSON change back to inserts, updates and deletes on the tables.

Each document also carries an etag, a version value calculated from the document's content. When an application writes a document back, Oracle compares its etag with the current one. If the data changed in the meantime, the write is rejected. This is optimistic concurrency control, built into the database.

Lab setup

Two normal tables, as a core banking application would use them. All names and figures are fictional.

CREATE TABLE dv_customers (
  customer_id NUMBER       CONSTRAINT dv_customers_pk PRIMARY KEY,
  full_name   VARCHAR2(60) NOT NULL,
  segment     VARCHAR2(20) NOT NULL
);

CREATE TABLE dv_accounts (
  account_id  NUMBER       CONSTRAINT dv_accounts_pk PRIMARY KEY,
  customer_id NUMBER       NOT NULL
              CONSTRAINT dv_accounts_fk REFERENCES dv_customers,
  acct_type   VARCHAR2(20) NOT NULL,
  balance     NUMBER(12,2) NOT NULL
);

One Duality View shows a customer with all their accounts as one document. The segment field is marked read-only through JSON, and accounts can be inserted, updated and deleted through the document:

CREATE JSON RELATIONAL DUALITY VIEW customer_dv AS
  SELECT JSON {'_id'      : c.customer_id,
               'name'     : c.full_name,
               'segment'  : c.segment WITH NOUPDATE,
               'accounts' :
                 [ SELECT JSON {'accountId' : a.account_id,
                                'type'      : a.acct_type,
                                'balance'   : a.balance}
                   FROM   dv_accounts a WITH INSERT UPDATE DELETE
                   WHERE  a.customer_id = c.customer_id ]}
  FROM dv_customers c WITH INSERT UPDATE DELETE;

The same data, read as a document (account entries compacted to one line each):

SELECT JSON_SERIALIZE(d.data PRETTY)
FROM   customer_dv d
WHERE  d.data."_id" = 102;

{
  "_id" : 102,
  "_metadata" :
  {
    "etag" : "CC01B067BBE69FEB947D239A2C3F2DB6",
    "asof" : "00002E3401EBAD7D"
  },
  "name" : "Meena Raj",
  "segment" : "RETAIL",
  "accounts" :
  [
    { "accountId" : 1021, "type" : "SAVINGS", "balance" : 12000 },
    { "accountId" : 1022, "type" : "CURRENT", "balance" : 3000 }
  ]
}

Test 1: write JSON, read SQL

One JSON insert for a new customer with two accounts:

INSERT INTO customer_dv (data) VALUES ('
{"_id"      : 103,
 "name"     : "Priya Shankar",
 "segment"  : "RETAIL",
 "accounts" : [ {"accountId" : 1031, "type" : "SAVINGS", "balance" : 7500},
                {"accountId" : 1032, "type" : "CURRENT", "balance" : 1500} ]}');
COMMIT;

The core application sees ordinary rows in both tables:

CUSTOMER_ID FULL_NAME     SEGMENT
----------- ------------- -------
        103 Priya Shankar RETAIL

ACCOUNT_ID CUSTOMER_ID ACCT_TYPE BALANCE
---------- ----------- --------- -------
      1031         103 SAVINGS      7500
      1032         103 CURRENT      1500

Test 2: the lost update, with plain SQL

Account 1011 starts with 5,000. App A reads the balance to process a withdrawal of 200. Before it writes, App B deposits 1,000 and commits. App A then writes the balance it calculated from its old read. In the lab, the two applications run one after the other in a single PL/SQL block to reproduce the timing exactly:

SELECT balance INTO l_bal FROM dv_accounts WHERE account_id = 1011;   -- App A reads 5000
UPDATE dv_accounts SET balance = balance + 1000 WHERE account_id = 1011;  -- App B
COMMIT;
UPDATE dv_accounts SET balance = l_bal - 200 WHERE account_id = 1011;    -- App A
COMMIT;

App A reads balance              : 5000
App B deposits 1000 and commits  : done
App A withdraws 200 from its old value and commits
Final balance                    : 4800   (correct would be 5800: the deposit was LOST)

Oracle did exactly what it was told. Nothing failed, no error was raised, and the customer lost 1,000. This read-then-write pattern is common in application code that loads a record, changes it in memory and saves it back.

Test 3: the same race, through the Duality View

The balance is reset to 5,000 and the same steps are repeated, except App A reads and writes the whole customer document through customer_dv, while App B still uses plain SQL on the table:

-- App A reads the document, including _metadata.etag
SELECT JSON_SERIALIZE(d.data RETURNING CLOB) INTO l_doc
FROM   customer_dv d WHERE d.data."_id" = 101;

-- App B deposits through SQL
UPDATE dv_accounts SET balance = balance + 1000 WHERE account_id = 1011;
COMMIT;

-- App A writes back its stale document with the withdrawal applied
UPDATE customer_dv d SET d.data = JSON(l_new) WHERE d.data."_id" = 101;

App A reads document. balance = 5000, etag = BA6F43CA541437F90692100AEE272683
App B deposits 1000 via SQL. New etag = 1A31ABCDF96AFA48ACAC23C0EA9F929D
App A stale update: ORA-42699: Cannot update JSON Relational Duality View 'CUSTOMER_DV':
  The ETAG of document with ID 'FB03C2020200' in the database did not match the ETAG passed in.
App A re-reads document. balance = 6000
Final balance in the table        : 5800   (correct: 5000 + 1000 - 200 = 5800)

Three details matter here:

• App B never touched the Duality View. It changed the table with plain SQL, and the document's etag still changed. The protection covers every access path, not only JSON.
• The application had no version column or locking code. The check came from the view itself.
• The error is the correct outcome. ORA-42699 tells the application to re-read and retry. After the retry, the balance was right.

Test 4: the rules of the view

A field marked WITH NOUPDATE cannot be changed through JSON:

ORA-40940: Cannot update field 'segment' corresponding to column 'SEGMENT' of table
'DV_CUSTOMERS' in JSON Relational Duality View 'CUSTOMER_DV': Missing UPDATE annotation
or NOUPDATE annotation specified.

Removing an account from the document's accounts array deleted the row from dv_accounts, because the view allows DELETE on that table:

UPDATE customer_dv d
SET    d.data = JSON_TRANSFORM(d.data, REMOVE '$.accounts[1]')
WHERE  d.data."_id" = 103;

Accounts for customer 103 before : 2
Accounts for customer 103 after  : 1

Finally, the document and the tables agree:

CUSTOMER_ID FULL_NAME     SEGMENT ACCOUNT_ID ACCT_TYPE BALANCE
----------- ------------- ------- ---------- --------- -------
        101 Arun Kumar S  RETAIL        1011 SAVINGS      5800
        102 Meena Raj     RETAIL        1021 SAVINGS     12000
        102 Meena Raj     RETAIL        1022 CURRENT      3000
        103 Priya Shankar RETAIL        1031 SAVINGS      7500

Things to know before using it

• Plain SQL can avoid this too, if written carefully. A relative update (balance = balance - 200) or SELECT ... FOR UPDATE would also have prevented the lost update in Test 2. The value of the Duality View is that the check is automatic for every document write, without each developer remembering it.
• Applications must handle ORA-42699. The right reaction is to re-read the document, re-apply the change and write again. Without a retry, the user just sees an error.
• Annotations are your access control. WITH NOUPDATE, WITH INSERT UPDATE DELETE and similar annotations decide what a JSON client may change. Review them as carefully as grants.
• Deletes through arrays are real deletes. Removing an element from a nested array removed a row. If accounts must never be deleted this way, leave DELETE out of that table's annotation.
• This lab simulated the race in one session. The timing was reproduced step by step in a PL/SQL block, not with two live applications. The behaviour of the ETAG check is the same.

Clean-up

DROP VIEW  customer_dv;
DROP TABLE dv_accounts  PURGE;
DROP TABLE dv_customers PURGE;

Summary

A JSON Relational Duality View let a JSON application and a SQL application share the same customer and account tables in Oracle AI Database 26ai. With plain read-then-write SQL, a deposit was silently lost and the balance ended at 4,800. Through the Duality View, the stale write was rejected with ORA-42699, the application retried, and the balance ended correct at 5,800. For systems where several applications change the same records, that built-in version check is the most valuable part of the feature.

Lab design, testing and all results are my own, run on an Oracle LiveLabs sandbox. 

Tamilselvan Perumal is an Oracle ACE Apprentice and Senior DBA with 12+ years in banking databases, working on database performance and benchmarking. Connect on LinkedIn.