Re: (Java) Synchronizing multiple app servers through Oracle

On Jan 21, 1:51 pm, chrism..._at_gmail.com wrote:
> On Dec 22 2007, 6:36 am, Galen Boyer <galen_bo..._at_yahoo.com> wrote:
>
>
>
> > On Fri, 14 Dec 2007, shortcut..._at_googlemail.com wrote:
> > > On Dec 13, 11:13 pm, chrism..._at_gmail.com wrote:
> > >> On Dec 13, 4:07 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>
> > >> > On Dec 13, 2:10 am, chrism..._at_gmail.com wrote:
>
> > >> > > What is the recommended way for using an Oracle database to
> > >> > > synchronize multiple app servers? When I speak of synchronizing,
> > >> > > I'm referring to the Java use of the word, where you can lock a
> > >> > > section of code so other threads don't access that section at the
> > >> > > same time.
>
> > You do that because the code has values that can change for the entire
> > JVM's instantiated objects, correct? Why do you think you need to do
> > that with the database?
>
> > --
> > Galen Boyer
>
> OK. What I'm trying to do is have N number of Java-based app servers
> use the database as a way of synchronizing between each other.
> Perhaps the more typical way of doing this is opening up sockets on
> each app server and broadcasting information between all the servers
> to the let them all know what everyone else is doing. I was hoping I
> could more easily accomplish the same thing using the built-in
> functionality of the database, because all the app servers need to
> access the database anyway.
>
> Here's what I'm doing:
> I have a table which contains records to process. Each app server can
> process these records. However, it is important that no 2 (or more)
> app servers attempt to process the same records. So what I'm doing is
> making a call to dbms_lock() to ensure that when I'm picking out
> records to process, no other app server is doing the same thing.
> After I pick the records, I move them to another table, then I release
> the lock with dbms_lock().
>
> If there is a better way to achieve the same thing through the
> database, I'd love to hear it.
>
> Thanks.

Out of curiosity, why can no other session process the same rows? If you really need to do this, search the doco (tahiti.oracle.com) for SELECT FOR UPDATE. That will effectively lock an entire chunk of rows in the update, causing other session to immediately (or close to it) throw an exception when they try to to SELECT FOR UPDATE NOWAIT the same rows (or even one in the bunch). You could handle the exception thrown however you like, i.e, get the next set, etc. I'm not sure that would scale well, however, as SELECT FOR UPDATE has to fetch all rows prior to processing the first one.

Any more input on what you are doing?

Regards,

Steve Received on Mon Jan 21 2008 - 14:28:47 CST