RE: Does SELECT start a transaction?

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 9.2.0.8) 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    

-- 
Mark J. Bobak 
Senior Oracle Architect 
ProQuest/CSA 

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

 


________________________________

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
ryan_gaffuri_at_comcast.net
Sent: Friday, April 06, 2007 9:22 AM
To: knightjck_work_at_yahoo.com; oracle-l_at_freelists.org
Cc: Jonathan Knight; cmerrill_at_concordefs.com; jknight_at_concordefs.com
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_at_yahoo.com> 
	


	> 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

... FOR UPDATE 


	> 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. 

	> http://tv.yahoo.com/collections/265 

	> -- 

	&g t; http://www.freelists.org/webpage/oracle-l 


	> 

	> 



--
http://www.freelists.org/webpage/oracle-l