Re: (Java) Synchronizing multiple app servers through Oracle
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 21 Jan 2008 22:36:38 +0100
Message-ID: <5vkhj8F1mq8qqU1@mid.individual.net>
>> 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.
Date: Mon, 21 Jan 2008 22:36:38 +0100
Message-ID: <5vkhj8F1mq8qqU1@mid.individual.net>
On 21.01.2008 21:28, Steve Howard wrote:
> 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.
I was also suggesting SELECT FOR UPDATE. This is much more scalable than a single DB lock.
But for work distribution queues are probably an even better solution. http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_aq.htm#CBAFIAGC
And when in a JEE app server there are even more mechanisms for clustering.
Cheers
robert Received on Mon Jan 21 2008 - 15:36:38 CST