What are the considerations when using SERIALIZABLE
isolation level over the default READ COMMITTED
in an Oracle database?
Given the concurrency system used within Oracle (using transaction logs to determine the state of the database at a point in time), I understand that using a SERIALIZABLE transaction does not decrease the availability of the database over READ COMMITTED isolation.
One consideration is that there must be enough space allocated for undo segments to allow the database to store enough history to accommodate the implied extra time required to support transaction-level over statement-level read consistency. If there is not enough undo data, there is a risk of error ORA-01555 Snapshot Too Old
(although this is still an risk with a READ COMMITTED query).
One point that I am not clear on is if there are any performance implications of serializable isolation. Is there any extra cost associated with, for example, having to go potentially further back in the undo data to return the records?
Finally, as I am talking about a read-only transaction the assumption is that there is no risk of a ORA-08177: Cannot serialize access for this transaction
from writing to data which has since been updated.