Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there a way to capture the order in which rows get committed in a table ?

Our application has clients that query the table based on a sequence_id column of the table, that is supposed to reflect the commit order, regularly. The largest sequence_id received in the previous attempt would be used as the filter to get the new rows.

How do we implement the logic of inserting rows with the correct sequence_id to reflect the order of commits. Using the sequence does not seems to guarantee this..

share|improve this question
1  
Are you trying to capture the order in which rows are inserted? Or the order in which rows are committed? If I insert row A at time 1, row B at time 2, commit B at time 3, and commit A at time 4, does A come before B? Or does A come after B? –  Justin Cave Jun 15 '12 at 21:35
    
I want to record the order in which rows became visible to a read operation (select query). So I believe it is the commit order I'm after. –  Ratnam Jun 16 '12 at 19:19
    
I want to capture the order in which rows became visible to a read operation (select query). This number will be used by the read operation to decide 'upto what point it received data last time' so it will ask for sequence_id > max_sequence_id recevied_last_time to get the new data. –  Ratnam Jun 16 '12 at 19:28
    
Judging by your last comment, you don't actually want the order rows were committed in. All you need is a normal sequence. What an odd way to ask the question! –  FreshPhilOfSO Jun 17 '12 at 11:06
    
Thanks Phil. How does rows become available to a select query ? I believe, it is after a row is committed, not after a row is inserted. Our tests show that order of rows become available for a read operation is different to the sequence values as the sequence.nextval is calculated at the point of insert and not at the point of commit. –  Ratnam Jun 17 '12 at 19:23
show 1 more comment

2 Answers

up vote 1 down vote accepted

It isn't possible to determine the order in which rows are committed. Either a sequence or a timestamp can allow you to determine the order in which rows are inserted. But neither will tell you the order in which those transactions were committed.

Since you have a process that is trying subscribe to changes in a table, however, you have a few options that don't require you to identify this order. You can use Oracle Change Data Capture (CDC) and then have the reading process register as a subscriber. Or you can use Streams to publish changes to the table and have the reader process act as a custom apply process.

share|improve this answer
    
Thanks Justin. But the reading process comes through http using xml as the exchange format, so we do not have the luxury of using CDC. Are there any options open to me ? –  Ratnam Jun 17 '12 at 19:17
    
@Ratnam - I'm not sure I understand. Some process needs to generate the XML in response to a HTTP request (i.e. some process is implementing the web service). That process is reading data from the database. That process should be the CDC subscriber. –  Justin Cave Jun 17 '12 at 20:01
    
Thanks Justin. Now I understand what you meant. Now, if we propagate the CDC entries to another table, how do we capture the commit order? Are there any special columns on the CDC entries that can be used to determine the comit order? –  Ratnam Jun 19 '12 at 22:39
    
@Ratnam - Both CDC and Streams will send the subscriber the data in commit order. The CDC change table will also store the commit SCN and the commit timestamp if you really want them (though I suspect you don't really care so long as the reader is sent every change in order). –  Justin Cave Jun 19 '12 at 22:52
    
Thanks Justin. We need the commit SCN to identify the order, as the reader can decide to pull data greater 'any' sequence_id when it wants. ...... It is a pull mechanism and not push. The reader decides when to gather the updates and let us know what is the maximum sequence_id on its side, when pulling for data. So the question is, can the commit SCN be dependable as representing commit order? ... meaning 'commit SCN' = 100 will not become visible to the reader before 'commit SCN' = 95 ? –  Ratnam Jun 20 '12 at 6:55
show 5 more comments

The psuedo-column ORA_ROWSCN is assigned at commit time and can be used to identify the order of commits, with the following caveats:

  • The table must be created with the ROWDEPENDENCIES clause. If this isn't done then commits will be at the block level, not the row level. You cannot alter a table to have this, you must drop and re-create it
  • Updated data will also have the ORA_ROWSCN updated on commit
  • It is approximate, from the Oracle documentation:

For each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row in the current session. This pseudocolumn is useful for determining approximately when a row was last updated. It is not absolutely precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. You can obtain a more fine-grained approximation of the SCN by creating your tables with row-level dependency tracking

To see it in effect:

16:43:49 SQL>-- session 1
16:43:53 SQL>create table t ( x integer, y timestamp default systimestamp) ROWDEPENDENCIES ;

Table created.

16:43:54 SQL>
16:43:54 SQL>insert into t (x) values (1);

1 row created.

16:43:54 SQL>

16:43:57 SQL>-- session 2
16:43:57 SQL>insert into t (x) values (2);

1 row created.

16:43:57 SQL>commit;

Commit complete.

16:43:57 SQL>

16:43:54 SQL>-- session 1
16:44:02 SQL>commit;

Commit complete.

16:44:02 SQL>select ORA_ROWSCN, t.*
16:44:02   2  from   t
16:44:02   3  order  by ora_rowscn;

          ORA_ROWSCN          X Y
-------------------- ---------- ----------------------------
   2,495,575,731,731          2 18-JUN-12 16.40.16.144909
   2,495,575,731,777          1 18-JUN-12 16.40.12.447235

16:44:02 SQL>select ORA_ROWSCN, t.*
16:44:02   2  from   t
16:44:02   3  order  by y;

          ORA_ROWSCN          X Y
-------------------- ---------- ----------------------------
   2,495,575,731,777          1 18-JUN-12 16.40.12.447235
   2,495,575,731,731          2 18-JUN-12 16.40.16.144909

16:44:03 SQL>

If you do this without ROWDEPENDENCIES they will come out with the same ORA_ROWSCN because the rows are on the same block:

16:44:03 SQL>drop table t purge;

Table dropped.

16:46:36 SQL>
16:46:36 SQL>create table t ( x integer, y timestamp default systimestamp);

Table created.

16:46:36 SQL>
16:46:36 SQL>insert into t (x) values (1);

1 row created.

16:46:36 SQL>commit;

Commit complete.

16:46:36 SQL>
16:46:36 SQL>insert into t (x) values (2);

1 row created.

16:46:36 SQL>
16:46:36 SQL>commit;

Commit complete.

16:46:36 SQL>select ORA_ROWSCN, t.*
16:46:36   2  from   t
16:46:36   3  order  by ora_rowscn;

          ORA_ROWSCN          X Y
-------------------- ---------- --------------------------------------------
   2,495,575,732,066          2 18-JUN-12 16.42.54.443898
   2,495,575,732,066          1 18-JUN-12 16.42.54.426690

Note the ORA_ROWSCNs are the same in the second example.

You can't index the ORA_ROWSCN, so filtering on this will result in full table scans. If combined with filtering on an insert timestamp you could overcome this however (which should also help prevent updated rows appearing, if that's the behaviour you want)

share|improve this answer
    
Thanks Chris. ORA_ROWSCN is unfortunately an approximate number and the logic behind the approximation is not made public. Also we definitely need an index on the column. –  Ratnam Jun 19 '12 at 22:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.