RE: Documentation on Transaction Management

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Thu, 19 Nov 2009 17:26:11 -0500
Message-ID: <21469B88E0EA11498818517F21033531126D7481_at_EPRI17P32001A.csfb.cs-group.com>

"select for update" will lock the row, and can do so only if there is no uncommitted transaction involving the row. You can use "SELECT...FOR UPDATE NOWAIT" to get an error if the row can't be locked right away; otherwise, the SELECT and lock will occur upon commit or rollback of the conflicting transaction.  

Either way, you'll get the current committed version of the row, and once locked, no other user can modify the row until your update is committed or rolled back. Thus, there's no possibility of overwriting someone else's update.  

The only issue is how long it will take! There's probably a better way but I'm sure others will weigh in on that.  

Paul Baumgartel
CREDIT SUISSE
Information Technology
Prime Services Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com  

---

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of chet justice

	Sent: Thursday, November 19, 2009 5:03 PM
	To: oracle-l
	Subject: Documentation on Transaction Management
	
	
	Here's the situation:
	
	Database is 10gR2
	
	(This is for a friend, so I don't have any more specifics than

what he described)         

        I need to update millions of records in an address table, specifically 2 columns. CTAS was the first thought. But there's a small catch, any user accessing the system needs to be able to update their record during that "maintainence" time. So far, the fastest he can get it to run in a test environment is anywhere from 12-24 hours, using a simple LOOP and committing every n number of records.         

        He had the idea to loop through using the PK of the address table, perform the lookups/updates necessary using SELECT FOR UPDATE and then commit. From my understanding of how Oracle works, getting the individual record within the outer loop would get it as it exists at that moment in time (committed or roll(ed)back). Using one big loop with the PK and the other values would get the idea as it existed at the beginning of the query, thus ultimately potentially writing over any updates made by end-users.         

        Am I off my rocker here? Is my thinking correct?         

        If so, where can I find that documentation, specifically? A quick glance and I couldn't find anything related specifically what I am trying to accomplish (or advise).         

        If I didn't explain this well enough, I'll accept any hand-slapping and dutifully provide more information.         

        chet                  

        chet justice         

	813.863.1213
	http://oraclenerd.com
	http://twitter.com/oraclenerd
	http://www.linkedin.com/in/chetjustice
	


=============================================================================== 

 Please access the attached hyperlink for an important electronic communications disclaimer:  http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

---

 

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

Received on Thu Nov 19 2009 - 16:26:11 CST