Re: Table locking

I would like a little more clarification of your problem. Specifically, when are you committing? If process A inserts a new row, process B will not see it until process A commits. If you are updating existing rows then process B may retrieve the unchanged row from rollback and not see your change.

It appears that if A grabs a row you want to lock B out. You could have all processes use 'select for update' which would make processes wait till the holding process finished before fetching the row, but this creates a bottleneck if each process does not absolutely have to process the row. If you would like B to continue processing skipping the row A has then you could have all processes access the table through a stored procedure that uses dbms_lock to allocate a user lock on the row in question. Since all access is through the procedure then when the row is locked you could have the row not meet the query conditions or if you are using cursors you could run a spin loop or take a snooze before trying again for the row.

I hope this gives you some ideas.

Stefan Meier <Stefan.Meier_at_danet.de> wrote in article ...
> Hi folks,
>
> I've got a real tough problem :)
>
> - Serveral processes access the same database
> - Each process reads a database entry (and processes it's information)
> - After being read by a process, the entry shall be locked so that other
> processes can not access it (even for read!) until the process
> finished it's processing.
>
> Is this possible??
>
> Thx for your help!!
>
> Stefan
>
>
> --
Received on Wed May 27 1998 - 10:55:28 CDT