andersswanson.dev

Lock-Free Writes at Scale: Under the Hood of Duality Views

In this article, we’ll explore Lock-Free Concurrency, a form of optimistic concurrency control. We’ll see how lock-free concurrency control synchronizes document writes with JSON Relational Duality Views, allowing multiple concurrent requests to safely work with the same data.

If you’re new to duality views, read my article 7 Reasons to try out JSON Relational Duality Views, with samples for a quick primer.

The problem of simultaneous writes

Imagine the following scenario: You have an application for booking airline seats where multiple users try to book identical seats simultaneously. If not handled carefully, you’ll encounter errors like:

  • One customer overwrites another customer’s seat assignment.
  • A seat that’s free appears as booked, or vice versa.
  • Unhappy customers and corrupt data.

Simultaneous data updates are fraught with potential timing errors and associated problems. Inefficient implementations often make use of locking, queuing, or other methods that negatively impact your user experience, or worse, corrupt your data.

How can we solve this with lock-free concurrency control?

Concurrency is implicitly managed by the database server:

  • If an application tries to write a stale document (e.g., an airline seat that’s already booked), the write is rejected by the database.
  • The application then refreshes data and tries again.
Flowchart illustrating the process of document insertion and updating in a database, including app interactions and version verification.

With lock-free concurrency control, we don’t need to manage state or locks. A client need only care about the current working document: Any document changes and version updates may be retrieved from the database as needed.

Practically, how does it work?

Each document in the database includes a managed _metadata field, containing calculated fields:

{
  "_id" : 1,
  "_metadata" :
  {
    "etag" : "C65D9AF0783FF9CB2C59014D69EF7425",
    "asof" : "000000000034CB51"
  },
  ... rest of the document
}

Let’s break these _metadata fields down:

  • ETAG is calculated from all of a document’s checked fields, and represents the current version of the document. It must be provided on updates to ensure optimistic concurrency control.
  • ASOF records the System Change Number (SCN) for the moment the document was modified.

Note: _metadata, etag, and asof are managed by the database, and INSERT/UPDATE operations on these fields are ignored.

Time to try it out!

I always like a good example, so let’s try this out. If you’d like to follow along, spin up an instance of Oracle Database Free.

First, let’s create a simple schema and duality view to work with. In our schema, we manage seat assignments for airlines:

CREATE TABLE airlines (
    id           NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    airline_name VARCHAR2(100) NOT NULL
);

CREATE TABLE seat_assignments (
    id               NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    airline_id       NUMBER NOT NULL,
    seat_number      VARCHAR2(10) NOT NULL,
    passenger_number VARCHAR2(50),
    CONSTRAINT fk_plane
        FOREIGN KEY (airline_id)
        REFERENCES airlines(id)
        ON DELETE CASCADE
);

Next, we create a JSON Relational Duality View to insert/update seats for airlines, layered over the relational schema:

CREATE OR REPLACE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW seats_dv
 AS airlines @insert @update {
  _id : id
  airline : airline_name
  seats : seat_assignments @insert @update @delete {
      _id : id
      seat : seat_number
      passenger : passenger_number
    }
};

Using the duality view, let’s insert a record into the database:

insert into seats_dv (data) values ('{
  "airline": "Oceanic",
  "seats": [
   {
     "seat": "23B",
     "passenger": "Jack"
   }
  ]
}');

Retrieving the record, we see the _metadata, ETAG, and ASOF fields populated by the database:

select json_serialize(s.data pretty) as seats
from seats_dv s;
{
  "_id" : 1,
  "_metadata" :
  {
    "etag" : "C65D9AF0783FF9CB2C59014D69EF7425",
    "asof" : "000000000034CB51"
  },
  "airline" : "Oceanic",
  "seats" :
  [
    {
      "_id" : 1,
      "seat" : "23B",
      "passenger" : "Jack"
    }
  ]
}

If I try an update with an invalid ETAG, the update should fail:

update seats_dv set data = '{
  "_id" : 1,
  "_metadata" :
  {
    "etag" : "E65D9AF0783FF9CB2C59014D69EF7425",
    "asof" : "000000000034CB51"
  },
  "airline" : "Oceanic",
  "seats" :
  [
    {
      "_id" : 1,
      "seat" : "23B",
      "passenger" : "Jack"
    }
  ]
}';

An error like “ORA-42699: Cannot update JSON Relational Duality View ‘SEATS_DV’: The ETAG of document with ID ‘FB03C10200’ in the database did not match the ETAG passed in.” is expected.

If we pass a valid ETAG, the document is successfully updated, ensuring only updates with the latest ETAG are allowed by the database. Note that your ETAG/ASOF values may be different:

update seats_dv set data = '{
  "_id" : 1,
  "_metadata" :
  {
    "etag" : "C65D9AF0783FF9CB2C59014D69EF7425",
    "asof" : "000000000034CB51"
  },
  "airline" : "Oceanic",
  "seats" :
  [
    {
      "_id" : 1,
      "seat" : "23B",
      "passenger" : "Jack"
    },
    {
      "seat" : "27H",
      "passenger" : "Kate"
    }
  ]
}';

If we attempt the same update again, it will fail as expected because of an invalid ETAG. Fetching the latest document from the database will retrieve the new, valid ETAG.

Do I always have to use lock-free concurrency control?

If you have a use case that doesn’t favor lock-free concurrency, know that it’s not mandatory. The @nocheck annotation allows us to specify fields or tables that are exempt from lock-free concurrency control:

CREATE OR REPLACE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW seats_nocheck
 AS airlines @insert @update {
  _id : id
  airline : airline_name
  seats : seat_assignments @insert @update @delete {
      _id : id
      -- seat and passenger are not included in ETAG checks
      seat : seat_number @nocheck
      passenger : passenger_number @nocheck
    }
};

If I want to entirely bypass lock-free concurrency control, I can omit the _metadata field. This is similar to applying @nocheck to the entire view:

update seats_dv set data = '{
  "_id" : 1,
  "airline" : "Oceanic",
  "seats" :
  [
    {
      "_id" : 1,
      "seat" : "23B",
      "passenger" : "Jack"
    },
    {
      "seat" : "27H",
      "passenger" : "Kate"
    }
  ]
}';

Summary

Lock-free concurrency control provides a powerful tool for optimistically managing concurrent data access. Users of the JSON Relational Duality Views aren’t required to explicitly lock, queue, or otherwise manage data, only keeping track of the database-managed _metadata object.

Questions? Drop me a comment or reach out on LinkedIn!

References

Responses

  1. Dharric Avatar
    Dharric

    Another reason to try & use JSON Duality Views in 23ai. Now, just have to convince the developers that an ORM may not be needed after all 🙂

    Reply
    1. Anders Swanson Avatar
      Anders Swanson

      Easier said than done, but it’s a great blog post idea 🙂

      Reply
  2. Reading List – August 2025 #3 – Gerald on IT

    […] Lock-Free Writes at Scale: Under the Hood of Duality Views – Anders Swanson explores lock-free concurrency with JSON Duality […]

    Reply

Leave a Reply

Discover more from andersswanson.dev

Subscribe now to keep reading and get access to the full archive.

Continue reading