I am working on an OLTP/OLAP hybrid application. I need to read from a
large table more than once consistently within a stored procedure. In
the stored procedure, I also stored the intermediate data in a temporary
table for further processing (select, update, and delete) by other SQL
statements. In the Oracle manual, it says that 'set transcation
isolation level serializable' statement will allow repeatable reads and
avoid phantom rows.
However, when I tried this, I have been getting the following error:
"ORA-08177: Cannot serialize access for this transaction"
At the time that this error occurred, there was only one user on the
database. I have set the INITRANS to 5 for the affected tables.
Is there a limit on how long a transaction can be when using
serializable transaction? What's the proper design for using
serializable transaction? Is there another way of getting repeatable
reads without using serializable transaction?
Thanks in advance!