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;
An Oracle Programmer 