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.

Avoiding Lost Updates with ORA_ROWSCN

Applications today use “optimistic locking” to avoid lost updates. Tom Kyte thought of using ORA_ROWSCN for optimistic locking, then decided it didn’t work (see ORA_ROWSCN for optimistic locking). I now think it does!

If you don’t understand what an SCN is, and how it helps with read consistency, this post may go over your head. Sorry I can’t take the time to try explaining all that here…

Test Data and Use Case

By default, Oracle keeps one ORA_ROWSCN per block. However, we can use ROWDEPENDENCIES when creating the table to make room for one SCN per row.

I’m going to create the venerable EMP table with ROWDEPENDENCIES:

CREATE TABLE EMP (
    EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2)
) rowdependencies;

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;

select distinct ora_rowscn from emp;
ORA_ROWSCN
13848205

 

In honor of Oracle’s 40-year birthday, SMITH is going to submit a batch job to give everyone a 10% raise, including himself. At the same time, MILLER is doing a reorganization: he assigns SMITH to department 30, using an OLTP application that updates both department number and salary.

Lost Updates

Here is what happens when an application neglects “optimistic locking”:

BATCH MILLER
– A1: SMITH submits a batch job for 10% raises

update emp set sal = sal * 1.1;

– A2: MILLER queries SMITH and sees committed data

select empno, sal, deptno from emp
where ename = ‘SMITH’;

EMPNO SAL DEPTNO
7369 800 20
– A3: MILLER reassigns SMITH: update waits for batch job to commit

update emp set sal = 800, deptno = 30
where empno = 7369;

– A4: the batch job commits

commit;

-The application sees “1 rows updated.”
– A5: MILLER commits

commit;

– A6: SMITH discovers he was the only one who didn’t get a raise!

select empno, sal, deptno from emp
where ename = ‘SMITH’;

EMPNO SAL DEPTNO
7369 800 30

 

You see what happened here? Oracle told MILLER that SMITH’s salary was 800, because that was the latest committed value. The batch update had not committed, so the new salary was invisible to any session but the batch session itself. Since the OLTP application updated the salary as well, it replaced the new salary with the old one.

For details on lost updates, see the documentation on Data Concurrency and Consistency.

Optimistic Locking using column values

To avoid lost updates, the application has to check during its update that someone else has not changed the data in the meantime.

BATCH MILLER
– B1: SMITH submits a batch job for 10% raises

update emp set sal = sal * 1.1;

– B2: MILLER queries SMITH and sees committed data

select empno, sal, deptno from emp
where ename = ‘SMITH’;

EMPNO SAL DEPTNO
7369 800 20
– B3: MILLER reassigns SMITH: update waits for batch job to commit

update emp set sal = 800, deptno = 30
where empno = 7369
and sal = 800 and deptno = 20;

– B4: the batch job commits

commit;

 -The application sees “0 rows updated.”
and explains to MILLER that someone else
already changed the data.
– B5: SMITH got his raise!

select empno, sal, deptno from emp
where ename = ‘SMITH’;

EMPNO SAL DEPTNO
7369 880 20

 

You may be wondering why Step B3 didn’t find the row it wanted to update. That is because of “restartability”:

  • Oracle did a “consistent get” to get the latest committed values, where SAL was 800
  • but then it did a “current get” to apply MILLER’s change to the current version of the data. This is when it blocked, waiting for the batch job to commit and release the lock on that row.
  • When the “current get” finally happened, Oracle noticed that the SAL value had changed, so it restarted the update: it did another consistent get, at which time it found no rows that matched the WHERE clause – so it did not do the update.

For a detailed explanation of restartability, see these posts by Tom Kyte:

http://asktom.oracle.com/Misc/something-different-part-i-of-iii.html

http://asktom.oracle.com/Misc/part-ii-seeing-restart.html

http://asktom.oracle.com/Misc/part-iii-why-is-restart-important-to.html

Why not use ORA_ROWSCN?

Tom discovered that putting ORA_ROWSCN in the WHERE clause did not trigger a restart. In our scenario:

BATCH MILLER
– C1: SMITH submits a batch job for 10% raises

update emp set sal = sal * 1.1;

– C2: MILLER queries SMITH and sees committed data

select empno, sal, deptno, ora_rowscn from emp
where ename = ‘SMITH’;

EMPNO SAL DEPTNO ORA_ROWSCN
7369 800 20 13857633

 

– C3: MILLER reassigns SMITH: update waits for batch job to commit

update emp set sal = 800, deptno = 30
where empno = 7369 and ora_rowscn = 13857633;

– C4: the batch job commits

commit;

-The application sees “1 rows updated.”

 

No reason to go any further: the update did not see that the batch job had changed the ORA_ROWSCN.

Making ORA_ROWSCN work!

Here is what I stumbled on: in the WHERE clause, referring to ORA_ROWSCN will not trigger a restart, but referring to SAL, which changed, will trigger a restart – and once the restart is triggered, then the new “consistent get” will see the new value of ORA_ROWSCN. We don’t need the old value of SAL, we just need to mention it. For example:

BATCH MILLER
– D1: SMITH submits a batch job for 10% raises

update emp set sal = sal * 1.1;

– D2: MILLER queries SMITH and sees committed data

select empno, sal, deptno, ora_rowscn from emp
where ename = ‘SMITH’;

EMPNO SAL DEPTNO ORA_ROWSCN
7369 800 20 13865770

 

– D3: MILLER reassigns SMITH: update waits for batch job to commit

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

– D4: the batch job commits

commit;

 -The application sees “0 rows updated.”
and explains to MILLER that someone else
already changed the data.

 

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.

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.

There are some limitations and caveats here:

  1. If your table has not been created with ROWDEPENDENCIES, ORA_ROWSCN will change every time any row in the block has changed. The table has to be “recreated” with ROWDEPENDENCIES for this to work.
  2. You cannot use this method with Index Organized Tables! If you create such a table with ROWDEPENDENCIES, then SELECT ORA_ROWSCN… will return NULL.
  3. You might wonder whether a “delayed block cleanout” will cause the ORA_ROWSCN to change without the data having changed. I don’t see how that could be a problem, since the SELECT done to get the data (and the ORA_ROWSCN) will do the cleanout as part of its processing.