Re: Isolation level/concurrency problem

There are various strategies, but you might look at used-defined locks (dbms_lock package).

Don't select the row for update, but open a select cursor on the set of rows. Then for each row generate a lock id, and try to select that lock for update with no wait. If you get it, no-one has locked the row; if you can't get it someone has locked that row, so you move on to the next one.

Use the option to release lock on commit or termination of session.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Wayne Menzie wrote in message
<8EDA72C85wayneshammalammading_at_216.64.31.80>...
>I hope somebody can help me figure out the best way to approach this
>problem.
>
>One field of a table marks the status of that record:
> 1 = Waiting for processing
> 2 = Being processed
> 3 = Processing complete
>
>The client application checks this field and reports back the records
>waiting for processing (i.e. status = 1). The oldest test where status = 1
>is selected for processing and the status is changed to 2. The processing
>is completed and the status is changed to 3 and the entire transaction is
>committed.
>
>The problem: while the test is being processed and the status = 2, it
>hasn't been committed so the other client applications still see it as
>status = 1 and will try to select it for processing.
>
>I would like the stage where status = 2 to be visible to other sessions but
>I still want the benefit of a transaction that I can rollback in case of a
>catastrophe. The workaround with MS SQLServer 7 was to set the isolation
>level to Read Uncommitted. I realize that this is crude and flies in the
>face of good database practices but it worked. Now, migrating to Oracle, I
>need to find a proper way to accomplish this task.
>
>I realize this is somewhat complicated but I'd appreciate any help you can
>give me. Thanks.
>
>
>Wayne Menzie
Received on Mon Feb 14 2000 - 13:23:12 CST