Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Chris.

Asked: September 28, 2018 - 8:14 am UTC

Last updated: October 31, 2018 - 1:54 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi,

I'm trying to work out how to assign an ordering to the results of potentially concurrent serializable transactions so I can definitively say either:
- They both had an identical view of the database, or
- Identify which one had a later view.

The angle I'm taking is to try to find the SCN corresponding to the snapshot used for the transaction. I.e., the SCN whereby every change with SCN <= current SCN is visible and every change with SCN > current SCN is not visible. I have tried using v$database.current_scn and dbms_flashback.get_system_change_number, but these can both change mid-transaction so wouldn't seem to correspond to the transaction snapshot.

SQL> set transaction isolation level serializable;

Transaction set.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 1888249390

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 1888249392

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
              1888249393

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
              1888249394


Is there any way to reliably access the current transaction snapshot SCN, or have you got any other suggestions for how I might accurately order my transactions?

Thanks.

and Connor said...

You could perhaps look at using transaction ID's to get the SCN information you want? eg

SQL> create table t(
  2    x int,
  3    xid varchar2(20),
  4    xidr raw(8),
  5    d date
  6  );

Table created.

SQL>
SQL>
SQL> declare
  2    l_xid varchar2(20);
  3    l_xidr raw(8);
  4  begin
  5    l_xid := dbms_transaction.local_transaction_id( true );
  6    select t.xid
  7    into   l_xidr
  8    from   v$transaction t,
  9           v$session s
 10    where  s.taddr = t.addr
 11    and    s.sid = sys_context('USERENV','SID');
 12
 13    insert into t values (10,l_xid,l_xidr,sysdate);
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

         X XID                  XIDR             D
---------- -------------------- ---------------- -------------------
        10 4.18.12877           040012004D320000 01/10/2018 09:39:40

--
-- waited for a few seconds
--
SQL> commit;

Commit complete.

SQL> select *
  2  from flashback_transaction_query
  3  where xid = hextoraw('040012004D320000')
  4  @pr
==============================
XID                           : 040012004D320000
START_SCN                     : 19846005
START_TIMESTAMP               : 01/10/2018 09:39:38
COMMIT_SCN                    : 19846015
COMMIT_TIMESTAMP              : 01/10/2018 09:39:47
LOGON_USER                    : SCOTT
UNDO_CHANGE#                  : 1
OPERATION                     : UNKNOWN
TABLE_NAME                    : T
TABLE_OWNER                   :
ROW_ID                        :
UNDO_SQL                      :


but it strikes me as an odd requirement

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Chris Spencer, October 01, 2018 - 9:15 am UTC

Hi Connor,

Thanks for your answer. As a bit of background, the problem is basically that there are two independent processes going on. One periodically polls the database and broadcasts any changes it finds. The other generates data snapshots on demand in order to provide an initial view, after which it can start listening to the stream of updates. Due to the asynchronous nature of these operations, there is a small chance of the results being delivered to the client out of order. Without attaching some kind of version number to the data I've got no way of saying which one is more up-to-date.

My initial thought was to take the maximum value of ora_rowscn for the set of rows I'm interested in as the version number, but it's possible for rows to be deleted from that set, so that max value would either not change or go backwards in that case.

Regarding your answer, while working through it I spotted the start_scnb and start_scnw columns in v$transaction. In my test this gave the same number as flashback_transaction_query.start_scn. Is there any reason I should use the value from flashback_transaction_query instead of v$transaction? It seems preferable to me to use v$transaction because I can query it from within the transaction itself.

As far as I can tell, the transaction start SCN is not exactly correlated to the serializable snapshot. Take the following example with two interleaved sessions.

Session 1 - Start the transaction. This is the point at which the 'snapshot' is generated.
SQL> set transaction isolation level serializable;

Transaction set.


Session 2 - Update the row, commit and exit.
SQL> set transaction isolation level serializable;

Transaction set.

SQL> update foo set value = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select value, ora_rowscn from foo;

     VALUE ORA_ROWSCN
---------- ----------
         1 1896777079

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

The row SCN of this update is 1896777079, so the snapshot SCN in session 1 must be less than this value.

Session 1 - Select from dbms_transaction to force it to create an entry in v$transaction.
SQL> select dbms_transaction.local_transaction_id from dual;

LOCAL_TRANSACTION_ID
--------------------------------------------------------------------------------
4.0.570818


Session 1 - Select from the test table. This is still showing the original value, unaffected by the change in session 2.
SQL> select value, ora_rowscn from foo;

     VALUE ORA_ROWSCN
---------- ----------
         0 1896776073


Session 1 - Select from v$transaction. The start SCN here is 1896777463. This is greater than the ora_rowscn we saw from session 2, so this cannot be the snapshot SCN because we have already seen that the change from session 2 is not visible.
SQL> select start_scnb, start_scnw from v$transaction;

START_SCNB START_SCNW
---------- ----------
1896777463          0


Note I also tried this using flashback_transaction_query and saw the same behaviour.


I guess this approach is probably not viable. I think I'm going to need to explicitly version data within the database. E.g., attach a trigger to the table of interest and every time something changes, increment a version number stored in a separate table.
Connor McDonald
October 31, 2018 - 1:54 am UTC

I think I'm going to need to explicitly version data within the database.

Agreed.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library