0 Replies Latest reply on Aug 14, 2007 11:21 PM by Chris Pesto

    Atomic Transaction Help!!

    Chris Pesto Newbie

      Hi, this question is a bit involved; though all I can offer is my eternal gratitude to somebody who helps me avoid wasting even more hours on understanding this...

      I am avoiding using Entity beans for now, and created a set of methods that directly call SQL, something like....

      class DBAccess {
      method1(parameters for SQL) {
      open connection();
      execute a statement with these parameters...
      close connection();
      }
      ...
      }

      Now, I have a message driven bean listening for JMS, and when it receives a message it calls a method in DBAccess that looks like:

      method {
      check_if_object_exists_in_DB();
      if(doesnt_exist) {
      create_object_in_DB();
      do_other_stuff_involving_new_object_in_DB();
      }
      }

      Now, JBoss won't treat this method as atomic no matter what...I get duplicates in the DB in a way that looks certainly like multiple threads are seeing doesnt_exist and creating.

      I've tried many things and rejected them:
      -- Consolidate into one statement? Too complicated, has to be done in multiple queries
      -- Locking database table? This just pushes the problem a layer lower, and ruins my whole nice set of SQL methods (used in other places) that just do one thing each, since I believe they could no longer open and close connections individually and would have to all be put together to operate under one connection with the lock
      -- Synchronizing around this method....supposedly very much not allowed in JBoss, and besides the method items then execute in the correct order but STILL I end up with duplicates

      If I were using Entity beans I think the thing to do here would be open a "transaction" at the start of the big method that calls all the others... Am I just going about this all wrong? I am new to JBoss, is there some brilliant way to structure methods that just execute SQL so this problem never comes up??

      Thanks for any help provided!!!!

      -Chris