5 Replies Latest reply on May 14, 2014 4:32 PM by Rinne

    transaction ordering -Streams?

    Rinne

      I have multiple nodes in a RAC (Oracle 11.2) where I need to preserve transaction ordering for DML transactions. I can have inserts, updates, deletes. I need to know the order of the DML transactions.

       

      I've found this link which suggests Streams. But it looks like Streams will not be enhanced anymore. We also have Golden Gate. Can Golden Gate be used for transaction ordering? Can somebody point me in the right direction? If not Golden gate, are there other solutions?

      https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2685071500346592076

       

      Thanks!

        • 1. Re: transaction ordering -Streams?
          rp0428
          I have multiple nodes in a RAC (Oracle 11.2) where I need to preserve transaction ordering for DML transactions. I can have inserts, updates, deletes. I need to know the order of the DML transactions.

           

          I've found this link which suggests Streams. But it looks like Streams will not be enhanced anymore. We also have Golden Gate. Can Golden Gate be used for transaction ordering? Can somebody point me in the right direction? If not Golden gate, are there other solutions?

          https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2685071500346592076

          Your statements are confusing. You say 'preserve transaction ordering' but then say 'order of the DML transactions' and provide a link that is NOT talking about transaction ordering.

           

          Transactions are already ordered - by the SCN of the commit. A transaction hasn't happened (as far as other sessions/users) until the COMMIT occurs.

           

          Are you having the same issue that link discusses?

           

          Are you also having the out-of-sync timestamp issue that link discusses?

           

          Provide a simple example (as they do in that link) of what problem YOU are experiencing.

          • 2. Re: transaction ordering -Streams?
            Rinne

            I have a history table of changes that occur to multiple transaction tables. I have a DML_FLAG column in the history table which indicates update, insert, or delete of the row. At the time of commit, the rows are inserted into the history table from the multiple transaction tables. I would need another column in the history table which would need to tell me that this transaction occurred earlier or later than other transactions getting inserted into this history table.

            • 3. Re: transaction ordering -Streams?
              Mike Kutz

              If rp042 is right in that what you need is the SCN...

               

              You may want to look into Flashback Data Archive and the 'versions between' SQL clause.

              (fyi - FDA is included with 11.2.0.4/12c and higher but part of the Adv. Comp. add-on for 11.2.0.x)

               

              The VERSIONS BETWEEN clause provides the pseudo columns VERSIONS_STARTSCN (what rp042 says you need) and VERSIONS_OPERATION (your DML flag)

              It might be possible that a simple ALTER TABLE ... FLASHBACK ARCHIVE ... will magically do what your "history table" is doing right now.

               

              12c doc (because I have that one bookmarked..)

              Using Oracle Flashback Technology

               

              MK

              • 4. Re: transaction ordering -Streams?
                rp0428
                I have a history table of changes that occur to multiple transaction tables. I have a DML_FLAG column in the history table which indicates update, insert, or delete of the row. At the time of commit, the rows are inserted into the history table from the multiple transaction tables. I would need another column in the history table which would need to tell me that this transaction occurred earlier or later than other transactions getting inserted into this history table.

                And NONE of that answers either of the two questions I ask or provides the example I ask you to post:

                Are you having the same issue that link discusses?

                 

                Are you also having the out-of-sync timestamp issue that link discusses?

                 

                Provide a simple example (as they do in that link) of what problem YOU are experiencing.

                Without an example of what YOUR timing is I don't know when you consider an action to have occured:

                At the time of commit, the rows are inserted into the history table from the multiple transaction tables

                Then you either need to capture that point in time yourself (e.g. in PL/SQL code by logging an entry in your log table when you issue a COMMIT) or use SCN which captures it.

                 

                The fundamental requirement you have to provide is YOUR definition 'occurs' as in 'this transaction occured'. What does it mean to YOU for a 'transaction' to occur 'earlier or later' than another transaction.

                 

                You can NOT capture that info within a statement that is part of a transaction because that statement will have NO knowledge of when, or if, a commit ever occurs. So you can NOT use SYSDATE or a sequence number generated as part of a statement.

                • 5. Re: transaction ordering -Streams?
                  Rinne

                  I am having the same out of sync timestamp issue.

                   

                  My problem:

                  I have two types of transactions:

                       transaction 1: insert and update table A, table B, and table C. Then inserts one row to the history table describing the transaction and issue one commit at the end.

                       transaction 2: insert and update table A and table D. Then inserts one row to the history table describing the transaction and issue one commit at the end.

                   

                  Because I am in a RAC and have the same timestamp issue, I need a way to order the transactions as per the commit. I want to tell which commit happened first between the two transactions above. I hope this is more clear.