EDB to Oracle db link isolation level problem

Hello!

I would need some help with isolation levels for db link between EDB and Oracle database. I've created a DB link from EDB to Oracle and we are pushing data to Oracle database.  I saw that default transaction isolation level for db link between EDB and Oracle is SERIALIZABLE (EDB -> Oracle db link docs). I see that there is another property for edb_dblink_oci parameter (default value od the property is SERIALIZABLE) which is SCROLL but I can't find which isolation level it uses. The problem that occurs when pushing data to Oracle DB is the exact one that is written in the link (

ORA-08177: can't serialize access for this transaction

).
The job has to send new data every 1 hour and with retry there is no guarantee that the job will finish in reasonable time and if I limit the number of retries, it's not possible to guarantee that all data will be pushed to Oracle DB.

Is it possible to change isolation level for db link to READ COMMITED?

Yes, it seams that setting the isolation level to READ_COMMITTED should be the way to go.

From Foreign Data Wrapper for Oracle https://github.com/laurenz/oracle_fdw#foreign-server-options

Unfortunately Oracle's implementation of SERIALIZABLE is rather bad and causes serialization errors (ORA-08177) in unexpected situations, like inserts into the table.
Using READ COMMITTED transactions works around this problem, but there is a risk of inconsistencies. If you want to use it, check your execution plans if the foreign scan could be executed more than once.

The same topic has been discussed 10 years ago http://www.pmsas.pr.gov.br/wp-content/?id=coderanch-1z0-809&exam=t/621777/databases/ORA-serialize-access-transaction#2842393.

As result two proposals were made:

(1) Handling of ORA-08177 by re-trying the transaction

The only cure for the ORA-08177 error is to rollback changes and re-run the entire business transaction. If there are no update collision on the next run, the transaction will finish.

(2) Adding ROWDEPENDENCIES to table in Oracle DB

This causes that the version of data in DB i.e. socalled System Change Number (SCN)  is handled on row level instead of table level (default). See also
Donald Burleson "Oracle rowdependencies tips" http://www.dba-oracle.com/t_row_scn_rowdependencies.htm

I guess doing this show decrease performance since more effort is needed to update data and do the additional checks.

If someone will have the same problem like I did, I found a solution. Default ISOLATION_LEVEL for DB Link from EDB to Oracle is SERIALIZABLE because it's the most similar to Postgres REPEATABLE READ ISOLATION_LEVEL. The solution for that is that you set edb_dblink_oci.rescans parameter to SCROLL which will then change the ISOLATION_LEVEL to READ COMMITED. I made the change on session level, but it can be set at DB properties but DB restart is necessary.

To change it on session level, you should execute following command:

set edb_dblink_oci.rescans=SCROLL;

To check value AFTER the changing edb_dblink_oci.rescans properties:

show edb_dblink_oci.rescans;

To check the ISOLATION_LEVEL on Oracle DB on which DB Link points to execute the following command:

SELECT s.sid, s.serial#, CASE t.flag::int & power(2,28)::int WHEN 0 THEN 'READ COMMITED' ELSE 'SERIALIZABLE' END AS isolation_level, s.machine, s.sql_exec_start FROM v$transaction@<db_link> t JOIN v$session@<db_link> s ON t.addr = s.taddr;

but be sure that you create transaction first (just do simple insert, select, update,...)

To read more about it:
https://www.enterprisedb.com/docs/epas/latest/reference/oracle_compatibility_reference/epas_compat_sql/21_create_public_database_link/#description
https://www.cybertec-postgresql.com/en/with-hold-cursors-and-transactions-in-postgresql/
https://www.oracle.com/technetwork/database/scrollable-cursor-131342.pdf