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.
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!
Leave a Reply