RE: Documentation on Transaction Management
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: <chet.justice@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Nov 2009 17:26:11 -0500
"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@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
===============================================================================
Other related posts:
