Re: invalid ROWID [a little long]

I apologize for lack of proper version info - it is 10.2.0.1 (no patches) on Solaris10/SPARC, 64-bit

Remigiusz Sokolowski wrote:
> Hi!
>
> while we have got some work-around for the problem I describe below, I
> still wonder where there is a misconception here (and where invalid
> ROWID error could come from) and look for people better informed than
> me (as You :-))
>
> The main concept here is to service large SOAP request which consists
> of many small operations (that is why there are tables soap_requests
> (being parent) and soap_atomic_requests (being children).
>
> There is a loop running at most 3 times. It executes the statement
> SELECT ar.rqa_id, ar.rq_id, [..], ar.pdesc, r.login, r.pass, r.sid
> FROM soap_atomic_requests ar JOIN soap_requests r ON ar.rq_id=r.rq_id
> WHERE ar.status='N' or (ar.status='E' AND ar.attempt<1 AND
> ar.proc_date<sysdate-900/86400)
>
> Then in an inner loop there is a processing of rows fetched from the
> query above. The result of the processing is set in the field status
> success
> UPDATE soap_atomic_requests SET fin_date=sysdate, status='X',
> e_code=:eCode WHERE rqa_id=:blkId
> or failure
> UPDATE soap_atomic_requests SET e_msg=:eMsg, e_code=:eCode,
> proc_date=sysdate, attempt=attempt+1, status='E' WHERE rqa_id=:blkId
> As You may see - there are updates of fields which are provided in
> WHERE clause of the "parent" query
>
> The application is trying to fullfil every subrequest at most 3 times.
> If any of subrequests has failed after 3 tries, whole big request is
> considered as failed.
> This script is being run from the crontab (let's say every 15 minutes)
>
> Another script (which is able to run in parallel and also is run from
> crontab) checks if any of requests-parents is fullfiled in the whole
> (i.e. all its subrequests were performed successfully) and sets
> request-parent status to X. Afterwards the script fetches all
> requests-parents with status X and generates a response.
> Then comes to cleaning
> DELETE FROM soap_atomic_requests a WHERE a.rq_id IN
> (SELECT r.rq_id FROM soap_requests r WHERE r.status='X' AND
> r.resp_path is not null)
> After removing operation script rebuilds indexes on the tables
> involved (as deleted rows can be quite large part of the tables).
> That is all.
>
> However the first script after some time in run catches ORA-1410
> invalid ROWID while fetching a row from
> SELECT ar.rqa_id, ar.rq_id, [..], ar.pdesc, r.login, r.pass, r.sid
> FROM soap_atomic_requests ar JOIN soap_requests r ON ar.rq_id=r.rq_id
> WHERE ar.status='N' or (ar.status='E' AND ar.attempt<1 AND
> ar.proc_date<sysdate-900/86400)
> query.
> The question is why? Is it due to rebuilding the indexes or deletion?
> I assume the ROWIDs of above query don't change (there is no shrinking
> operation performed), though the row movement for the table
> soap_atomic_requests is enabled so in general it is possible - are
> there any row "migrations" in tables behind the scenes?
>
> A workaround is to run the second script after the completion of the
> first one.
>
>
> TIA
> Remigiusz
>

-- 
---------------------------------------------------------------------
Remigiusz Sokolowski <rems_at_wp-sa.pl>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL v04.x,05.x; Oracle v10.x

Zastrzezenie:
Niniejsza wiadomosc stanowi jedynie wyraz prywatnych pogladow autora 
i nie jest w zadnym wypadku zwiazana ze stanowiskiem przedsiebiorstwa 
Wirtualna Polska S.A.
---------------------------------------------------------------------



WIRTUALNA  POLSKA  SA, ul. Traugutta 115c, 80-226 Gdansk; NIP: 957-07-51-216; 
Sad Rejonowy Gdansk-Polnoc KRS 0000068548, kapital zakladowy 62.880.024 zlotych (w calosci wplacony)
--
http://www.freelists.org/webpage/oracle-l