Optimistic Locking Made Easy: The Power of ETags in Action

JSON Relational Duality Views are one of the most remarkable features introduced with Oracle AI Database 26ai. They effortlessly blend the best of both worlds: relational and document, greatly simplifying the way developers work with data.

Duality Views aren’t just read-only, you can perform changes (updates, inserts, deletes, …) with them, too. Optimistic locking is important for modern application development, and you can implement that with Duality Views. Rather than bore you with a wordy article, let’s look at an example of optimistic locking in action.

Setup

The article is based on the assumption that you access a Duality View via REST calls. I used a compose file to create a development environment. If you’d like to try this for yourself, you’ll need an Oracle AI 26 Database instance and a modern ORDS (Oracle REST Data Services) instance, mine was 25.4.0. If you don’t want to set these up yourself, grab yourself an Always Free AI Autonomous Database where ORDS is configured out of the box.

Next, create the following objects in a schema of your choice (it’s named emily in my case). These tables represent a simple data model to keep track of tasks and their status including who’s assigned. For the sake of simplicity I opted to use auto-REST for the Duality View. This is fine for this article, but it’s not for production. Always use proper authentication and authorization, and follow industry best-known methods to securely expose API endpoints.

SQL

			
create table people (
    id   number primary key,
    name varchar2(100) not null
);
create table tasks (
    id     number primary key,
    name   varchar2(100) not null,
    status varchar2(15) not null,
    check ( status in ( 'open',
                        'closed' ) )
);
create table assignments (
    id          number primary key,
    person_id   number
        references people
    not null,
    task_id
        references tasks
    not null,
    description varchar2(100)
);
-- indexes to cover the foreign keys
create index i_assigments_people_fk on
    assignments (
        person_id
    );
create index i_assigments_tasks_fk on
    assignments (
        task_id
    );
-- the Duality View
create or replace json relational duality view projects_dv as 
  tasks @insert @update @delete
{
    _id : id,
    name : name,
    status : status
    team : assignments @insert @update @delete
    {
        team_assignment_id : id,
        people @unnest
        {
            person_id : id,
            name : name
        }
    }
};
-- sample data
insert into people values (1, 'Andy'),(2,'Brad'),(3,'Charlie');
insert into tasks (id, name, status) values (1, 'task 1', 'open' );
insert into assignments (id, person_id, task_id) values 
(1,1,1), (2,2,1), (3,3,1);
-- REST-enable the Duality View, lab/playground use only, secure API 
-- endpoints properly in production!
begin
  ords.enable_object (
    p_enabled      => TRUE,
    p_schema       => user,
    p_object       => 'PROJECTS_DV',
    p_object_type  => 'VIEW'
  );
    
  commit;
end;
/

		

Let’s look at the output of the Duality View, a select * from projects_dv returns the following:

JSON

			
{
    "_id": 1,
    "name": "task 1",
    "status": "open",
    "team": [
        {
            "team_assignment_id": 1,
            "person_id": 1,
            "name": "Andy"
        },
        {
            "team_assignment_id": 2,
            "person_id": 2,
            "name": "Brad"
        },
        {
            "team_assignment_id": 3,
            "person_id": 3,
            "name": "Charlie"
        }
    ],
    "_metadata": {
        "etag": "09128D18386F667A76E5B0676B304BCF",
        "asof": "00002A000404329A"
    }
}

		

Note the _metadata object in the output, it’s important.

Using Optimistic Locking for Updates

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.

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 demo employs 2 sessions

A terminal session using curl to simulate an application, and to remain language agnostic
An interactive session in SQLcl or the SQL Developer Extension for VSCode

Session #1

Let’s start with the curl command. As you can see from the above output, task 1 is still open. Now let’s assume that the work is done, and the task’s status can be set to closed. The first step is to read the document:

Shell

			
$ curl --silent https://localhost:8443/ords/emily/projects_dv/1 | jq 
{
  "_id": 1,
  "name": "task 1",
  "status": "open",
  "team": [
    {
      "team_assignment_id": 1,
      "person_id": 1,
      "name": "Andy"
    },
    {
      "team_assignment_id": 2,
      "person_id": 2,
      "name": "Brad"
    },
    {
      "team_assignment_id": 3,
      "person_id": 3,
      "name": "Charlie"
    }
  ],
  "_metadata": {
    "etag": "09128D18386F667A76E5B0676B304BCF",
    "asof": "0000000000229808"
  },
  "links": [
    {
      "rel": "self",
      "href": "https://localhost:8443/ords/emily/projects_dv/1"
    },
    {
      "rel": "describedby",
      "href": "https://localhost:8443/ords/emily/metadata-catalog/projects_dv/item"
    },
    {
      "rel": "collection",
      "href": "https://localhost:8443/ords/emily/projects_dv/"
    }
  ]
}

		

Let’s save the ETag for later, it’s "09128D18386F667A76E5B0676B304BCF" (the double-quotes are important).

Session #2

After the document has been retrieved by curl, let’s update the document in session 2. Assuming Brad has been asked to join another task, he’s taken off task 1. This can be done as follows (Make sure auto-commit is disabled, and if it’s disabled, don’t commit just yet)

SQL

			
update projects_dv dv
   set
    data =
      json('{
        "_id": 1,
        "name": "task 1",
        "status": "open",
        "team": [
            {
                "team_assignment_id": 1,
                "person_id": 1,
                "name": "Andy"
            },
            {
                "team_assignment_id": 3,
                "person_id": 3,
                "name": "Charlie"
            }
        ],
    }')
 where
    dv.data."_id" = 1;

		

The JSON returned by the Duality View now looks like this:

JSON

			
{
    "_id": 1,
    "name": "task 1",
    "status": "open",
    "team": [
        {
            "team_assignment_id": 1,
            "person_id": 1,
            "name": "Andy"
        },
        {
            "team_assignment_id": 3,
            "person_id": 3,
            "name": "Charlie"
        }
    ],
    "_metadata": {
        "etag": "357C12120E1690FD5C6CB001F6D1DB9D",
        "asof": "00000000002298BD"
    }
}

		

Session 1 is blissfully unaware. Remember that the second session didn’t commit yet.

Back to Session #1

With the ETag retrieved, session 1 can pursue its work and update the status of the document:

Shell

			
curl -iv \
-X PUT \
-H 'Content-Type: application/json' \
-H 'If-Match: "09128D18386F667A76E5B0676B304BCF"' \
-d '{
    "_id": 1,
    "name": "task 1",
    "status": "closed",
    "team": [
        {
            "team_assignment_id": 1,
            "person_id": 1,
            "name": "Andy"
        },
        {
            "team_assignment_id": 2,
            "person_id": 2,
            "name": "Brad"
        },
        {
            "team_assignment_id": 3,
            "person_id": 3,
            "name": "Charlie"
        }
    ]
}' https://localhost:8443/ords/emily/projects_dv/1

		

Note the if-match header in line 4: it passes the previously obtained ETag along with the PUT (= update) request. And this is where the database returns an error:

			
* upload completely sent off: 424 bytes
< HTTP/1.1 412 Precondition Failed
HTTP/1.1 412 Precondition Failed
< Date: Fri, 30 Jan 2026 17:20:34 GMT
Date: Fri, 30 Jan 2026 17:20:34 GMT
< Content-Type: application/problem+json
Content-Type: application/problem+json
< Content-Length: 214
Content-Length: 214
< Connection: keep-alive
Connection: keep-alive
< 
{
    "code": "PredconditionFailed",
    "message": "Predcondition Failed",
    "type": "tag:oracle.com,2020:error/PredconditionFailed",
    "instance": "tag:oracle.com,2020:ecid/f5bac5f92db1963b0e4a54da88197de3"
* Connection #0 to host ... left intact
}

		

The update didn’t go through. Curl reports a HTTP 412 Precondition Failed error, which is exactly what was needed. No waiting for the enqueue (“enq: row lock contention”), but a rather direct response.

Solution

The solution is to re-read the document after session 2 committed or rolled back, and try the update again. Here’s the flow:

Shell

			
$ curl https://localhost:8443/ords/emily/projects_dv/1
{
  "_id": 1,
  "name": "task 1",
  "status": "open",
  "team": [
    {
      "team_assignment_id": 1,
      "person_id": 1,
      "name": "Andy"
    },
    {
      "team_assignment_id": 3,
      "person_id": 3,
      "name": "Charlie"
    }
  ],
  "_metadata": {
    "etag": "357C12120E1690FD5C6CB001F6D1DB9D",
    "asof": "0000000000229F53"
  },
  "links": [
    {
      "rel": "self",
      "href": "https://localhost:8443/ords/emily/projects_dv/1"
    },
    {
      "rel": "describedby",
      "href": "https://localhost:8443/ords/emily/metadata-catalog/projects_dv/item"
    },
    {
      "rel": "collection",
      "href": "https://localhost:8443/ords/emily/projects_dv/"
    }
  ]
}
$ curl -iv \
-X PUT \
-H 'Content-Type: application/json' \
-H 'If-Match: "357C12120E1690FD5C6CB001F6D1DB9D"' \
-d '{
  "_id": 1,
  "name": "task 1",
  "status": "closed",
  "team": [
    {
      "team_assignment_id": 1,
      "person_id": 1,
      "name": "Andy"
    },
    {
      "team_assignment_id": 3,
      "person_id": 3,
      "name": "Charlie"
    }
  ]
}
' https://localhost:8443/ords/emily/projects_dv/1

		

This time the update succeeds. Curl reports a HTTP status code 200, which is the REST way of saying “everything is fine”. The JSON output is formatted for readability, otherwise it would all be on a single line and very much unreadable.

			
< HTTP/1.1 200 OK
HTTP/1.1 200 OK
< Content-Type: application/json
Content-Type: application/json
< Content-Location: https://localhost:8443/ords/emily/projects_dv/1
Content-Location: https://localhost:8443/ords/emily/projects_dv/1
< ETag: "30EFD79973EB81D011A2ABCC4941A468"
ETag: "30EFD79973EB81D011A2ABCC4941A468"
< Transfer-Encoding: chunked
Transfer-Encoding: chunked
< 
* Connection #0 to host localhost left intact
{
    "_id": 1,
    "name": "task 1",
    "status": "closed",
    "team": [
        {
            "team_assignment_id": 1,
            "person_id": 1,
            "name": "Andy"
        },
        {
            "team_assignment_id": 3,
            "person_id": 3,
            "name": "Charlie"
        }
    ],
    "_metadata": {
        "etag": "30EFD79973EB81D011A2ABCC4941A468",
        "asof": "000000000022A0D3"
    },
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/emily/projects_dv/1"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/emily/metadata-catalog/projects_dv/item"
        },
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/emily/projects_dv/"
        }
    ]
}

		

The document has been updated, this is confirmed by queries against the Duality View:

SQL

			
SQL> select * from projects_dv;
{
    "_id": 1,
    "name": "task 1",
    "status": "closed",
    "team": [
        {
            "team_assignment_id": 1,
            "person_id": 1,
            "name": "Andy"
        },
        {
            "team_assignment_id": 3,
            "person_id": 3,
            "name": "Charlie"
        }
    ],
    "_metadata": {
        "etag": "30EFD79973EB81D011A2ABCC4941A468",
        "asof": "000000000022A14D"
    }
}

		

Great, it worked!

Summary

ETags, combined with optimistic locking help you mitigate against accidental data loss in well-designed concurrent systems.

Efficient caching & validation
ETags also enable conditional requests (If-None-Match), reducing bandwidth and improving performance when data hasn’t changed.
Detect concurrent updates
An ETag represents the current version of a resource. If someone else modifies the document after you fetched it, the ETag changes—your update is rejected instead of silently overwriting their work. You can even use a SQL function, SYS_ROW_ETAG, to calculate the value. Ulrike Schwinn wrote a nice article about ETags and SQL.
No locks, better scalability
Optimistic locking assumes conflicts are rare. There’s no server-side lock held while users think or edit, which keeps systems fast and scalable.
Safer “last write wins” prevention
Without ETags, the last update overwrites everything. With If-Match, the server only accepts your update if you’re working with the latest version.
Better conflict handling
A failed update (typically 412 Precondition Failed) is an explicit signal to refetch, merge, or prompt the user—rather than discovering corruption later.

Happy coding!