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 :

  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);
  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.


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.


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.


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);
  l_scn := dbms_flashback.get_system_change_number;
  select json_object(
    'SCN' value l_scn,
    'DEPTS' value json_arrayagg(
        '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,
      "LOC" : "NEW YORK"
      "DEPTNO" : 20,
      "DNAME" : "RESEARCH",
      "LOC" : "DALLAS"
      "DEPTNO" : 30,
      "DNAME" : "SALES",
      "LOC" : "CHICAGO"
      "DEPTNO" : 40,
      "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);

  -- 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, '$'
      action path '$.Action',
      scn number path '$.SCN',
      nested path '$.DEPTS[*]'
        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(
      '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:

  l_scn varchar2(100);
  select json_value(:v_result, '$.SCN') into l_scn from dual;

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.

Optimistic ORA_ROWSCN 2: the SCN

In my previous post, I covered lost updates. Before saying how ORA_ROWSCN can help avoid them, I need to talk about the SCN. It’s hard to be simple and correct about such a central element of the Oracle database, but I’ll try.

Aside from the documentation, I am relying on Oracle Core by Jonathan Lewis, chapter 3, along with this blog post and Jonathan’s answer to my question on OTN.

The Commit SCN

The documentation defines the SCN, or “System Change Number”, as a “database ordering primitive. The value of an SCN is the logical point in time at which changes are made to a database.” It also calls it a “stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN.”

In other words:

  • Every change to data in the database is made as part of a transaction.
  • The changes in a transaction become effective when the transaction is committed.
  • Every committed transaction gets a unique SCN.
  • The SCN increments at each commit, which allows it to “order the database” in time.
  • Every “commit SCN” “defines a committed version of the database”, so the SCN is like a version number that applies to the entire database.

The “commit SCN” of a transaction is precisely the SCN that was assigned to a transaction on commit.

[UPDATE 2017-07-24: Jonathan Lewis just pointed me to a blog post by Frits Hoogland where he showed that it is possible for two commits to share the same SCN. To quote Frits: “both commits … have the same SCN. At this point I think it’s fitting to theorise why this is happening. I think this is happening and makes sense because the table is protected for consistency at another level in the database, which is the locking mechanisms (TX locks for rows, TM locks for segments), which guarantees that two transactions at the same time do not compromise data consistency. And because of that, it’s not a problem to batch them with the same SCN.”

Frits seems to have shown an error, or at least a simplification, in the documentation. Either that or “unique” means every transaction has just one “commit SCN”, even if one “commit SCN” can be associated with two simultaneous transactions.

Fortunately for me, this refinement doesn’t invalidate the rest of this post.]

[UPDATE 2017-08-01: thanks to Connor McDonald on asktom for posting this quote from the Concept Guide:

“Several events may share the same SCN, which means that they occurred at the same time in the database. Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN.”

This seems to confirm that two transactions can share the same SCN.]

The SCN and 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 – you guessed it – 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.

Now comes the hard part.

When we submit a query, Oracle gets the data blocks it needs to read the rows of interest. The problem is that each data block may contain uncommitted data, or data that was committed after the “read-consistent SCN”. To determine that, Oracle consults the ITL (Interested Transaction List) in the block header, and if necessary the UNDO that the ITL entries point to.

There are three case to consider:

  1. All the ITL entries show committed transactions having “commit SCNs” that are less than or equal to the “read-consistent” SCN.
  2. All the ITL entries show committed transactions, but there is at least one “commit SCN” greater than the “read-consistent” SCN.
  3. At least one ITL entry shows a transaction that has not committed.

Case 1. is the most frequent and the simplest: none of the data is more recent than the read-consistent SCN, so the query may use the block as is.

Case 2. is not as simple as you might think. The “commit SCN” in an ITL entry may be an upper bound: it cannot be less than the real “commit SCN”, but it could be greater. In this case Oracle cross-checks with the “transaction table slot” in the UNDO segment header. If it finds that the transaction was actually committed before the read-consistent SCN, it will use the block in the query, and it will lower the “commit SCN” stored in the ITL entry.

Case 3. is similar to case 2. The ITL entry says “transaction not committed”, but that may be because Oracle did not have time to update the ITL entry before the block was flushed from memory. Oracle must cross-check with the “transaction table slot” in the UNDO segment header. If it says “actually, the transaction has committed”, then the ITL entry is updated with an upper bound “commit SCN”. This is known as “delayed block cleanout”.

We can see case 3. and case 2. at work in the following example, using two independent sessions:

  • Session 1
create table T1 (a number, ts timestamp, val number);
create table T2 (a number, ts timestamp, val number);

    insert into T1 values (1, localtimestamp, 0);
    insert into T1 values (2, localtimestamp, 0);
    insert into T2 values (3, localtimestamp, 0);
select dbms_flashback.get_system_change_number current_scn from dual;


update T1 set ts = localtimestamp, val = 1 where a = 2;
alter system flush buffer_cache; -- (1)

    for i in 1 .. 5000 loop
        update T2 set val = val + 1;
        commit; -- (2)
    end loop;
select dbms_flashback.get_system_change_number current_scn from dual;


(1) Notice that we update T1, then flush the block from memory before the COMMIT. This prevents Oracle from doing the “commit cleanout” in memory that would normally put the real “commit SCN” in the ITL entry.

(2) After the update to T1, we do 5000 transactions to T2 in order to increment the SCN.

  • Session 2
set transaction read only; -- This simulates a long-running query
  • Session 1
    for i in 1 .. 5000 loop
        update T2 set val = val + 1;
    end loop;
select dbms_flashback.get_system_change_number current_scn,
ora_rowscn from T1 t;

----------- ----------
   20041709   20041359
   20041709   20041359

We use the pseudocolumn ORA_ROWSCN to query the “last change SCN” of the block, which corresponds to the highest SCN in the ITL. Notice the value is the same for both rows, even though we updated one row later than the other. Notice also that ORA_ROWSCN shows an SCN value that is clearly less than the current SCN (so we know the data is read consistent with the query) but much greater than the real commit SCN.

What happened here was what I called case 3. above. The data block was flushed from memory before the commit, so Oracle had to go to the UNDO to find out what happened. It discovered the transaction was committed some time ago, and updated the ITL entry to an SCN it could find efficiently and that was “good enough” for the query being executed. This SCN is still almost 10,000 more than the real “commit SCN”.

  • Session 2
select dbms_flashback.get_system_change_number current_scn,
ora_rowscn from T1 t;

----------- ----------
   20041714   20036604
   20041714   20036604

This is a example of what I called case 2. above. Oracle got the data block, found an SCN that was too recent for the query, and went to the UNDO to find out if the real commit SCN was lower. Once it found an SCN that was not greater than the read-consistent SCN (at the beginning of the transaction), it updated the ITL entry (together with the “last change SCN”) and used the data block in the query.

  • Session 1
select dbms_flashback.get_system_change_number current_scn,
ora_rowscn from T1 t;

----------- ----------
   20041714   20036604
   20041714   20036604

This is just to show that the ITL entry was well and truly changed for all sessions, not just for Session 2.

To summarize what we have seen: whenever a query accesses a data block, Oracle has to determine whether the block has changed earlier or later than the read-consistent SCN of the query. It may have to go to the UNDO to find that out, but if it does it updates one or more ITL entries so the next query doesn’t have to do the same work over again. At the end of the query, all the data blocks that were read consistent now have ORA_ROWSCN values no greater than the read-consistent SCN.

By the way, flashback queries work the same way. Using AS OF SCN nnnn, I can lower an ORA_ROWSCN several times in a row and get pretty close to the real commit SCN.


I said the pseudocolumn ORA_ROWSCN tells us what is the most recent SCN in the ITL. That is true if the table was created with NOROWDEPENDENCIES, which is the default. If we create the table using ROWDEPENDENCIES, Oracle makes room for an SCN for each row. For this kind of table, ORA_ROWSCN shows us the SCN stored with each row, not the overall SCN we discussed up until now. I’ll have more to say about this in a later blog post.

(Updated 2017-09-12) If we went through the example above using a table with ROWDEPENDENCIES, we would see different results for case 2. The first time an ITL entry gets a “commit SCN” (whether “upper bound” or not), the affected rows get that same “commit SCN”. In case 2, the ITL entry gets a new, lower “commit SCN” – but it is not copied to the affected row.

As a result, it is conceivable that the ORA_ROWSCN of a row will be greater than the most recent “commit SCN” stored in the data block.


  • Oracle orders all transactions in time by assigning each one a unique SCN on commit: this is called the “commit SCN”. [UPDATE 2017-07-24: it may be possible for two transactions to share the same “commit SCN” (and therefore be simultaneous), but no transaction will ever have more than one “commit SCN”.]
  • Every query returns results that were consistent as of an SCN that I call the “read-consistent SCN”: it corresponds to the start of the query (or the start of the transaction).
  • During query processing, Oracle compares the two SCNs and returns results where the “commit SCN” is never greater than the “read-consistent SCN”.

Sometimes a data block has incomplete information in its ITL, and Oracle consults the UNDO to determine whether the “commit SCN” is not greater than the “read-consistent SCN”. If it is, Oracle will update the ITL to save work for the next query.

The end result is that, after the query finishes, every ITL entry for eligible blocks will contain an SCN less than or equal to the “read-consistent SCN” of the query. This is crucial for doing optimistic locking with ORA_ROWSCN.

More later…

Optimistic ORA_ROWSCN 1: Lost Updates

I’ve gotten lots of feedback about my proposal to use ORA_ROWSCN for optimistic locking. I’m going to start over in more detail and try to leave less scope for misinterpretation. Thanks to all for your comments, especially Jonathan Lewis who saved me from some technical doubts.

“Optimistic locking” is a type of solution to the problem of “lost updates”. So what do we mean by “lost update”, anyway?

Same row, same column

The Oracle documentation defines a lost update as: “A data integrity problem in which one writer of data overwrites the changes of a different writer modifying the same data.” The Concepts document gives an example where two sessions update the same column in the same row of the same table:

  • Session 1 updates, but does not commit yet
  • Session 2 selects the row, but does not see the change made by Session 1 because it is not committed yet.
  • Session 2 updates the same column in the same row. In fact, it has to wait on Session 1.
  • Session 1 commits.
  • Session 2 is no longer blocked by Session 1, so the Session 2 update overwrites the Session 1 update.
  • Session 2 commits.
  • Session 1 queries the row and discovers its update is “lost”.

Why say this update is “lost”? Simply because Session 2 did not see it when it queried the data. Session 2 overwrote the update without ever seeing it. The documentation also calls this a “conflicting write”.

I think the following scenario is also a “lost update”.

  • Session 2 selects a row.
  • Session 1 updates a column in the row and commits.
  • Session 2 updates the same column in the same row and commits.
  • Session 1 queries the row and discovers its update is “lost”.

In both scenarios, the Session 1 update is “lost” because Session 2 (that is, the user behind Session 2) did not see the change before overwriting it.

Why should we care?

In real life, what we have here is a failure to communicate. Behind the two sessions are two users who want to put different values into the same place in the database. In collaborative software such as Lotus Notes, Session 1 would create a document and Session 2 would create another document marked as a “conflict”: after the fact, human beings would work together to resolve the conflict.

In the database, we can’t store both values simultaneously, so we generally communicate the existence of a conflict by refusing to execute the second update. Whoever gets the error can query the row again, see the change made by the first update, then decide what to do next.

Update vs. Changed Value

An “update” to a column does not necessarily mean that the value of the column changed!

If I say UPDATE EMP SET SAL = SAL, Oracle will perform an update, executing the same triggers and creating UNDO as it does when the SAL value changes. (There is one difference: if the column is indexed and the value does not change, Oracle doesn’t touch the index.)

Updating columns to the same value may mean two different things: either

  1. the application uses generic UPDATE code and always updates the same columns, whether the user wanted to update all of them or just some of them; or
  2. a user deliberately updates a column with the same value it had previously, as a way of confirming that the value is still valid.

I suspect the first case is much more common than the second, but the second cannot be completely ruled out.

Same row, different columns

In both of the scenarios above, suppose Session 1 updates one column and Session 2 updates another column in the same row. As long as Session 2 leaves the first column alone, there is no conflict, no “lost update”.

Multiple rows and columns

I recently learned of a project where the “lost update” scenario has been extended to a group of rows. Since the rows are related functionally, the developers want to avoid applying changes to one row if any of the others in the group have changed. I’ll call this requirement “detecting changed context” instead of “detecting lost update”.

“Lost updates”, and beyond

Optimistic locking basically means detecting updates or changes that were committed between the time the user queried the data and the time (s)he updated it.

But: different applications may want to detect different things. For example:

  • Detect only changed values in columns the user really wants to change.
  • Detect changed values in any updatable column of the row the user wants to change.
  • Detect changed values in the row, even if the user cannot update them.
  • Detect any update to the row, whether values changed or not.
  • Detect changes in the context, meaning all rows related to the target row(s), even those the user does not intend to update.

Mechanisms for detecting changes / updates

In the old “client / server” architectures, each user kept the same session for querying the data and updating it. To avoid conflicts at the row level, it was sufficient to do SELECT FOR UPDATE instead of a simple SELECT. That way, the current user saw all previous changes and the later users would see all the current changes. “Lost updates” were purely and simply avoided. This is known as “pessimistic locking”, since we assume conflicts will occur and take steps to avoid them.

In stateless architectures today, a user may obtain one session for querying the data and another for updating it. No session will remember anything about what happened when the user accessed the database previously. Therefore, SELECT FOR UPDATE is out of the question. This led to various “optimistic locking” strategies, where the data is to some extent queried again and compared to the values that were originally shown to the user. Some methods are:

  • column compare (all columns or only those of interest)
  • hash compare (hashing all or some columns)
  • timestamp compare (a row has been updated if the timestamp is different)
  • version compare (a rows has been updated if the version number has incremented).

Note that for efficiency, conflicts within a row can be detected directly in the UPDATE statement – a prior SELECT FOR UPDATE is not needed.

Where do I fit in?

My proposal is inspired by Tom Kyte (even though he gave up on ORA_ROWSCN himself). I want to use ORA_ROWSCN to detect conflicting updates to a row, whether any values changed or not. We’ll see in the following posts how this works, what requirements it fits and how much we can rely on it.

Optimistic Locking with one Timestamp?

A reader called chris asked me a good question about optimistic locking: why not use a “user-defined” scn (e.g. timestamp) as an extra column to achieve the same goal as ORA_ROWSCN? Well, chris, you’d have to use one per row, not one per transaction.

The Context

In this series of posts, I am assuming a two-phase API:

  1. The first API queries data and sends it to the user.
  2. The second API receives changes from the user to be applied to the database.

The goal is to avoid “lost updates”, by refusing to apply the changes if they would wipe out changes made by others between step 1. and step 2.

Update time and commit time

To really change a row in the database, we have to update it and commit. If we roll back instead, it’s as if the update never happened. What’s more, the update is one statement and the commit is another, so they do not occur at exactly the same moment. In the example I have been using to demonstrate, there is even an overlap: two sessions try to update the same row at about the same time, and the first commit only happens after both UPDATE statements have been issued.

What this means is that “update time” is not the same as “commit time”.

Which time can we control?

Let’s say we add a column called CHANGE_TS to our table, and we set it to SYSTIMESTAMP every time we update a row. That column will contain the “update time”, not the “commit time”. On the other hand, when Oracle updates ORA_ROWSCN the change has been committed, and the SCN is equal to (or possibly greater than) the SCN of the transaction. In other words, ORA_ROWSCN reflects “commit time”.

One SCN vs. one timestamp

The SCN is “a stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN.” To repeat: the SCN is a point-in-time version number for all committed data in the database.

If we send read-consistent data to the user, one SCN is enough to identify the “version” of that data. If that same SCN comes back with data to be updated, it is enough to determine whether a row has been updated by someone else in the meantime. Of course, we don’t know which column(s) have been updated, or whether the values have really changed – but the SCN is enough to double-check if we want.

  • We have the data the user wants to change.
  • We can get the current committed data with a normal query.
  • We can get the data we originally sent to the user with a flashback query “as of” that same SCN.

By comparing the data of interest, we can determine what values, if any, have actually changed in the meantime. If we want to go this far, we do have to write and test the code, but it will only execute for the rows whose SCN has actually changed. Most of the time, the SCN will not have changed and the update will take place efficiently.

If we use a TIMESTAMP column, we cannot send just one “current” timestamp to the user.

  • At the time of our query, there could be uncommitted data with slightly older timestamps, and our query would not see that data.
  • While our user is pondering his screen, that data gets committed.
  • When our user sends back new data with that timestamp,
    it would not see any of the changes that were updated before our query but committed afterwards.

Here is a scenario to illustrate.

drop table emp_ts purge;
create table emp_ts as select e.*, systimestamp change_ts from emp e;

– E1: SMITH submits a batch job for 10% raises

update emp_ts set sal = sal * 1.1, change_ts = systimestamp;

— E2: MILLER queries SMITH and sees committed data

select empno, sal, deptno, systimestamp current_ts from emp_ts
where ename = ‘SMITH’;

7369 800 20 2017-07-02 05:21:02.640271 -04:00
– E3: MILLER reassigns SMITH: update waits for batch job to commit

update emp_ts set sal = 800, deptno = 30, change_ts = systimestamp
where empno = 7369
and change_ts <= ‘2017-07-02 05:21:02.640271 -04:00’

– E4: the batch job commits


-The application sees “1 row updated.”
– E5: MILLER commits


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

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

7369 800 30

Conclusion: one timestamp is not enough

We have seen that comparing user-defined timestamps to just one overall “current” timestamp does not always work. If you use timestamps, the “query” API must send a timestamp for every table row involved in the query, and the user must send back a timestamp for every row to be updated. The user-defined timestamp becomes a version number for that row.

The SCN, on the other hand, is a version number for the entire database, so passing just one back and forth is enough to signal any number of “lost updates”. We can provide APIs (using JSON for example) that completely hide the table / row organisation of the data in the database!

Remember, I am assuming a two-phase API:

  • The first API queries data and sends it to the user.
    • With timestamps, every row sent to the user has to include its timestamp.
    • With SCNs, only one SCN needs to be sent.
  • The second API receives changes from the user to be applied to the database.
    • With timestamps, the user sends back the timestamp of each row to be updated, and for each row the API compares the returned timestamp to the current timestamp.
    • With SCNs, the user sends back one SCN, and for each row the API compares its current SCN to that one SCN.

More on Optimistic Locking with ORA_ROWSCN

Thanks to comments by Tony Hasler and pingbacks from Jeff Kemp, here’s more detail on how optimistic locking works with SCNs, especially with respect to “restarts” during update.

(I assume you have already read Avoiding Lost Updates with ORA_ROWSCN and Optimistic Locking: One SCN to rule them all before reading this post.)

Jeff’s post lists several methods for dealing with lost updates.

  • One of them, the “column compare” method, is different from the others. Suppose application A updates the DNAME column in DEPT, and application B updates the LOC column. If A checks only DNAME for a conflicting change, and B checks only LOC, then both applications can modify the same row independently.
  • The other methods, based on hashes, timestamps or version numbers, stop two sessions from updating the same row even if the columns involved are different.

So how does the “SCN” method behave? Well, it depends on whether there is a “restart”.

Again, suppose A in one session wants to update DEPT.DNAME and B in another session wants to update DEPT.LOC.

Scenario 1

  1. A: query DEPT where DEPTNO=10 and get current SCN
  2. B: query DEPT where DEPTNO=10 and get current SCN
  3. B: update LOC
  4. B: commit
  5. A: update DNAME, referring to the DNAME column and checking the SCN
    Result: since the ORA_ROWSCN has changed, the update is refused.

Scenario 2

  1. A: query DEPT where DEPTNO=10 and get current SCN
  2. B: query DEPT where DEPTNO=10 and get current SCN
  3. B: update LOC
  4. A: update DNAME, referring to the DNAME column and checking the SCN, which appears OK.
    The update will be blocked waiting for B to commit.
  5. B: COMMIT
  6. A: the update is unblocked and Oracle checks to see if the DNAME column has been updated. Since it has not, there is no restart and the ORA_ROWSCN pseudocolumn is not re-evaluated.
    Result: the update is allowed!

So, the behavior of the SCN method is inconsistent: when a restart is involved, applications can update different columns in the same row, but when no restart is involved they cannot.

Scenario 3

This scenario is just to emphasize that there is no “restart” if B rolls back.

  1. A: query DEPT where DEPTNO=10 and get current SCN
  2. B: query DEPT where DEPTNO=10 and get current SCN
  3. B: update LOC and DNAME
  4. A: update DNAME, referring to the DNAME column and checking the SCN, which appears OK.
    The update will be blocked waiting for B to commit.
  6. A: the update is unblocked and Oracle checks to see if the DNAME column has been changed. Since it has not (because of the rollback), there is no restart.
    Result: the update is allowed.

Dealing with “false positives”

If you want to allow independent updates to different columns in the same row, the SCN method will not work – most of the time. It will only work in a restart scenario, since you can decide which columns will trigger a restart. Even then, you know a column has been updated but you don’t know if its value is different: someone may have updated a column to the same value.

If that is truly a problem for you, our famous SCN can come to the rescue. When a row is not updated, do a flashback query (“AS OF SCN <your SCN>”) on that row. You can then do a “column compare” to see whether the changes concern you or not.

By the way, if you use the SCN method on a table without ROWDEPENDENCIES you risk getting “false positives” even when the row of interest has not been updated. Again, you can double-check with a flashback query and a “column compare”.


The SCN method will warn of a “lost update”:

  • If the table does not have ROWDEPENDENCIES: when any row in the block has been updated, deleted or inserted.
  • If the table has ROWDEPENDENCIES and no restart is involved: when any column in the same row has been updated.
  • If the table has ROWDEPENDENCIES and a restart is involved: when the columns listed in the WHERE clause of the update have been updated.

To make sure the warning is not a “false positive”, use the SCN in a flashback query, compare the columns of interest and then decide whether to redo the update.

With that extra code, the SCN method can be used with confidence, even on tables without ROWDEPENDENCIES.

If your table has ROWDEPENDENCIES and you accept that two sessions cannot update the same row at the same time no matter what, then you don’t need that extra code.