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

3 thoughts on “Optimistic Locking 9: proof of concept

  1. Hi Stew,

    Crystal clear, like fresh water :)

    Since the code looks so very standard, I wonder whether Oracle could not be able to implement internally all these steps
    and add to the standard SQL language a new simple clause that could achieve the same result, something along the lines of:

    UPDATE dept
    SET …
    WHERE deptno = …
    AND [ ALL | UPDATED ] [DATA IS] CURRENT AS OF p_scn

    That is, while still having to preserve and pass back the SCN, the update could be performed in a single go,
    checking for each row to be updated whether either ALL columns or only the columns UPDATED by the current statement
    do still have the same values as at the given p_scn.

    Cheers & Best Regards,
    Iudith Mentzel

    • Hi Iudith,

      Good as always to hear from you. I also think some things could be done to improve or simplify this approach:

      1. Provide a function in SELECT that returns the SCN used for the consistent gets. As it is, my simple approach is not guaranteed to be absolutely accurate.
      2. Change the documentation to more clearly support this approach. The documentation states that the SCN may change, but it does not state that it can only become less.

      I do two updates for a reason: the first one is much less work for the database than the second one, so I only do the second one for the rows where the first one fails. Your suggestion simplifies the second update I do, but we would still need to do two steps.

      Best regards,
      Stew

  2. Hi Stew,

    Yes, you are right that currently there is no function returning the read consistent SCN for a query.
    A pseudocolumn named ORA_CURRENT_SCN could be nice for this purpose.
    Or, even amending the semantics of DBMS_FLASHBACK.get_system_change_number, so that when calling it from a query,
    it will return the exact SCN used for that query’s read-consistency.

    If Oracle were to implement my suggestion in a clever enough way, then I think that it could “catch” both situations in a single update,
    and do it as efficiently as possible.

    That is, when asking for condition “CURRENT AS OF p_scn”, it could apply the same two logical steps as your two updates do:

    a. If the row (or block) has ORA_ROWSCN <= p_scn, then the condition is true, without any more need to check the column values.
    b. If not, then the column values should be checked.

    I think that this should always work, because a column change always triggers a change (increase) in the ORA_ROWSCN,
    but not the opposite.

    An additional aspect that a 100% complete solution should consider is the possibility that a flashback query might theoretically
    fail because of a "snapshot too old" error.
    Though rather uncommon for a real-life web application, if Oracle implemented everything internally,
    then in such a case it could cause the (single) update to fail "out-of-the-box".

    Thanks a lot & Best Regards,
    Iudith

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s