Hi
I'd like to know if there is a way to specify different transaction
isolation levels for different tables in the db. The reason i'm asking
this (rather bizarre sounding, i know ;-) ) question is the following:
I'm importing about 2 million records into my application each day (the
data is more or less fully replaced each day). My importer updates only
a few tables (about 5 - 10), but reads a lot of other tables (10 or so)
while importing. Those (read-only, meta-information) tables contains
information on how to
import the data, and what reports to calculate from the imported data.
My import sometimes crashed, becausse the meta-information tables are
changed while importing (e.h, I pass a id to a function, the function
does some calculations, than tries to select the row with the given id,
but fails, because the row was deleted in the meantime). I understand
that the standard approach to this problem is to set the transaction
isolation level to "serializeable" , thus avoiding non-repeatable reads.
But since the import is a lenghty operation (a few hours), I don't want
to import in a searializeable transaction, since it would force me to
import "in a loop" until no serialization error occurs while importing.
But since it's only the meta-information tables for which I want to
avoid non-repeatable reads, and since those are read-only anyway (for my
importer), I wouldn't have to fear getting "serializat ion errors" when I
access only those tables in serializeable mode (since read-only
transaction never trigger serialization errors).
I know I could simulate something like that using dblink, but if
possible I'd prefer a simpler approach (Using dblink would meand that I
need to rewrite large parts of import, since it's mostly stored procedures).
greetings, Florian Pflug
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
I'd like to know if there is a way to specify different transaction
isolation levels for different tables in the db. The reason i'm asking
this (rather bizarre sounding, i know ;-) ) question is the following:
I'm importing about 2 million records into my application each day (the
data is more or less fully replaced each day). My importer updates only
a few tables (about 5 - 10), but reads a lot of other tables (10 or so)
while importing. Those (read-only, meta-information) tables contains
information on how to
import the data, and what reports to calculate from the imported data.
My import sometimes crashed, becausse the meta-information tables are
changed while importing (e.h, I pass a id to a function, the function
does some calculations, than tries to select the row with the given id,
but fails, because the row was deleted in the meantime). I understand
that the standard approach to this problem is to set the transaction
isolation level to "serializeable" , thus avoiding non-repeatable reads.
But since the import is a lenghty operation (a few hours), I don't want
to import in a searializeable transaction, since it would force me to
import "in a loop" until no serialization error occurs while importing.
But since it's only the meta-information tables for which I want to
avoid non-repeatable reads, and since those are read-only anyway (for my
importer), I wouldn't have to fear getting "serializat ion errors" when I
access only those tables in serializeable mode (since read-only
transaction never trigger serialization errors).
I know I could simulate something like that using dblink, but if
possible I'd prefer a simpler approach (Using dblink would meand that I
need to rewrite large parts of import, since it's mostly stored procedures).
greetings, Florian Pflug
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
Comment