RE: Does SELECT start a transaction?

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • To: <ryan_gaffuri@xxxxxxxxxxx>, <knightjck_work@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 6 Apr 2007 10:29:55 -0400

Hi Ryan,
2-phase commit, not 3-phase commit, but yes, that's the reason.
Hi Jon,
In reading Ryan's reply, I also re-read your original posting.
"....Oracle documentation states that one of the requirements is that
there are no active transaction on the *destination* table."
The reason for this is that if you do a direct load, it will take a TM
enqueue in 'X' mode, preventing any other DML from happening on the
table.  (This is done to protect the HWM, which is directly manipulated
during direct load.)  At the start of the load, before the TM enqueue
can be acquired, the transaction will queue behind any other DML holding
locks on the table.  However, I just did a little test (on and
though a transaction is started on the local database, it doesn't seem
to actually acquire any locks on any tables, so, even if you DO have
remote or distributed selects happening, they should NOT interfere with
your direct load.
Sorry, just a case of me not fully processing *why* you were asking the
question, and that changing the answer.

Mark J. Bobak 
Senior Oracle Architect 

"There are 10 types of people in the world:  Those who understand
binary, and those who don't." 



From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Sent: Friday, April 06, 2007 9:22 AM
To: knightjck_work@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Cc: Jonathan Knight; cmerrill@xxxxxxxxxxxxxx; jknight@xxxxxxxxxxxxxx
Subject: Re: Does SELECT start a transaction?

I think in some databases where reads lock writes, select is considered
to lock a transaction. I think SQL Server used to work this way, but
with SQL Server 2005 they added some form of Multi-Versioning.
Transactions only involve changes to the data. 
Mark: I didnt know a select started a transaction when you access a
remote object. That is interesting. So you actually get an SCN off a
select in the redo logs when you select across a database link? I guess
this is for the 3 phase commit process. 

        -------------- Original message -------------- 
        From: Jonathan Knight <knightjck_work@xxxxxxxxx> 
        > We've scoured the documentation and are still a little fuzzy
:-) on this ... 
        > We're trying to convert some SQL*Loads from conventional to
direct and the 
        > Oracle Documentation states that one of the requirements is
that there are no 
        > active transactions on the destination table. Obviously SELECT
        > would begin a transaction, but what about a simple SELECT? 
        > Many thanks, 
        > Jon Knight 
        > ____ 
        > We won't tell. Get more on shows you hate to love 
        > (and love to hate): Yahoo! TV's Guilty Pleasures list. 
        > -- 
        &g t; 

Other related posts: