Isolation level/concurrency problem

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 - 11:57:09 CST