Re: Accessing data before commit

Ritvij a écrit :

> My application has messaging interface with Stock Exchange for trade
> management.
> Consider my scenario:
> I send a message to other application X with Reference 'AAA' (Msg A).
> X can send two responses (Msg B : confirmation for Msg A and Msg C:
> Processing completion of Msg A), both with same reference 'AAA'.
>
> I need to process (always) Msg B and then Msg C for reference 'AAA'
>
> Consider scenario 1:
> X sends Msg B and then Msg C. I process Msg B and then Msg C. No
> problem!!
> Scenario 2: X sends first Msg C , then Msg B.
> I check Msg C, check that for reference 'AAA' no Msg B is received. I
> park the message C in a pending_process table.
> Then I process Msg B, process it, I check in pending_process table , if
> any Msg C is pending processing for 'AAA'. I process it. Again, no
> problem
>
> Scenario 3:
> X sends both B and C together. I use Tuxedo as transaction manager.
> Both B and C start getting processed in parallel by two different
> tuxedo processes.
> Msg C is processed and since Msg B is still not completed processing, I
> again park in pending_process. Howevever, the comit is still not done.
> While processing B, I need to check if any Msg C is pending, but since
> Msg C is not committed, I do not see.
> I do not process Msg C .,, Big Problem !!!!
> What do I do??

As sybrandb hinted, you need to serialize the transactions processing B and C for a given reference 'AAA'.

Apparently, you have a table with a row referenced by 'AAA', updated when B is processed....

If correct, then transactions processing B and C should be serialized if
each performs - as the first statement - a SELECT FOR UPDATE of the row referenced by 'AAA'

Note that I dont know a thing about Tuxedo. Also, locking a row while processing C may be unacceptable for you... Then, a variation on the idea or queues may be solutions.

>
>
>
> Scenario
>
> On Dec 19, 11:53 am, "sybrandb" <sybra..._at_gmail.com> wrote:
> > Ritvij wrote:
> > > is it anyhow possible to access data in a table which is not committed?
> >
> > > we have two messages A, B coming from an application at the same
> > > time.
> > > hence 2 different processes are triggered in our application to process
> > > the two messages A and B parallely.
> >
> > > Process B processes the data and inserts into table X, but still
> > > transaction is not complete and hence no comit.
> > > Process A running parallely meanwhile needs to check whether B has
> > > inserted data in table X to decide its processing.
> >
> > > Is it possible?Yes, by creating message communication between the two processes using
> > Advanced Queuing. Process A queues a message after insert, which
> > Process B dequeues.
> > By reading the table: NO.
> > Obviously one would wonder why you want to parallelize an essentially
> > serialised algorithm. Usually this kind of approach ends in hell.
> >
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
Received on Tue Dec 19 2006 - 05:56:24 CST