[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Atomic Transaction Help!!

[lemboy4]

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

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4074256#4074256

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4074256