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:
- The “query API” queries data and sends it to the user.
- 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.
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.)
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:
timestamp_to_scn(systimestamp): this method is not precise and should be avoided.
V$DATABASE: this method works but advances the SCN. It also requires read access to
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER: this method works, does not advance the SCN, but requires the EXECUTE privilege on
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 :
- The query determines the “read-consistent” SCN
- Someone else commits an update (which will be invisible to the query), so new SCN.
- The query calls
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBERand gets the new SCN.
- When it comes time to check the data for “lost updates”, the change in step 2. will not be detected.
If beforehand :
- We call
- Someone else commits an update, so new SCN.
- The query runs with the new SCN and sees the update.
- 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); begin 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.
I forgot to credit and thank Jonathan Lewis for the code that gets the “start SCN” of a read-only transaction.