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

Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL for NOT_LOCKED?

Re: SQL for NOT_LOCKED?

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Fri, 26 Jan 2001 17:02:28 GMT
Message-ID: <94sait$ak$1@nnrp1.deja.com>

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

Original text of this message

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