5 Replies Latest reply on Nov 16, 2010 9:28 PM by alwu-Oracle

    Concurrent transaction isolation

    696067
      Hello,
      I am building a multithreaded application that uses the Semantic Jena APIs that relies on the transactions of the different threads to be isolated before a commit but I'm not quite getting this behavior. Here's a simple example (the full example source is available upon request).

      -----
      <h1>Thread 1</h1>
      Open a connection
      Get a GraphOracleSem from the connection
      call GraphOracleSem.getTransactionHandler.begin()
      Add Triple A
      Add Triple B
      Add Triple C
      call GraphOracleSem.getTransactionHandler.commit()
      Close the GraphOracleSem
      Dispose the connection

      Open a connection
      Get a GraphOracleSem from the connection
      call GraphOracleSem.getTransactionHandler.begin()
      Add Triple A
      Add Triple B
      Add Triple C
      call GraphOracleSem.getTransactionHandler.commit()
      Close the GraphOracleSem
      Dispose the connection
      -----

      -----
      <h1>Thread 2</h1>
      Open a connection
      Get a GraphOracleSem from the connection
      call GraphOracleSem.getTransactionHandler.begin()
      CheckA = true if Triple A Exists
      CheckB = true if Triple B Exists
      CheckC = true if Triple C Exists
      Throw Exception unless CheckA == CheckB == CheckC
      call GraphOracleSem.getTransactionHandler.abort() //no write is necessary here
      Close the GraphOracleSem
      Dispose the connection
      -----

      Now if the effects of the two threads were isolated from each other, CheckA and CheckB and CheckC would always be equivalent (sometimes, true, sometimes false) but this does not seem to be the case (when my code at least...). I'm not sure if this requires a Serializeable transaction isolation level to be specified but quoting the GraphOracleSem performAdd method:
      <h4>"Adds a triple into the graph. This change to this graph object will not be persisted until the transaction is committed. However, subsequent queries (using the same Oracle connection) can see this change."</h4>

      Doesn't this mean that two connections making changes to GraphOracleSem should not see each-other's changes until a commit? Or is there something I'm missing here?
      Also if this isn't the way to get something like this to work, how can it be done?

      Edited by: alexi on Nov 11, 2010 12:22 PM - Whoops, cant attach anything to this forum
        • 1. Re: Concurrent transaction isolation
          alwu-Oracle
          Hi,

          Actually commit in Thread 1 could happen in between the following checks, right? That means CheckA could return false, but checkB and C both return true (because commit happens after CheckA but before CheckB)

          CheckA = true if Triple A Exists
          CheckB = true if Triple B Exists
          CheckC = true if Triple C Exists

          Serializeable isolation level is rarely used. With the default isolation level, one session can always read committed changes made in another session.

          Why do you need to see A & B & C in an all-or-nothing manner?

          Note that the behavior you observed is not specific to Oracle Database semantic technologies.

          Thanks,

          Zhe Wu
          • 2. Re: Concurrent transaction isolation
            696067
            Right before the statement "CheckA = true if Triple A Exists" I call "graph.begin()" which I assumed would have the effect of isolating all changes (that happen to the graph from other Oracle connections). I need Serialization of transactions or the equivalent since I have multiple threads writing to the model that could potentially step on each-other's toes cause the model to become invalid.

            Also, I tried doing a oracle.getConnection().setTransactionIsolationLevel(TRANSACTION_SERIALIZABLE) but I keep getting the following error:

            -----
            com.hp.hpl.jena.shared.JenaException: java.sql.BatchUpdateException: ORA-55303: SDO_RDF_TRIPLE_S constructor failed: Simple case: SQLERRM=ORA-06519: active autonomous transaction detected and rolled back
            ORA-06512: at "MDSYS.MD", line 1723
            ORA-06512: at "MDSYS.MDERR", line 17
            ORA-06512: at "MDSYS.SDO_RDF_TRIPLE_S", line 211
            • 3. Re: Concurrent transaction isolation
              alwu-Oracle
              Hi,

              I am afraid you cannot use it this way.

              See this example using SQL inserts directly. Assume there are two concurrent sessions.

              Session 1:

              SQL> set transaction isolation level serializable;

              Transaction set.

              SQL> insert into basic_tpl values(sdo_rdf_triple_s('basic','<urn:a>','<urn:b>','<urn:c_123>'));

              1 row created.


              Session 2:

              SQL> set transaction isolation level serializable;

              Transaction set.

              SQL> insert into basic_tpl values(sdo_rdf_triple_s('basic','<urn:a>','<urn:b>','<urn:c_567>'));
              insert into basic_tpl values(sdo_rdf_triple_s('basic','<urn:a>','<urn:b>','<urn:c_567>'))
              *
              ERROR at line 1:
              ORA-08177: can't serialize access for this transaction
              ORA-06512: at "MDSYS.SDO_RDF_INTERNAL", line 7538
              ORA-06512: at "MDSYS.BASIC_INS", line 37
              ORA-04088: error during execution of trigger 'MDSYS.BASIC_INS'

              SQL> rollback;
              Rollback complete.

              SQL> insert into basic_tpl values(sdo_rdf_triple_s('basic','<urn:a>','<urn:b>','<urn:c_567>'));
              insert into basic_tpl values(sdo_rdf_triple_s('basic','<urn:a>','<urn:b>','<urn:c_567>'))
              *
              ERROR at line 1:
              ORA-55303: SDO_RDF_TRIPLE_S constructor failed: BNode-non-reuse case:
              SQLERRM=ORA-06519: active autonomous transaction detected and rolled back
              ORA-06512: at "MDSYS.MD", line 1723
              ORA-06512: at "MDSYS.MDERR", line 17
              ORA-06512: at "MDSYS.SDO_RDF_TRIPLE_S", line 64

              If you want application level serialization, you can use dbms_lock package to acquire a lock before
              performing updates. Another simple way is to create a simple table with one row and do a "select * from tabName for update." You can add a "nowait" if you don't want your session to be blocked.

              Hope it helps,

              Zhe Wu
              • 4. Re: Concurrent transaction isolation
                696067
                Hum, if we have one server (writing to the DB) we can make in-memory locks but if there are multiple servers... does this means we have to lock on the database?
                • 5. Re: Concurrent transaction isolation
                  alwu-Oracle
                  Hi,

                  If you have multiple JVMs and multiple computers for the mid-tier, then acquiring a lock from the database is a very reliable way to serialize your application flow. The "select * ... for update" is simple to use. It has been used heavily by some other projects I was involved with.

                  Thanks,

                  Zhe Wu