Home » SQL & PL/SQL » SQL & PL/SQL » Is it safe to rely on ROWID if the row is locked? (Oracle 10g)
Is it safe to rely on ROWID if the row is locked? [message #406350] Wed, 03 June 2009 09:19 Go to next message
kentbower
Messages: 6
Registered: June 2009
Location: USA
Junior Member
I understand there are events that can happen to change the value of an Oracle rowid.

Some examples:
Quote:
"Although a rowid uniquely identifies a row in a table, it might change its value if the underlying table is an index organized table or a partitioned table.... This implies that rowids should not be stored away for later re-use as the corresponding row then might either not exist or contain completely different data."


My question is, can this ever happen if I have the row locked for update?

For example: if I "select rowid ... for update" and I know the row is locked, can I 100% rely on the fact that the rowid cannot change while I have the row locked? (I'd prefer to use "where current of returning value into variable", but this is apparently not supported, so I need to do a "select ... where rowid = var_rowid" to refresh some data that changed since the cursor was open.)

If the row is locked, am I guaranteed rowid hasn't changed since I locked the row?

P.S. there is no primary key on the table. On asktom.com, I see this quote, but I have to primary key and also the thread didn't mention the case where the row was locked:
Quote:
It is perfectly safe to use the rowid in ALL CASES however, assuming you combine it with
the primary key as well:


update t
set...
where rowid = :x and primary_key = :pk;


we can use the rowid to find the row (fast) and assuming it is still there update it.
Note, you need to combine with this some form of LOST UPDATE protection as well !!! You
cannot just update by rowid and primary key - unless you locked the row when you read it
out, else you will overwrite other peoples changes blindly.


http: //asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53140678334596
Re: Is it safe to rely on ROWID if the row is locked? [message #406351 is a reply to message #406350] Wed, 03 June 2009 09:29 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
kentbower wrote on Wed, 03 June 2009 15:19

My question is, can this ever happen if I have the row locked for update?



No.

If you have a cursor that selects records for update then do UPDATE WHERE CURRENT OF <cursor>, oracle uses rowid internally to do the update. It wouldn't if it wasn't safe to do so.
Re: Is it safe to rely on ROWID if the row is locked? [message #406353 is a reply to message #406351] Wed, 03 June 2009 09:49 Go to previous messageGo to next message
kentbower
Messages: 6
Registered: June 2009
Location: USA
Junior Member
cookiemonster,

Thanks very much for the reply!

I had read Oracle uses rowid for implementing "where current of <cursor>", but wasn't sure about the specifics or if it is actually documented.

I also figured Oracle might be smart enough to change its internal references of rowid if it detected that a change had occurred so that Oracle could guarantee that "where current of" would never break, even if the rowid changed. Obviously, if it did this, it wouldn't update *my copy* of the rowid variable.

Is it possible Oracle is doing something like this/is it documented, or are you certain it won't change rowid on a locked row?

Thanks again
Re: Is it safe to rely on ROWID if the row is locked? [message #406356 is a reply to message #406350] Wed, 03 June 2009 10:21 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think you're worrying unnecessarily here.
If rowid could change at any point then it'd be absolutely no use to anyone and oracle probably wouldn't even advertise its existence, and if they did the warnings against its use would be a lot stronger.

That said if you find yourself having to store rowids you might as well just use where current of, not really sure why you're trying to do it manually.
Re: Is it safe to rely on ROWID if the row is locked? [message #406359 is a reply to message #406356] Wed, 03 June 2009 11:12 Go to previous messageGo to next message
kentbower
Messages: 6
Registered: June 2009
Location: USA
Junior Member
Yeah, you are probably right, they probably can't change.

Quote:
you might as well just use where current of, not really sure why you're trying to do it manually


The reason I can't use "where current of" is because I'm in a fairly complex pl/sql cursor for loop where I need to process each record (for which I do use "where current of".)

However, as part of processing a record, other records that are in the cursor need to be updated, so once I get to that record, the cursor snap-shot is out of date (I've changed it during my previous processing).

So the first thing I need to do at the top of the for loop is refresh the data.

However, Oracle doesn't allow:

Select field
from table
where current of cursor;

Oracle's documentation show this as valid syntax, but it doesn't support it either:

update table
set field = field
where current of cursor
returning field
into variable;

That would have allowed me to do the refresh.

Since that isn't supported, I have very few options left.
There is no primary key on the table and it is an interfaced table that I am not allowed to change (so I can't add a pk).

I could close and re-open the cursor, but then I have to start over and keep track of which records I've already processed each time, and that gets very messy for a variety of reasons.

With no primary key, the only thing I can think of left is to do the refresh by rowid:

select field
into row.variable
from table
where rowid = row.rowid;

I know the record is locked, but I was disturbed by the notion that Oracle can change rowids.

Your reasoning seems sound to me, and you are confident that the rowid can't change while the row is locked, correct?

Can you think of any other approach to this?

Thanks again very much for your input.

[Updated on: Wed, 03 June 2009 11:13]

Report message to a moderator

Re: Is it safe to rely on ROWID if the row is locked? [message #406360 is a reply to message #406350] Wed, 03 June 2009 11:28 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Can you think of any other approach to this?
I have been monitoring this thread & I am still unclear exactly what problem you are trying to solve.

I just wish to point out that even though this table has no PK, if a subset of columns uniquely identify a row, they could be used as a pseudo primary key.

Based upon what criteria would an independent observer conclude your problem has been solved?
Re: Is it safe to rely on ROWID if the row is locked? [message #406361 is a reply to message #406359] Wed, 03 June 2009 11:30 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The way I see it, you can usually use rowids "in memory" quite confident that they are not changing while you use them.

The common causes for rowid changes can only happen during application downtime. Like moving tables to different tablespaces, or exporting / importing the data, etc....

So you should definitely not storethem anywhere for later use, where they might "survive" such procedures, but using them inside a single transaction in memory should be perfectly save.

[Updated on: Wed, 03 June 2009 11:31]

Report message to a moderator

Re: Is it safe to rely on ROWID if the row is locked? [message #406365 is a reply to message #406360] Wed, 03 June 2009 11:46 Go to previous messageGo to next message
kentbower
Messages: 6
Registered: June 2009
Location: USA
Junior Member
Quote:
I have been monitoring this thread & I am still unclear exactly what problem you are trying to solve.



Sorry if I've been unclear. Imagine a simple cursor:

cursor c is
Select *
from tablea
where (some criteria for processing)
for update;
...

for row in c loop
--processing on row...
--this processing may update another record in tablea
--which we haven't reached yet in this loop
end loop;


Now, since processing the records fetched by the cursor may update other records that we haven't reached yet, once we get to that point later in the loop, the data will no longer be accurate, so I need to refresh it:


for row in c loop
select field-that-might-have-changed
into row.field-that-might-have-changed
from tablea
where current of c;

--processing on row...
--this processing may update another record in tablea
--which we haven't reached yet in this loop
end loop;


That is what i'd prefer to do in order to refresh this data, but oracle doesn't allow "select...where current of", so I am resorting to rowid. (There is no PK, nor is there a unique way to identify this row.)

for row in c loop
select field-that-might-have-changed
into row.field-that-might-have-changed
from tablea
where rowid = row.rowid;

--processing on row...
--this processing may update another record in tablea
--which we haven't reached yet in this loop
end loop;


My concern was that Oracle can change this rowid. Others have pointed out that this concern is probably not founded especially since the row is locked for update.

But that is what the thread was asking: is it safe to use rowid if the row is locked?

Thanks again to everyone who had input (especially if you are sure of the answer and not speculating... Smile )

Re: Is it safe to rely on ROWID if the row is locked? [message #406366 is a reply to message #406350] Wed, 03 June 2009 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Now, since processing the records fetched by the cursor may update other records that we haven't reached yet,
>once we get to that point later in the loop, the data will no longer be accurate, so I need to refresh it:

This "assumption" is simply WRONG!
Oracle ensure a Read Consistent view of data from start of transaction!

Your transaction will only see data as it existed at time of SELECT ... FOR UPDATE.

You seem to be trying to solve a "problem" which does not exist in Oracle.

[Updated on: Wed, 03 June 2009 11:51]

Report message to a moderator

Re: Is it safe to rely on ROWID if the row is locked? [message #406368 is a reply to message #406366] Wed, 03 June 2009 12:02 Go to previous messageGo to next message
kentbower
Messages: 6
Registered: June 2009
Location: USA
Junior Member
Quote:
This "assumption" is simply WRONG!


No need to yell. Wink


I believe you misunderstood what I have written or else you are incorrect.

If I change data in a row after I have opened the cursor, but before I have fetched that row, you seem to be saying that when the data is fetched from the cursor, I will have the newly changed data. That is certainly incorrect.

The data is consistent from when the cursor is opened.

Re: Is it safe to rely on ROWID if the row is locked? [message #406486 is a reply to message #406350] Thu, 04 June 2009 03:52 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's hard to know what to suggest because your requirement is unclear.

I know you want to update rows you might select later on but without knowing why you need to do this it's hard to suggest
an alternative. The way you're doing it will work, but it's going to be horribly slow for any significant amount of data.

One thing you could try is to select bulk collect the data into an array.
Then loop over the array and fix up the data how you want it.
Then apply the changes from the array in a single update statement.
This may be easier and faster than what you're currently doing.

Ideally you want to do everything in a single update but again we'd need to know the requirements to make any suggestions.

Thinking about it - how do you know that the processing you're doing isn't going to update a row you've already looped over?
And if it does is that a problem?
Re: Is it safe to rely on ROWID if the row is locked? [message #406522 is a reply to message #406486] Thu, 04 June 2009 07:01 Go to previous message
kentbower
Messages: 6
Registered: June 2009
Location: USA
Junior Member
The amount of data shouldn't be a problem so I am ok with it being slow.

I'd get more into the requirements if I thought that was feasible under the circumstances.

Even if I did the bulk collect into an array, I'd need to select the rowid so I could perform the update, correct? So that isn't much different except for possibly performance.

Quote:
Thinking about it - how do you know that the processing you're doing isn't going to update a row you've already looped over?
And if it does is that a problem?


That's covered, but I can tell you're really quick for identifying it!

Thanks again for the suggestions. My main concern was about rowid changing, but the consensus seems this is safe if I've got the rows locked, at least that's the consensus I gather and no one has disputed that...
Previous Topic: string matching
Next Topic: Find Out Day By Number Of Day
Goto Forum:
  


Current Time: Sun Dec 11 03:51:09 CST 2016

Total time taken to generate the page: 0.12774 seconds