Optimistic Locking 9: proof of concept

To wind up this series about optimistic locking using the SCN, here is a sample API for querying and updating the DEPT table, using JSON as the format for exchanging data with the client application.

I want to make optimistic locking as unobtrusive as possible, even if it means more code for me:

  • the DEPT table does not have to be created with ROWDEPENDENCIES. The ORA_ROWSCN pseudocolumn reflects the latest change to the data block, not to one specific row.
  • the client application receives just one SCN when it queries data. Its only responsibility is to return the same SCN when it wants to update the data.
  • Being “optimistic”, I assume that most of the time the data block will not have changed, so the update will succeed directly.
  • If and only if the data block has changed, I check to see if the actual column values have changed.

The API

(Sorry about the lack of line numbers, there is a problem with the [ code ] tag at one point in the package body.)

create or replace package dept_json as
  function get_depts return varchar2;
  procedure update_depts(p_json in varchar2);
end dept_json;

The GET_DEPTS function returns one VARCHAR2 containing all the departments in JSON format, together with the SCN:

{
  "data_version_num" : 56781167,
  "depts" :
  [
    {
      "deptno" : 10,
      "dname" : "ACCOUNTING",
      "loc" : "NEW YORK"
    },
    {
      "deptno" : 20,
      "dname" : "RESEARCH",
      "loc" : "DALLAS"
    },
    {
      "deptno" : 30,
      "dname" : "SALES",
      "loc" : "CHICAGO"
    },
    {
      "deptno" : 40,
      "dname" : "OPERATIONS",
      "loc" : "BOSTON"
    }
  ]
}

In this simplistic proof of concept, the UPDATE_DEPTS API sends back JSON in exactly the same format with the same “data_version_num” and the changed data:

{
  "data_version_num" : 56781167,
  "depts" :
  [
    {
      "deptno" : 10,
      "dname" : "ACCOUNTING",
      "loc" : "NEW LOC"
    }
  ]
}

Getting the data

create or replace package body dept_json as
  ...
  function get_depts return varchar2 is
    l_json varchar2(4000 byte);
    l_scn number;
  begin
    l_scn := dbms_flashback.get_system_change_number;
    select json_object(
      'data_version_num' value l_scn,
      'depts' value json_arrayagg(
        json_object(
          'deptno' value deptno,
          'dname' value dname,
          'loc' value loc  
        ) order by deptno
      )
    )
    into l_json
    from dept;
    return l_json;
  end get_depts;
  ...
end dept_json;

Using the 12c SQL/JSON functions, I can query the data and convert it to JSON format immediately. I get an SCN just before querying the data. In a busy database, the actual “read SCN” may be slightly higher. See Optimistic Locking 5: “Read-consistent” SCN for a discussion.

Updating the data

create or replace package body dept_json as

  type tt_dept is table of dept%rowtype;  
  cursor c_request is select 0 read_scn, d.* from dept d;
  type tt_request is table of c_request%rowtype;
  ...
  procedure update_depts(p_json in varchar2) is
    lt_request tt_request;
    lt_try_again tt_request := new tt_request();
    c_char constant varchar2(1) := '*';
    lt_dept tt_dept := new tt_dept();
  begin
    select * bulk collect into lt_request
    from json_table(p_json, '$' columns
      read_scn number path '$.data_version_num',
      nested path '$.depts[*]' columns (
        deptno number path '$.deptno',
        dname varchar2(40) path '$.dname',
        loc varchar2(40) path '$.loc'
      )
    )
    order by deptno;

    forall i in 1..lt_request.count
      update dept set dname = lt_request(i).dname, loc = lt_request(i).loc
      where deptno = lt_request(i).deptno
      and coalesce(c_char, dname||loc) is not null
      and ora_rowscn <= lt_request(i).read_scn;
  ...
  end update_depts;
end dept_json;

This is the first part of the update procedure. I convert the JSON input to SQL format, then I do a conditional update: the update will succeed only if ORA_ROWSCN remains less that the SCN supplied by the GET_DEPTS function. The line in italics is necessary to ensure “restartability” of the update: see Optimistic Locking 7: Restartability for details.

Double-checking

After the first UPDATE, I gather the rows that were not updated into a new collection.

  procedure update_depts(p_json in varchar2) is
    lt_request tt_request;
    lt_try_again tt_request := new tt_request();
    c_char constant varchar2(1) := '*';
    lt_dept tt_dept := new tt_dept();
  begin
    -- I have removed the code for the first UPDATE
    for i in 1..lt_request.count loop
      if sql%bulk_rowcount(i) = 0 then
        lt_try_again.extend;
        lt_try_again(lt_try_again.last) := lt_request(i);
        dbms_output.put_line('deptno '||lt_request(i).deptno||': SCN too recent');
      else
        dbms_output.put_line('deptno '||lt_request(i).deptno||': first update succeeded');
      end if;
    end loop;

Now I use a flashback query to get the actual column values I sent to the client application in the first place.

    if lt_try_again.count > 0 then
      lt_dept := select_depts_flashback(lt_try_again);
  function select_depts_flashback(
    pt_request in tt_request
  ) return tt_dept is
    pragma autonomous_transaction;
    lt_dept tt_dept;
    lt_deptno sys.odcinumberlist := new sys.odcinumberlist();
  begin
    lt_deptno.extend(pt_request.count);
    for i in 1..pt_request.count loop
      lt_deptno(i) := pt_request(i).deptno;
    end loop;
    dbms_flashback.enable_at_system_change_number(pt_request(1).read_scn);
    select * bulk collect into lt_dept
    from dept where deptno in (select * from table(lt_deptno))
    order by deptno;
    dbms_flashback.disable;
    return lt_dept;
  end select_depts_flashback;

Finally, I do a second update that will only succeed if the actual column values have not changed. If there has been a change, I raise an exception. That will cause all of the updates to be rolled back automatically, thanks to Statement-Level Atomicity.

      forall i in 1..lt_try_again.count
        update dept set dname = lt_try_again(i).dname, loc = lt_try_again(i).loc
        where deptno = lt_try_again(i).deptno
        and decode(lt_dept(i).dname, dname, 0, 1)
          + decode(lt_dept(i).loc,   loc,   0, 1)
          = 0;

      for i in 1..lt_try_again.count loop
        if sql%bulk_rowcount(i) = 0 then raise_application_error(
          -20001,
          'DEPT row with DEPTNO = ' 
          || lt_dept(i).deptno 
          ||' already changed by another user. No updates have been made.'
          );
        else
          dbms_output.put_line('deptno '||lt_request(i).deptno||': second update succeeded');
        end if;
      end loop;
    end if;

  end update_depts;
end dept_json;

 

A few tests

I’m going to create a DEPT table with the data in three different blocks.

drop table dept cascade constraints purge;
CREATE TABLE DEPT(
  DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14),
	LOC VARCHAR2(13) 
);
INSERT INTO DEPT VALUES	(10,'ACCOUNTING','NEW YORK');
commit;
INSERT /*+ append */ INTO DEPT 
select 20,'RESEARCH','DALLAS' from dual
union all
select 30,'SALES','CHICAGO' from dual;
commit;
INSERT /*+ append_values */ INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
commit;

select d.*,
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) block_num,
ora_rowscn
from dept d;

    DEPTNO DNAME          LOC            BLOCK_NUM ORA_ROWSCN
---------- -------------- ------------- ---------- ----------
        10 ACCOUNTING     NEW YORK           80742   56026114
        20 RESEARCH       DALLAS             85848   56026119
        30 SALES          CHICAGO            85848   56026119
        40 OPERATIONS     BOSTON             85849   56026122

>> Test 1: update with no intervening update
SQL> begin
  2  dept_json.update_depts(
  3  '{"data_version_num":56781167,"depts":[{"deptno":10,"dname":"ACCOUNTING","loc":"Test 1"}]}'
  4  );
  5  end;
  6  /
deptno 10: first update succeeded

PL/SQL procedure successfully completed.

>> Test 2: update with intervening select for update
SQL> select * from dept where deptno = 20 for update;

    DEPTNO DNAME          LOC          
---------- -------------- -------------
        20 RESEARCH       DALLAS       

SQL> commit;

Commit complete.

SQL> begin
  2  dept_json.update_depts(
  3  '{"data_version_num":56781167,"depts":[
  4  {"deptno":20,"dname":"RESEARCH","loc":"Test 2"},
  5  {"deptno":30,"dname":"SALES","loc":"CHICAGO"}
  6  ]}'
  7  );
  8  end;
  9  /
deptno 20: SCN too recent
deptno 30: SCN too recent
deptno 20: second update succeeded
deptno 30: second update succeeded

PL/SQL procedure successfully completed.

>> Test 3: update refused, intervening update
SQL> update dept set loc = 'Test 3a' where deptno = 30;

1 row updated.

SQL> commit;

Commit complete.

SQL> begin
  2  dept_json.update_depts(
  3  '{"data_version_num":56781167,"depts":[
  4  {"deptno":20,"dname":"RESEARCH","loc":"Test 2"},
  5  {"deptno":30,"dname":"SALES","loc":"Test 3b"}
  6  ]}
  7  ');
  8  end;
  9  /
deptno 20: SCN too recent
deptno 30: SCN too recent
...
Error report -
ORA-20001: DEPT row with DEPTNO = 20 already changed by another user. No updates have been made.

>> Test 4: update refused, intervening update with restart
/* In another session
update dept set loc = 'Test 4a' where deptno = 40;
*/
SQL> begin
  2  dept_json.update_depts(
  3  '{"data_version_num":56781167,"depts":[
  4  {"deptno":40,"dname":"OPERATIONS","loc":"Test 4b"}
  5  ]}'
  6  );
  7  end;
  8  /
  
/* In another session
commit;
*/
deptno 40: SCN too recent
...
Error report -
ORA-20001: DEPT row with DEPTNO = 40 already changed by another user. No updates have been made.

Conclusion

To repeat a bit of what I said in Optimistic Locking 4: the Good :

If you base optimistic locking on row version numbers, row timestamps, or checksums of row columns, then the client code has to keep track of that data and send it back with the updates. With the SCN, the data from each row is just user data, not technical data. It can be pivoted, unpivoted, aggregated or denormalized. With JSON, it is trivial to separate the SCN from all the user data.

All the client code has to do is keep the SCN somewhere and include it in the call to the update API.

P.S. the package body

create or replace package body dept_json as

  type tt_dept is table of dept%rowtype;  
  cursor c_request is select 0 read_scn, d.* from dept d;
  type tt_request is table of c_request%rowtype;

  function get_depts return varchar2 is
    l_json varchar2(4000 byte);
    l_scn number;
  begin
    l_scn := dbms_flashback.get_system_change_number;
    select json_object(
      'data_version_num' value l_scn,
      'depts' value json_arrayagg(
        json_object(
          'deptno' value deptno,
          'dname' value dname,
          'loc' value loc  
        ) order by deptno
      )
    )
    into l_json
    from dept;
    return l_json;
  end get_depts;

  function select_depts_flashback(
    pt_request in tt_request
  ) return tt_dept is
    pragma autonomous_transaction;
    lt_dept tt_dept;
    lt_deptno sys.odcinumberlist := new sys.odcinumberlist();
  begin
    lt_deptno.extend(pt_request.count);
    for i in 1..pt_request.count loop
      lt_deptno(i) := pt_request(i).deptno;
    end loop;
    dbms_flashback.enable_at_system_change_number(pt_request(1).read_scn);
    select * bulk collect into lt_dept
    from dept where deptno in (select * from table(lt_deptno))
    order by deptno;
    dbms_flashback.disable;
    return lt_dept;
  end select_depts_flashback;

  procedure update_depts(p_json in varchar2) is
    lt_request tt_request;
    lt_try_again tt_request := new tt_request();
    c_char constant varchar2(1) := '*';
    lt_dept tt_dept := new tt_dept();
  begin
    select * bulk collect into lt_request
    from json_table(p_json, '$' columns
      read_scn number path '$.data_version_num',
      nested path '$.depts[*]' columns (
        deptno number path '$.deptno',
        dname varchar2(40) path '$.dname',
        loc varchar2(40) path '$.loc'
      )
    )
    order by deptno;
    
    if lt_request.count = 0 then
      dbms_output.put_line('No depts found, so nothing to update.');
      return;
    end if;

    forall i in 1..lt_request.count
      update dept set dname = lt_request(i).dname, loc = lt_request(i).loc
      where deptno = lt_request(i).deptno
      and coalesce(c_char, dname||loc) is not null
      and ora_rowscn  0 then
      lt_dept := select_depts_flashback(lt_try_again);

      forall i in 1..lt_try_again.count
        update dept set dname = lt_try_again(i).dname, loc = lt_try_again(i).loc
        where deptno = lt_try_again(i).deptno
        and decode(lt_dept(i).dname, dname, 0, 1)
          + decode(lt_dept(i).loc,   loc,   0, 1)
          = 0;

      for i in 1..lt_try_again.count loop
        if sql%bulk_rowcount(i) = 0 then raise_application_error(
          -20001,
          'DEPT row with DEPTNO = ' 
          || lt_dept(i).deptno 
          ||' already changed by another user. No updates have been made.'
          );
        else
          dbms_output.put_line('deptno '||lt_request(i).deptno||': second update succeeded');
        end if;
      end loop;
    end if;

  end update_depts;
end dept_json;
Advertisements

Optimistic Locking 8: double-checking with flashback

Some optimistic locking methods, including the SCN method, can detect intervening updates even when column values have not really changed. With the SCN method, we can use flashback queries to confirm that real changes occurred.

Flashback queries are evil

As soon as we mention flashback queries, some wise DBAs will immediately object that such queries harm the “shared pool”. This is a serious objection; let me explain why.

Before executing a SQL statement, Oracle must parse it and establish an execution plan: this is called the “hard parse”. Hard parsing can be an expensive operation, and it can be a bottleneck since it uses resources that must be serialized. To avoid constant parsing, Oracle stores executable forms of SQL cursors and PL/SQL programs in the “library cache”, which is part of the shared pool. This enables “parse once, execute many”, a prerequisite for good performance in OLTP databases.

Imagine a SQL statement that must be hard parsed before every execution. That one statement will use up resources and slow down parsing of other new statements. Not good. Now imagine that the “cursor” resulting from that parse goes into the library cache: the cache will get bigger and bigger and slower to navigate, until finally other cursors start getting aged out. We now have a dysfunctional database that spends its time hard parsing instead of executing statements.

Unfortunately, one of those statements that gets hard parsed every time is the AS OF flashback query :-(

To demonstrate this, I’ll run a simple AS OF query in a PL/SQL block, using the same SCN every time. Note that I flush the shared pool beforehand, so there will always be a first hard parse.

declare 
  l_scn number;
  l_cnt number;
begin
  dbms_flashback.disable;
  execute immediate 'alter system flush shared_pool';
  l_scn := dbms_flashback.get_system_change_number;
  for i in 1..10 loop
    select count(*) into l_cnt from T as of scn l_scn;
  end loop;
end;
/

Now I’ll query V$SQL_SHARED_CURSOR to see what cursors could not be “shared” and had to be hard parsed over and over again.

select child_number, parse_calls, executions, reason "Reason: AS OF SCN"
from v$sql
left join V$SQL_SHARED_CURSOR using(sql_id, child_number)
where sql_text like 'SELECT COUNT(*) FROM T%';

CHILD_NUMBER PARSE_CALLS EXECUTIONS Reason: AS OF SCN                                                               
------------ ----------- ---------- --------------------------------------------------------------------------------
           0           1          1 <ChildNode><ChildNumber>0</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           1           1          1 <ChildNode><ChildNumber>1</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           2           1          1 <ChildNode><ChildNumber>2</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           3           1          1 <ChildNode><ChildNumber>3</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           4           1          1 <ChildNode><ChildNumber>4</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           5           1          1 <ChildNode><ChildNumber>5</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           6           1          1 <ChildNode><ChildNumber>6</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           7           1          1 <ChildNode><ChildNumber>7</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           8           1          1 <ChildNode><ChildNumber>8</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</r
           9           1          1 

Formatted content of first Reason:
<ChildNode>
  <ChildNumber>0</ChildNumber>
  <ID>21</ID>
  <reason>Flashback cursor(1)</reason>
  <size>3x4</size>
  <Flashback_cursor>0</Flashback_cursor>
  <As_of_cursor>1</As_of_cursor>
  <Flashback_session>0</Flashback_session>
</ChildNode>

Compare this to a normal query:

declare 
  l_cnt number;
begin
  execute immediate 'alter system flush shared_pool';
  for i in 1..10 loop
    select count(*) into l_cnt from T;
  end loop;
end;
/
select child_number, parse_calls, executions, reason "Reason: no flashback"
from v$sql
left join V$SQL_SHARED_CURSOR using(sql_id, child_number)
where sql_text like 'SELECT COUNT(*) FROM T%';

CHILD_NUMBER PARSE_CALLS EXECUTIONS Reason: no flashback
------------ ----------- ---------- --------------------
           0           1         10

Flashback queries are evil – not all the time

Instead of adding AS OF SCN to every table in my query, I can use DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER to set the “consistent read” SCN for every table in every query. Let’s see what happens with this technique:

declare 
  l_scn number;
  l_cnt number;
begin
  dbms_flashback.disable;
  execute immediate 'alter system flush shared_pool';
  l_scn := dbms_flashback.get_system_change_number - 1001;
  for i in 1..10 loop
    dbms_flashback.enable_at_system_change_number(l_scn+i);
    select count(*) into l_cnt from T;
    dbms_flashback.disable;
  end loop;
end;
/
select child_number, parse_calls, executions, reason "DBMS_FLASHBACK, increasing SCN"
from v$sql
left join V$SQL_SHARED_CURSOR using(sql_id, child_number)
where sql_text like 'SELECT COUNT(*) FROM T%';

CHILD_NUMBER PARSE_CALLS EXECUTIONS DBMS_FLASHBACK, increasing SCN
------------ ----------- ---------- ------------------------------
           0          10         10

Notice that there are 10 “parse calls” here. Oracle did a “soft parse”, that is it looked in the library cache for a cursor that could be reused, and it found one. This is extra work as compared to a normal query, but not nearly as much as a hard parse. More importantly, the library cache is not flooded with useless child cursors.

Conclusion

It turns out that we can safely use this technique under certain conditions:

  • The FLASHBACK and SELECT privileges must be granted on whatever tables we want to query;
  • The EXECUTE privilege must be granted on DBMS_FLASHBACK;
  • The SCN parameter of ENABLE_AT_SYSTEM_CHANGE_NUMBER must be increasing! If it decreases, we get hard parses and extra child cursors.
    This should never be a problem in the “optimistic locking” use case.

Optimistic Locking 7: Restartability

When we UPDATE, Oracle may find a discrepancy between the read-consistent and “current” versions of the data. If so, Oracle “restarts” the process. Our optimistic locking solution must make that happen when appropriate.

(It’s been a long time since my last post on this subject: see Optimistic Locking 4: the Good for context.)

What happens when we UPDATE?

(To simplify, I will only talk about the default READ COMMITTED isolation level.)

If we do an UPDATE without a prior SELECT FOR UPDATE,

  • Oracle will start by doing “consistent gets” of the target rows. This produces a result set that is read-consistent.
  • Next, Oracle will do “current gets” to lock and update the target rows.
    • If there is a predicate in the WHERE clause, such as WHERE HIREDATE > TRUNC(SYSDATE, 'YY'), and the column has been changed in the meantime, then Oracle will check whether the WHERE clause is still satisfied,
    • and if not then Oracle will “restart” the update processing.

Oracle will not stop until it has gotten and locked current versions of all the rows that meet the WHERE clause criteria in a read-consistent manner.

Re-evaluate, then (maybe) Restart

To make sure restarts happen when they should, we need to know:

  1. What columns set off the re-evaluation? To set off the re-evaluation, a changed column must be referenced in the WHERE clause or in a BEFORE UPDATE...FOR EACH ROW trigger. The ORA_ROWSCN pseudocolumn does not set off a re-evaluation.
  2. Once the re-evaluation is under way, what additional data is evaluated to decide whether to restart?
    If the re-evaluation takes place, the entire WHERE clause is evaluated.
    If ORA_ROWSCN is included in the WHERE clause, its “current” value is used in any comparison.

If we want to use ORA_ROWSCN for optimistic locking, we need to reference in the WHERE clause any columns we ourselves change. That will set off the re-evaluation, which will then use ORA_ROWSCN to determine whether some other process has changed the row since we last queried it.

See Avoiding Lost Updates with ORA_ROWSCN for a demonstration. Here is an example of an UPDATE that will restart when necessary:

update emp set sal = 800, deptno = 30
where empno = 7369
and ora_rowscn <= 13865770
and coalesce('*',sal || deptno) is not null;

I decided to use COALESCE to refer to the column values because it uses short-circuit evaluation. Once it determines that ‘*’ is not null, it doesn’t bother evaluating the concatenation of the columns. Also, concatenation implicitly converts every datatype to VARCHAR2 so I can list strings, dates, timestamps, numbers and intervals without worry. To prevent the Optimizer from removing that condition someday, I could replace ‘*’ by a bind variable with a non-null value.

Conclusion

ORA_ROWSCN can indeed be used as a “version number” that can be checked to avoid lost updates. The UPDATE statement just has to list the appropriate columns in the WHERE clause, but it doesn’t have to check them against values from a prior SELECT statement. The only real value that needs to be checked is ORA_ROWSCN.

In the next post I’ll address the problem of “false alarms”: ORA_ROWSCN will tell us that a row has previously been updated, but it will not say whether the values have really changed in the columns we care about.

Optimistic Locking 6: which ORA_ROWSCN?

Continuing my series on using ORA_ROWSCN to achieve optimistic locking, let’s zoom in on ORA_ROWSCN itself. I’ve been asking myself this question:

  • ORA_ROWSCN can be maintained at the block level or the row level. What is the difference and which one should we use?

Previously in this series

I want to implement optimistic locking in a two-phase database API:

  • The “query API” queries data and sends it to the user.
  • The “update API” receives changes from the user to be applied to the database.

Here is how ORA_ROWSCN fits into the overall scenario:

  1. Client calls the query API to display data to the user.
  2. The query API returns the data for the user.
    1. All the rows that might be updated are SELECTed here.
    2. The SELECT ensures that ORA_ROWSCN will be less than or equal to the “read-consistent SCN”.
    3. The data is read-consistent, and the “read-consistent SCN” is returned along with the data.
  3. Client calls the update API to update the data.
    1. The “read-consistent SCN” from step 2. is sent back along with the updates.
  4. The update API compares each ORA_ROWSCN to the”read-consistent SCN”.
    1. If any ORA_ROWSCN is greater, then some intervening update has occurred
      and the update cannot continue. All lost updates have been avoided.

I’m going to zoom in on the statements in bold red (2B and 4). How they work depends on whether ORA_ROWSCN tracks the SCN at the block level or the row level.

Block Level or Row Level?

By default, ORA_ROWSCN reflects the “commit SCN” of the most recent change to any row in a block. If a table is created with ROWDEPENDENCIES, the size of each row is increased in order to store an SCN with each row. We can see the difference with this simple illustration:

SQL> create table t as select level n from dual connect by level <= 2;

Table T created.

SQL> select n, ora_rowscn from t;

N  ORA_ROWSCN
-- ----------
 1 7949719
 2 7949719

SQL> update t set n = 3 where n = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select n, ora_rowscn from t;

N  ORA_ROWSCN
-- ----------
 1 7949731
 3 7949731

SQL> drop table t purge;

Table T dropped.

SQL> create table t rowdependencies as select level n from dual connect by level <= 2;

Table T created.

SQL> select n, ora_rowscn from t;

N  ORA_ROWSCN
-- ----------
 1 7949749
 2 7949749

SQL> update t set n = 3 where n = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select n, ora_rowscn from t;

N  ORA_ROWSCN
-- ----------
 1 7949749
 3 7949760

As you can see, with block-level tracking every row in a block has the same ORA_ROWSCN, even for rows that were not changed in the most recent transaction.

Optimistic Locking with block-level SCN

In my previous post on the SCN, I was working with block-level SCNs. We saw that after a SELECT, all the data blocks that were read consistent now have ORA_ROWSCN values no greater than the read-consistent SCN. This is exactly what statement 2B above is saying.

Unfortunately, for statement 4 things are not so simple. If one row in a block has just been changed, the ORA_ROWSCN will increase for the other rows as well. When my “update API” checks one of the other rows, it will say the row has been updated when in fact it has not.

As a result, the SCN comparison will always detect updated rows, but it will sometimes say a row was updated when it was not. We probably need some confirmation before saying the row was truly changed.

Optimistic Locking with row-level SCN

If we really want to depend on the SCN comparison alone, we should create the table with ROWDEPENDENCIES. There are some limitations when we do this: for example, table compression is not possible. The advantage is that our “statement 4” is now true: the ORA_ROWSCN of a row will only increase when that row was updated in a more recent transaction.

As I mentioned in my previous post on the SCN, it is possible that a row-level ORA_ROWSCN will be greater than the block-level SCN used to get read-consistent data. However, it is hard to imagine a real-life scenario where the row-level ORA_ROWSCN would be greater than the “read-consistent SCN” used in my query API.

Which ORA_ROWSCN should we use?

It depends, of course.

  • If we are free to create our tables with ROWDEPENDENCIES, and if it is enough to detect conflicting updates at the row level, then comparing ORA_ROWSCN to the “read-consistent” SCN should be sufficient for our needs.
  • If we are updating tables without ROWDEPENDENCIES, or if we want to detect real changes to values in the row,
    • then ORA_ROWSCN will tell us which rows have certainly not changed, so we can update them directly.
    • For the small number of rows that might have changed, we need to write code to check the values.
      Even then, our “read-consistent SCN” will help us do that – as I’ll explain in a later post.

Next up: Tom Kyte was wrong – about being wrong! ;)

 

Optimistic Locking 5: “Read-consistent” SCN

Continuing my series on using ORA_ROWSCN to achieve optimistic locking, let’s zoom in on the query API and the “read-consistent” SCN of the data it returns. I’ve been asking myself these questions:

  • How do we get read-consistent data in the query API if it does more than one SELECT?
  • How do we know the exact value of the “read-consistent” SCN?

Previously in this series…

Suppose you and I query the same data at the same time, then you update it, then I update it. If I never know you changed the data, my update will overwrite yours and your update will be “lost”. This is a danger of stateless applications that cannot use SELECT FOR UPDATE to prevent write conflicts. “Optimistic locking” checks the data when I update it to make sure no one else has changed it in the meantime.

My solution implements a two-phase database API:

  1. The “query API” queries data and sends it to the user.
  2. The “update API” receives changes from the user to be applied to the database.

This post concentrates on the query API, making sure the data returned is read-consistent and determining the “read-consistent” SCN of the data.

Read consistency

In Oracle, queries are “read consistent”, which means “The data returned by a query is committed and consistent for a single point in time.” This point in time is defined by an SCN. I’ll call that SCN the “read-consistent SCN”. By default Oracle uses the SCN that was current when the query started, but within read-only or serializable transactions it uses the SCN when the transaction began.

If the query API does only one SELECT, then the data is automatically read-consistent, with one exception: if the SELECT calls a PL/SQL function, then any queries within that function may use a later SCN as a starting point.

To ensure read-consistency, the query API must either:

  • do one and only one SELECT (avoiding any queries within functions), or
  • set the transaction READ ONLY (and of course COMMIT at the end of the API.
  • (I won’t go into flashback queries because they cause extra parsing and fill the shared pool with child cursors.)

Read-consistent SCN

Providing read-consistent data in a query API is not hard if we pay attention to what we are doing. Getting the “read-consistent SCN” is another story.

Let’s start by just getting the current SCN. I know of three methods:

  1. timestamp_to_scn(systimestamp) : this method is not precise and should be avoided.
  2. Accessing V$DATABASE: this method works but advances the SCN. It also requires read access to V$DATABASE.
  3. Using DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER: this method works, does not advance the SCN, but requires the EXECUTE privilege on DBMS_FLASHBACK.

I strongly recommend the third method (especially since we are going to need DBMS_FLASHBACK for other reasons).

Let’s suppose our query API has just one SELECT. When do we call DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER? If we call it within the query, there is no guarantee that it will be called at the very beginning, so the result may be slightly later than the true “read-consistent” SCN. If on the other hand we get the SCN beforehand, the result may be slightly earlier.

What are the risks in each case? If within :

  1. The query determines the “read-consistent” SCN
  2. Someone else commits an update (which will be invisible to the query), so new SCN.
  3. The query calls DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER and gets the new SCN.
  4. When it comes time to check the data for “lost updates”, the change in step 2. will not be detected.

If beforehand :

  1. We call DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
  2. Someone else commits an update, so new SCN.
  3. The query runs with the new SCN and sees the update.
  4. When it comes time to check the data, the change in step 2. will be detected and assumed to be a “lost update”, when in fact the change was visible in the query.

It is much better to call the function beforehand to avoid any undetected update, even though it introduces a very slight risk of announcing a write conflict where none exists.

If we really want to get the precise “read-consistent” SCN – or if the query API does more than one SELECT – we must use transaction-level read consistency. We can start a READ ONLY transaction and then get the START_SCN value from V$TRANSACTION:

create or replace function set_read_only_get_scn
  return number as  
  l_scn_trans number;
  l_local_trans_id varchar2(32);
begin
  set transaction read only;
  l_local_trans_id := dbms_transaction.local_transaction_id;
  select start_scn into l_scn_trans
  from v$transaction
  where l_local_trans_id = 
    to_char(xidusn, 'fm999999') || '.' ||
    to_char(xidslot,'fm999999') || '.' ||
    to_char(xidsqn, 'fm999999');
  return l_scn_trans;
end set_read_only_get_scn;
/

Using a READ ONLY transaction has several effects:

  • There can be no outstanding uncommitted changes. If there are, an exception is raised.
  • No DML or DDL allowed – which is fine in a query API.
  • Unlike with flashback queries, cursors in the shared pool are reused, whereas flashback queries cause multiple child cursors to be generated.
  • The query API must COMMIT before returning the data.

And the answer is…

In the query API, use a READ ONLY transaction to get fully read-consistent data from any number of SQL queries, and to obtain the exact “read-consistent” SCN used by those queries.

If the API does only one SELECT, then calling DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER just before the query is fine in my book, even though theoretically it might return an SCN very slightly lower than the real “read-consistent” SCN.

Next up: the two flavors of ORA_ROWSCN.

Footnote (2017-09-12)

I forgot to credit and thank Jonathan Lewis for the code that gets the “start SCN” of a read-only transaction.

Optimistic Locking 4: the Good

After discussing lost updates, the SCN and my theoretical use case, here is my idea for doing SCN-based optimistic locking. I’m going to simplify and emphasize the advantages here; limitations and gruesome details will come later.

Context

Here is the situation I am thinking of, before adding my solution.

There is an OLTP application. The “client” part is either a generic middleman like ORDS or a program written in java, PHP, node.js, etc. . The “client” accesses the database via SQL*Net. To support updates, the “data” part of the application provides two APIs in the database, one for querying the data in the first place and another for processing the updates. The query API returns read-consistent data, and the update API is atomic: either all the updates occur or none of them occur.

The dialogue between “client” and “data” uses generic connections and stateless sessions, so optimistic locking is the only solution for avoiding lost updates. I’ll start with a simplistic “lost update” requirement: if the “client” wants to update some rows, and someone else has already updated one of them between query time and update time, then the update must not take place.

In order to avoid the “object-relational impedance mismatch”, without frameworks and without being language-specific, I am going to use JSON as my data-interchange format.

The application scenario is:

  1. Client calls the query API to display data to the user.
  2. The query API returns the data for the user.
    • If any additional data is needed to support optimistic locking, the API has to return it here.
  3. Client calls the update API to update the data.
    • Any additional data from step 2. has to be sent back along with the updates.
  4. The update API uses the additional data to check for intervening updates during its processing.

Adding the SCN solution

My idea is based on comparing:

  1. The “read-consistent SCN”:  it determines what read-consistent version of the data is returned from a query.
  2. ORA_ROWSCN : a pseudocolumn that “reflects the system change-number (SCN) of the most recent change to a row.”

As I showed in an earlier post, the “SCN of the most recent change” may be an upper bound, but the query itself will adjust that upper bound downwards so it will not be greater than the “read-consistent SCN”.

Now I’ll add the SCN to my scenario:

  1. Client calls the query API to display data to the user.
  2. The query API returns the data for the user.
    1. All the rows that might be updated are SELECTed here.
    2. The SELECT ensures that ORA_ROWSCN will be less than or equal to the “read-consistent SCN”.
    3. The data is read-consistent, and the “read-consistent SCN” is returned along with the data.
  3. Client calls the update API to update the data.
    1. The “read-consistent SCN” from step 2. is sent back along with the updates.
  4. The update API compares each ORA_ROWSCN to the”read-consistent SCN”.
    1. If any ORA_ROWSCN is greater, then some intervening update has occurred and the update cannot continue.
      All lost updates have been avoided.

Advantages

I’m concentrating on the “good” here, since I want you to keep reading. Several things tempt me in this approach:

  • No special “version number” or “change timestamp” columns needed in the table.
  • No CPU spent doing checksums or hashes.
  • Only one number, the SCN, sent back and forth between the “client” and the APIs.
  • The data used for optimistic locking (the SCN) can be kept separate from the user data.
  • The “client” is freed from practically all implementation details.

If you base optimistic locking on row version numbers, row timestamps, or checksums of row columns, then the client code has to keep track of that data and send it back with the updates. With the SCN, the data from each row is just user data, not technical data. It can be pivoted, unpivoted, aggregated or denormalized. With JSON, it is trivial to separate the SCN from all the user data.

All the client code has to do is keep the SCN somewhere and include it in the call to the update API.

Example

My query API is going to get the current SCN, then query the DEPT table.

create or replace function queryAPI return varchar2 is
  l_scn number;
  l_result varchar2(4000);
begin
  l_scn := dbms_flashback.get_system_change_number;
  select json_object(
    'SCN' value l_scn,
    'DEPTS' value json_arrayagg(
      json_object(
        'DEPTNO' value DEPTNO, 
        'DNAME' value DNAME, 
        'LOC' value LOC
      )
    )
  ) into l_result
  from dept;
  return l_result;
end queryAPI;
/

Now my “client” (Oracle SQL Developer) will call the API:

var v_result varchar2(4000);
exec :v_result := queryAPI;
select json_query(:v_result, '$' pretty) from dual;


{
  "SCN" : 22422562,
  "DEPTS" :
  [
    {
      "DEPTNO" : 10,
      "DNAME" : "ACCOUNTING",
      "LOC" : "NEW YORK"
    },
    {
      "DEPTNO" : 20,
      "DNAME" : "RESEARCH",
      "LOC" : "DALLAS"
    },
    {
      "DEPTNO" : 30,
      "DNAME" : "SALES",
      "LOC" : "CHICAGO"
    },
    {
      "DEPTNO" : 40,
      "DNAME" : "OPERATIONS",
      "LOC" : "BOSTON"
    }
  ]
}

After changes to DNAME and LOC in the user interface, the client is going to send this data to the update API:

{
  "Action": "Update",
  "SCN": 22422562,
  "DEPTS": [
    {
      "DEPTNO": 10,
      "DNAME": "Admin",
      "LOC": "Seattle"
    },
    {
      "DEPTNO": 20,
      "DNAME": "Marketing",
      "LOC": "Toronto"
    },
    {
      "DEPTNO": 30,
      "DNAME": "Purchasing",
      "LOC": "Seattle"
    },
    {
      "DEPTNO": 40,
      "DNAME": "HR",
      "LOC": "London"
    }
  ]
}

In the update API, I’ll use JSON_TABLE to convert the JSON data to SQL rows and columns, then use FORALL to do the updates in bulk. For any row, if ORA_ROWSCN is greater than the read-consistent SCN then the update will not take place and I raise an exception. That is enough to roll back all the work done in that call to the API.

create or replace procedure updateAPI(p_json_input varchar2) as
  cursor c_input is select 0 SCN, deptno, dname, loc from dept;
  type tt_dept is table of c_input%rowtype;
  lt_dept tt_dept;
  l_rowcount number;
  c_dummy varchar2(1) := substr(p_json_input,1,1);
begin

  -- get data in SQL format. Every row will have the SCN
  select SCN, deptno, dname, loc bulk collect into lt_dept
  from json_table(p_json_input, '$'
    columns(
      action path '$.Action',
      scn number path '$.SCN',
      nested path '$.DEPTS[*]'
      columns(
        deptno number path '$.DEPTNO',
        dname path '$.DNAME',
        loc path '$.LOC'
      )
    )
  )
  where action = 'Update';

  -- Use FORALL to update in bulk
  forall i in 1..lt_dept.count
    update dept set dname = lt_dept(i).dname, loc = lt_dept(i).loc
    where deptno = lt_dept(i).deptno
    and ora_rowscn <= lt_dept(i).scn
    and coalesce(c_dummy,dname||loc) is not null;

  -- Check for row not updated due to ORA_ROWSCN change
  for i in 1..lt_dept.count loop
    if sql%bulk_rowcount(i) = 0 then raise_application_error(
      -20001,
      'DEPT row with DEPTNO = ' 
      || lt_dept(i).deptno 
      ||' already changed by another user. No updates have been made.'
    );
    end if;
  end loop;
end updateAPI;
/

Now suppose that, between the query and the update, someone else has updated department 10. In that case:

declare
  l_scn varchar2(100);
begin
  select json_value(:v_result, '$.SCN') into l_scn from dual;
  updateAPI('{"Action":"Update","SCN":'||l_scn||',"DEPTS":[
          {"DEPTNO":10,"DNAME":"Admin","LOC":"Seattle"},
          {"DEPTNO":20,"DNAME":"Marketing","LOC":"Toronto"},
          {"DEPTNO":30,"DNAME":"Purchasing","LOC":"Seattle"},
          {"DEPTNO":40,"DNAME":"HR","LOC":"London"}
          ]}');
end;
/

ORA-20001: DEPT row with DEPTNO = 10 already changed by another user. No updates have been made.
ORA-06512: at "STEW.UPDATEAPI", line 28
ORA-06512: at line 5

There: that wasn’t too hard, was it?

Too Good to be True?

I hope I have gotten the basic idea across and aroused your interest. In my next few posts, I’ll talk in more detail about limitations or possible objections and how to deal with them. Here are some of the issues:

  • How do we get read-consistent data in the query API if it does more than one SELECT?
  • How do we know the exact value of the read-consistent SCN? My example code is not perfect in that regard.
  • ORA_ROWSCN can be maintained at the block level or the row level. What is the difference and which one should we use?
  • Tom Kyte gave up on this idea because a change in ORA_ROWSCN does not “trigger a restart” of the update, see https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2680538100346782134. How do we get the restart to happen when it should?
  • What happens if an update occurred but the columns we want to update did not change? How can we avoid “false alarms”?
  • How can we provide more information on the intervening update so the user can better decide what to do next?

More later!

Optimistic ORA_ROWSCN 3: the Use Case

My goal is to show how ORA_ROWSCN can help deal with the problem of “lost updates”. Before that, I need to explain the use case I have in mind: an OLTP application that provides a transactional API in the database, using JSON for data interchange, across a stateless connection.

OLTP versus Batch

Technically, OLTP (On Line Transaction Processing) implies lots of small transactions running concurrently, whereas “batch” implies changing lots of data in one process, using bulk or set-based techniques. There is another important difference: with OLTP, someone is there!

To avoid a “lost update”, someone has to be there to “find” it. Suppose you and I query a row at the same time, then we both try to update it. If your update happens first and commits, I need to know it so I can decide whether my update should overwrite yours. In other words, I need to “find” your update so I can think about how to deal with the conflict.

When a batch program runs, there is no one there to “find” an update made by something else while the batch is running.

A prerequisite of avoiding “lost updates” is the presence of a human being who can be notified in case of a conflict and decide what to do next.

Stateless: Request and Response

When you and I write SQL or PL/SQL, we generally use a tool like SQL Developer, Toad or SQL*Plus. These tools use the same client / server architecture that applications used in the 1980’s: each person has a conversation with the database using a session that keeps track of what is going on. If I query 1000 rows in SQL Developer, the tool will execute the query and fetch 100 rows. If I scroll down, the tool will fetch 100 more rows from the same query – because the cursor was left open in my session.

The fact that the session remembers what is going on during these exchanges is called “state”.

In modern OLTP applications, people access servers using HTTP, then the servers access the database using connections from a pool. Each connection has a session that can by used by you, then me, then you again. When you use the session a second time, the session remembers nothing from your previous use – if it does, there is a huge bug in the application. This absence of “state” is what we call “stateless”.

Being “stateless” makes a big difference. A “stateful” interface can do a SELECT FOR UPDATE, then UPDATE the rows that I change. A “stateless” interface cannot do that, since the session I used for the SELECT and the session I use for the UPDATE are not necessarily the same.

A stateless application must use a pure “Request and Response” protocol with the database:

  • I ask the application to query some data for me. The application gets a connection from the pool, requests the data and shows me the response from the database. The database session forgets about me because the next user will likely be somebody else.
  • I ask the application to update some data. The application gets whatever connection and session are free. If the database needs some information from my first query, the application has to pass it back.

The whole idea of “optimistic locking” is based on this scenario: a stateless OLTP application with a real life user who has to be informed of update conflicts.

JSON and the Object-relational impedance mismatch

As stated in the JSON standard, JSON is a data interchange format. Did I emphasize that enough for you? JSON was not meant for storing data, but for exchanging it between programming languages. Starting with Oracle Database 12c, SQL can query JSON (meaning it can map JSON to SQL rows and columns); starting with version 12.2, SQL can also generate JSON from “normal” database data. In other words, SQL all by itself can now use JSON as a data interchange format.

Ever since Java hype got huge mind share in the minds of enterprise IT architects, developers are supposed to do object-oriented programming. Thinking of data as rows and columns in a SQL database is old hat. Instead of data, there are objects with attributes and methods; several EMP objects can point to the same DEPT object, whereas in a JOIN the same DEPT data is repeated across several rows. The conceptual differences between “object” and “relational” are important enough to have a fancy name: the “object-relational impedance mismatch”. People write object-relational mapping frameworks so that application code can ignore SQL as much as possible, leading to all sorts of wasted resources, time and money.

By using JSON intelligently as a data interchange format, we can map relational data to an “object-friendly” form right in our SQL statements. As long as the application developers and the SQL developers understand the mapping and what underlies it, each can work in their own environment with data everyone can access with relative ease.

Note I am doing the JSON-relational mapping in the database, in SQL. The database client (application or generic tool) accesses the database via SQL*Net and receives data in JSON format already. That data may or may not be a payload for a RESTful web service. JSON in the database can be used in browser-based, #ThinJava applications but also in enterprise applications with thick application layers.

Transactional API: read-consistent, write-coherent

I wish I had a better term than “transactional API”, because I don’t mean the same thing by it as others – sorry!  Let me explain.

Getting back to the “lost update” problem, the user needs one API to query some data and another API to update it. Each API has to include one “request” from the application and one “response” from the database (this is all stateless, remember). In my view, every “query” API should return read-consistent data, and every “update” API should be transactional – at least potentially.

As far as querying is concerned, I am not saying that two different responses from the database should be consistent – only that all the data contained within one response should be internally consistent. If data from two queries are combined in one response, then the queries should be “as of” the same SCN.

With respect to “update” APIs (or any API that changes data), the API should provide everything the database needs to make a coherent set of changes that can reasonably be committed together. This is what we used to call a “logical unit of work”. If a change doesn’t make sense by itself, but only as part of a whole, then there should be no API to make just that change.

Unlike the #SmartDB paradigm, I don’t insist that the API commit before returning. It may be convenient for the application to do a complex transaction that involves two APIs which must succeed together or fail together. It may be that the application accesses multiple data sources and does two-phase commits. I do insist that the API is “atomic”: the changes either entirely succeed or entirely fail, and if they fail the database returns to precisely the state it was in before the API was called. For want of a better phrase, I’ll call this API a “write-coherent” API.

When is there a lost update?

Let me add one final touch to my use case. Optimistic locking schemes are often row-based: if you change any column in a row, I get notified of a “lost update” even if I am changing another column. My approach will start with that idea, but then I will make it more granular so that only updates to the same column will cause a conflict.

Next up: an overview of the ORA_ROWSCN solution.