Re: row locking and inserts

On Mar 25, 9:40 am, "pp" <pedro.e.pi..._at_gmail.com> wrote:
> Allright, I apologize for my lack of Oracle expertise. I will try to
> explain my motivations a little better.
>
> I have multiple processes accessing my oracle database. Each of these
> processes is, at any given time, only interested in a subset of the
> rows in my table, the subset for which field1 has a specific value.
> Based on rows found in this subset (which can be empty), my process
> will make different decisions. When the subset is empty, a new row
> should be added, when it is not, all the rows in the subset need to be
> analyzed. This analysis has to be done through Java code. Depending on
> the result of the analysis, a new row can be added to the table (a row
> which would be logically part of the subset), a row in the subset can
> be deleted, or no action taken.
>
> For correctness, my application requires that access to this subset be
> serialized.
>
> Does that make more sense?
>
> -pp

Makes a kind of sense.

Like Sybrand says, if it's serialization you want, use DBMS_LOCK to create a non-table lock. That way you can control inter-session serialization without blotto-ing the data.

On initial read, it doesn't seem like a 'proper' design .. specifically in terms of RDBMS principals. If true, this kind of design often ends up using Oracle as an expensive data dump. I'd seriously encourage re-evaluating in context of the database (Oracle).

<rant>
It has the secondary impact of developer job security - or at least should ... developer's who create these kinds of things should be forced to support them, not simply be allowed to turn them over to operations. <g>
</rant>

/Hans Received on Sun Mar 25 2007 - 16:19:33 CDT