invalid ROWID [a little long]
- From: Remigiusz Sokolowski <rems@xxxxxxxx>
- To: 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 31 Jul 2007 15:15:20 +0200
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@xxxxxxxx>
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)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
