Re: keep select in transaction

DA Morgan wrote:
> Rauf Sarwar wrote:
> > Rene Nyffenegger wrote:
> >
> > My understanding of the word "Transaction" in Daniel's post was a
> > single DML which involves either select, insert, update or delete.
> > You on the other hand read "Transaction" as a single PLSQL call.
Two
> > completely different things as you may open/close/commit/rollback
one
> > to many DML's in a single PLSQL call.
> >
> > That is the confusion as I see it.
> >
> > Regards
> > /Rauf
>
> Based on my understanding of your understanding of my understanding,
> etc. you are wrong on all counts.
>
> if a single DML statement, or PL/SQL call doesn't end with either
> COMMIT or ROLLBACK the statement has not ended. What happens next
> is still part of the same transaction.
>
> BEGIN
> INSERT INTO t (pid) VALUES (1);
> COMMIT; -- one transaction ... one statement
>
> INSERT INTO t (pid) VALUES (1);
> INSERT INTO t (pid) VALUES (1);
> ROLLBACK; -- one transaction ... two statements
>
> INSERT INTO t (pid) VALUES (1);
> UPDATE t SET pid = 2 WHERE rownum = 1;
> DELETE FROM t WHERE pid <> 2;
> COMMIT; --one transaction ... three statements
> END;
> /
>
> But then maybe you are referring to another Daniel and I am
> totally confused about the entire thread.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)

1. I'll answer the last part first. You don't see any other Daniel in this thread (atleast not used as a signature or post header) so you should not be confused as to who I was referring to.
2. It always help to read the thread... or atleast the relevant posts to what you are referring to before replying.
3. My first post in this thread was in reply to Rene's post where he/she tried to prove your 2nd statement in the previous post incorrect i.e. "By default it is impossible, in Oracle, to see records committed after your transaction has begun." He was using two different sessions to prove the point but he was using two different cursors to prove it... which in my judgment was incorrect. You can refer to my two previous posts.
4. Now let's look at your original post that all started it. "By default it is impossible, in Oracle, to see uncommitted data." "By default it is impossible, in Oracle, to see records committed after

your transaction has begun."

When you refer to committed or uncommitted data... you have to mean two different sessions because you can view your data within the same session without actually committing it (until rolled back within the same session), BUT no other session can see it. Other sessions can ONLY see your committed data.

Now, if we see both your statements within context of a single session then they are both wrong because you can see your own committed or uncommitted data. Your statements ONLY make sense if they are looked at from two different sessions. Therefore your example above is irrelevant as you are doing all the DML's within the same session.

A PLSQL call can invlove one to many DML's. If you meant transaction as one PLSQL call that may involve a trip around the world with 100's of DML's... then your statements in question are correct in some instances and incorrect in other. e.g. you open a cursor in the beginning of your plsql call and see record 'A' in table1. Let's say another session inserted a record 'B' into table1 and committed it while your plsql call is still hot. Until you close your cursor and reopen it... record 'B' will not be visible to you... subsequently if the other session did not commit record 'B'... it will never be visible to you until it is committed. On the other hand... you could keep inserting records into any table during your plsql call... they will be visible to you all the time without actually committing them (unless you do a rollback) however, until you issue a commit, none of it is visible to any other session.

So it all boils down to... did you mean transaction as a single plsql call or did you mean transaction as a single DML?

Hope that makes it clear.

Regards
/Rauf Received on Fri Apr 15 2005 - 12:31:24 CDT