16-07-2013 door: Jurrie Zaat
Onlangs liep ik tegen een vervelend probleem aan: een in de ontwikkel- en testomgeving prima draaiend batchprogramma gaf in de acceptatieomgeving opeens allerlei foutmeldingen.
Wat was er aan de hand? Een aanzienlijke stored procedure (package) in een Oracle 11g database had tot doel data van een tweetal databases te converteren naar een derde. Dit gebeurde in een grote loop, met allerlei cursoren waarbij in 75% van de gevallen met databaselinks werd gewerkt. Na elke doorloop van de buitenste loop werd een commit uitgevoerd. Hier ging het mis: na de commit trad een foutmelding op: ORA-02050 (transaction string rolled back, some remote DBs may be in-doubt) gevolgd door ORA-02051 (another session in same transaction failed). De integriteit van de database die over de databaselink wordt benaderd ten opzicht van de opgehaalde dataset wordt betwijfeld en Oracle draait de hele transactie terug. Verschil tussen ontwikkel en test aan de ene kant en acceptatie was de hoeveelheid data-activiteit. In acceptatie waren behoorlijk veel transacties in de gelinkte databases, in ontwikkel en test vrijwel geen.
Belangrijk om te weten is dat Oracle ook bij het uitsluitend opvragen van data via een databaselink toch een transactie start. Zolang er geen commit wordt gegeven, blijven de opgevraagde rijen dus in de sessie gereserveerd.
De oplossing voor het probleem is derhalve, om direct na het opvragen van de rij(en) via een databaselink een commit uit te voeren. Meestal is het echter in een grote loop, waarin allerlei controles dienen te worden uitgevoerd niet wenselijk om tussentijdse commits uit te voeren. Vandaar dat ik er voor gekozen heb om alle cursoren te vervangen door functies, die via een autonomous_transaction pragma een aparte sessie starten om de data op te halen. Deze kan vervolgens afgesloten worden met een commit en het probleem is verholpen.
Een voorbeeld:
Oude situatie
[code type=”sql”]
declare
—
cursor c_tst(b_parameter in varchar2)
is
select *
from tst_tabel
where code = b_parameter;
—
begin
—
for r_tst in c_tst(‘FOO’) loop
…
end loop;
—
commit;
end;
[/code]
Nieuwe situatie
[code type=”sql”]
declare
type t_tst is table of tst_tabel%rowtype index by binary_integer;
l_tst t_tst;
—
function get_tst(p_parameter)
return t_tst
is
pragma autonomous_transaction;
l_tst t_tst;
—
cursor c_tst(b_parameter in varchar2)
is
select *
from tst_tabel
where code = b_parameter;
—
begin
open c_tst(p_parameter);
fetch c_tst bulk collect into l_tst;
close c_tst;
commit;
return l_tst;
end get_tst;
—
begin
l_tst := get_tst(‘FOO’);
for i in 1..l_tst.count loop
…
end loop;
commit;
end;
[/code]
Beetje meer code, maar wel opgelost.