Do you know that you also can use ETags with table data? What are Etags? The ETag or entity tag is part of HTTP. An ETag is an opaque identifier assigned by a Web server to a specific version of a resource found at a URL. If the resource representation at that URL ever changes, a new and different ETag is assigned. Used in this manner, ETags are similar to fingerprints and can quickly be compared to determine whether two representations of a resource are the same. (source Wikipedia).
The first time I heard about ETags in Oracle database was in the context of JSON relational duality. You can use optimistic/lock-free concurrency control within duality views, writing JSON documents or committing their updates only when other sessions haven’t modified them concurrently. It can be done at the document level using embedded ETag values in the field etag, which is in the object that is the value of field _metadata. Here’s an example of a JSON employee document, showing field _metadata, with its etag field, followed by the document payload.
{
"_metadata" :
{
"etag" : "E546E2220E8F9620E36C2A7F8858D6F7",
"asof" : "00000000008B03CD"
},
"departmentNumber" : 10,
"departmentName" : "ACCOUNTING",
"location" : "NEW YORK",
"employees" :
[
{
"employeeNumber" : 7782,
"employeeName" : "CLARK",
"job" : "MANAGER",
"salary" : 2450
}, ...
Oracle ETag concurrency control is thus value-based, or content-based. Conflicting UPDATEs are detected by examining, in effect, the content of the data itself, see the following diagram from the documentation.
Last year when I attended one of the json duality presentations from Beda and Tirthanka at DOAG 2023 conference, they mentioned that this functionality will also be available for table data. And here we are: With the new function SYS_ROW_ETAG, you can use ETags with table data, for lock-free row UPDATEs in SQL.
The function SYS_ROW_ETAG calculates the ETag value (128 bits hash value) for a row using only the values of those columns in the row. Pass the names of all columns that you want to be sure no other session tries to update concurrently. This includes the columns that the current session intends to update, but also any other columns on whose value that updating operation logically depends for your application.
So let’s demonstrate this with a simple example:
In a first session issue the following:
SQL> create table testvalue (t number, t1 number); Table created.
Let’s insert 3 rows with the new INSERT VALUES syntax.
SQL> insert into testvalue (t,t1) values (1,1),(2,2),(3,3); 3 rows created.
Let’s issue SYS_ROW_ETAG for all two columns and all rows.
SQL> select t, t1, SYS_ROW_ETAG(t,t1) from testvalue; T T1 SYS_ROW_ETAG(T,T1) ---------- ---------- -------------------------------- 1 1 FF225F5D85CF60203653349D199F789D 2 2 002D77C788BC8D639A8245DCF02FCE61 3 3 03ED2DFBD4A796F801C40B6CDA8437B7
Now let’s update one value of the first row:
SQL> update testvalue set t=10 where t=1; 1 row updated. SQL> commit; Commit complete.
In a second session, try to update the value of the column T refering to the initial calculated ETag value.
… Of course we will receive the following …
SQL> update testvalue set t=2 where SYS_ROW_ETAG(t,t1)='FF225F5D85CF60203653349D199F789D'; 0 rows updated.
Let’s find out the actual ETag values:
SQL> select t, t1, SYS_ROW_ETAG(t,t1) from testvalue; T T1 SYS_ROW_ETAG(T,T1) ---------- ---------- -------------------------------- 10 1 47588C12AB1A1EA83E8AA42A261E7FDA 2 2 002D77C788BC8D639A8245DCF02FCE61 3 3 03ED2DFBD4A796F801C40B6CDA8437B7
In the second example, we know now the new value and can issue the following UPDATE successfully.
SQL> update testvalue set t=2 where SYS_ROW_ETAG(t,t1)='47588C12AB1A1EA83E8AA42A261E7FDA'; 1 row updated. SQL> commit; Commit complete.
This demonstrates how you can use value-based concurrency control with Oracle Database Tables instead of locks or version information. More information can be found in the documentation: