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.
>>>
>>>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
I think the horse is likely deceased but to follow up:
DECLARE
CURSOR acur IS
SELECT * FROM t1;
CURSOR bcur IS
SELECT * FROM t2;
BEGIN
OPEN acur;
OPEN bcur;
- do stuff with acur
- bcur will never see it even in the same session
END;
/
I suspect there is no disagreement.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Fri Apr 15 2005 - 18:07:30 CDT
