Re: Table locking

Hi Matt,

> B) An alternate approach might be to pre-create a limited number of locks
> and hash each row as it is processed into one of these locks. This would
> limit the overhead cost of allocating user locks and would make the odds
of
> collision be about one in the number of pre-allocated locks, say 1 in 100.
> There is the danger that if process A grabbed row 97 and B grabbed row
15121
> and both hash to lock X44 that row 15121 would not be processed because B
> would find the lock X44 in use by A and would skip the row. When process
A
> gets to row 15121 it will process it unless process C or D happens to be
> processing a row that also hashes to X44 at that time (assuming that one
of
> these processes has not already processed it.) A row that is skipped on
one
> pass may be acceptable if the processes are already running after making a
> complete pass through the cursor close it and re-execute it to find newly
> created data. If after one run all the processes terminate you could
> potentially run only one processes again to find any missed rows, but this
> would require that the initial cursor be pretty quick which is probably
not
> the case.

Many thanks for your great help! This is the solution for my problem.

But during the implementation, I ran into another problem:

Is it possible to execute the DBMS_LOCK functions from within a simple embedded SQL statement or do I have to switch to PL/SQL blocks? (Oracle reports an error that the
functions can not guarantee not to alter the database, which is obviously correct).

I understand that stored functions can only be executed from within E-SQL statements when they do not write to the database.

Is that correct?

Many thanks again!!! Stefan

===
Stefan Meier, Danet GmbH
Gutenbergstr.10, D-64331 Weiterstadt
E-Mail: Stefan.Meier_at_danet.de Received on Tue Jun 02 1998 - 09:19:30 CDT