Re: (Java) Synchronizing multiple app servers through Oracle
Date: 21 Jan 2008 15:50:02 -0600
Message-ID: <uodbepsv7.fsf@rcn.com>
On Mon, 21 Jan 2008, chrism778_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.
I would just select the records you want "for update". Then, each app server selects all the records it wants to process "for update". I'd also look at nowait.
You may want to be a bit more "optimistic" and add an extra column to your database table that is acts as a "timestamp". Select it along with the other columns. Then, have the app server process the rows, then send back an "update table set blah = bleh where id = id AND timestamp = [timestamp returned on initial select]". Then, if 2 apps servers happen to work on the same row, only one of the appserver's completed work is stored. Yes, multiple app servers may unknowingly work on the same row but maybe that doesn't happen too much? See what I mean by optimistic? Your expectation would be that different app servers would usually choose different rows. Is this the case?
I would populate that extra column with a sequence number and not a timestamp.
> After I pick the records, I move them to another table, then I release
> the lock with dbms_lock().
You shouldn't need to do that, especially in the optimistic scenario.
> If there is a better way to achieve the same thing through the
> database, I'd love to hear it.
Not sure, you aren't describing the process all that deeply.
-- Galen BoyerReceived on Mon Jan 21 2008 - 15:50:02 CST