Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: keep select in transaction

Re: keep select in transaction

From: DA Morgan <>
Date: Fri, 15 Apr 2005 16:07:30 -0700
Message-ID: <1113606227.630814@yasure>

Rauf Sarwar wrote:

> 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.
>>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.
>>   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
>>But then maybe you are referring to another Daniel and I am
>>totally confused about the entire thread.
>>Daniel A. Morgan
>>University of Washington
>>(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

I think the horse is likely deceased but to follow up:



   OPEN acur;
   OPEN bcur;

I suspect there is no disagreement.

Daniel A. Morgan
University of Washington
(replace 'x' with 'u' to respond)
Received on Fri Apr 15 2005 - 18:07:30 CDT

Original text of this message