Re: Isolation level/concurrency problem

Hi Wayne. There is no way in Oracle to see uncommitted data. Oracle uses optimistic locking, whereby all real data that is either read or altered, is first copied to a user-private space, and only during commit is the real data locked and the transaction isolation requirements checked. For the duration of the transaction, you will be working on copied data. The real data may well change out from under you, and you will only find out at commit time, with a 'failure to serialize' message.

   You will have to try a multi-transaction model, where you lock the row by setting it to being processed where it is waiting (commit), then do the processing, and also set it to complete when that's true.   Do NOT use any transaction isolation level other than READ_COMMITTED with Oracle. There is a SERIALIZABLE mode, but is is dangerously broken, as I've recounted in another thread recently. Proof on request.   There may be a way to select the row(s) you want in such a way as to preemptively lock them against any other users, but I don't know it. I am not really fluent with Oracle SQL, so I too will await any expert's response.

Joe Weinstein at BEA, the home of WebLogic

Wayne Menzie wrote:

> 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

--

PS: Folks: BEA WebLogic is in S.F., and now has some entry-level positions for people who want to work with Java and E-Commerce infrastructure products. Send resumes to joe_at_beasys.com

                    The Weblogic Application Server from BEA
         JavaWorld Editor's Choice Award: Best Web Application Server
  Java Developer's Journal Editor's Choice Award: Best Web Application Server
     Crossroads A-List Award: Rapid Application Development Tools for Java
Intelligent Enterprise RealWare: Best Application Using a Component Architecture
               http://weblogic.beasys.com/press/awards/index.htm