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.


One thought on “Optimistic Locking 5: “Read-consistent” SCN

Leave a Reply

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

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s