Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Does SELECT start a transaction?

RE: Does SELECT start a transaction?

From: <ryan_gaffuri_at_comcast.net>
Date: Sat, 07 Apr 2007 11:29:10 +0000
Message-Id: <040720071129.4197.4617808600048828000010652200763692079D9A00000E09A1020E979D@comcast.net>


I remember reading some theory books a few years ago and my understanding is that

  1. 2 phase commit is just lock on, lock off(when you commit)
  2. 3 phase commit is for distributed transactions across multiple databases.

my understanding of a 3 phase commit is:

1. you need to insert to 2 tables in 2 databases.
2. process inserts to table A in database A
3. process inserts to table A in database B
4. However, the process managing the transaction needs to wait for an 'ack' from both databases before the transaction is complete and the ack process is the three phase commit. 

I could be mis-remembering it.

I would think the reason for starting a transaction on a select across a database link was to manage the transaction across multiple databases which is a 3 phase commit.

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
Received on Sat Apr 07 2007 - 06:29:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US