Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Design question

RE: Design question

From: Carel-Jan Engel <cjpengel.dbalert_at_xs4all.nl>
Date: Fri, 22 Oct 2004 14:38:42 +0200 (CEST)
Message-ID: <11354.213.34.28.241.1098448722.squirrel@webmail.xs4all.nl>


Do DBMS_LOCK-locks survive instance restarts? Because of the asynchronous type of 'transaction' that needs to be covered. There is always the possibility of restarting the instance between sending and receiving the instance, if not, Murphy will invent one.

So, the locks need to be persistent, and I think that the only way to achieve this is locking the rows involved in some table, either with a column in the table itself or in a dedicated locktable.

Think of restore/recovery issues as well. What if the 'other' database gets restored, and loses the data passed to it from the Oracle database? Can it be recovered up to and including the last transaction? Does the 'last transaction' wait for completion until Oracle received the associated XML message? Probably not, so it will not resend it during recovery. What if an XML-messages is lost along the lines? Are procedures to overrule locks in that case?

This whole thing is quite error-prone. Once you have covered _all_ pitfalls you might have created some job-safety!

Regards, Carel-Jan

===
If you think education is expensive, try ignorance. (Derek Bok) ===

> Chris,
> did you look at the DBMS_LOCK package? that might provide the
> functionality
> you need ...
>
> Kind regards,
> Lex.
>
> -------------------------------
> visit http://www.naturaljoin.nl
> -------------------------------
> skype me <callto://lexdehaan>
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Stephens, Chris
> Sent: Friday, October 22, 2004 13:09
> To: oracle-l_at_freelists.org
> Subject: Design question
>
>
> =20
>
> I've just been asked to attend a meeting this morning to resolve the
> following issue:
>
> Data resides in Oracle.
>
> .NET application pulls relevant data out into an xml file, brings it
> back to the client which has a sql-server database locally. The data
> may or may not be updated. If it is, the updates are sent back to
> Oracle.
>
> Meanwhile there is a complete separate application (not sure what it is
> implemented in) that needs to be prevented from modifying the data which
> the .NET application users are currently using. ....the question is how
> exactly to do this.
>
> ....my first impression was to somehow make use of the 'select..for
> update' and push pl/sql processing but since the data is shipped off via
> an xml file and the system is already developed, I don't think that's
> possible. ...the only other option that I can think of (and the one
> they were already thinking of) is to have a column that indicates
> whether a rows is locked by some .net user...assuming the data comes
> from a single table. If the data resides in multiple tables I think
> that best option is to create a 'data_lock' table that holds the keys
> from each individual table for the lifetime of the 'transaction'.
>
> Any better/additional ideas?
>
> Not exactly a database centric environment here. :)
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 22 2004 - 07:34:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US