Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL for NOT_LOCKED?
In article <94q1pb$2gi$1_at_nnrp1.deja.com>,
mikedba_at_my-deja.com wrote:
> Thanks Mark,
>
> This is exactly the kind of work-around I was hoping to hear about if
> there isn't something like a NOT_LOCKED pseudocolumn I can query on.
>
> Since the result-set from the query will start out returning hundreds
> of thousands of records, I'm assuming I don't even want a cursor to be
> that size. If I know I've got no more than ten users, then I could
> always just limit the query/cursor to a low rowcount because there can
> only be ten locked records in existence at any one time. If I had a
> rowcount < 20, then there would always be a record available for
> editing in the result set.
>
> Is this flawed logic?
>
> > in pl/sql I would open a cursor on all rows that meet the
> > query criteria and then loop through the result set.
> >
> > I would perform a select for update with the nowait option on each
row
> > from the driving cursor. If I get the row I update it and advance
to
> > the next row. On error (locked condition with immediate return from
> > nowait) I just advance to the next row.
> >
> > If coding like this in Delphi is not an option then perhaps you
could
> > write a database stored procedure (or package) and call the package
> > from your application.
>
Your ability to limit the cursor really depends on how the records will
be processed, and if when they are updated that they drop out of the
driving cursor result set so that if the cursor is re-opened the
updated records will not be picked up again.
Another problem you may encounter has to do with all the users targeting the same Oracle blocks as they all start at the beginning of the cursor. This can result in the creation of a lot of transaction work areas in the blocks or waits to get one, and with a large number of updates also lead to 'snapshot too old' errors since the logic will have a tendency to group the users.
You can pre-allocate a couple of transaction work areas in each table and index block by using the initrans parameter, but if the snapshot too old occurs you may need to distribute your users to different parts of the table.
I should mention that the select for update should also check that the row being selected still meets the driving query criteria. That is, if the driving table selects all rows where a specific column is null, you would want to test that the column is still null when you select it for update and skip it if it has already been updated (but is no longer locked since a commit was issued). You do not want to reprocess what has already been done.
-- Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice -- Sent via Deja.com http://www.deja.com/Received on Fri Jan 26 2001 - 11:02:28 CST