When extracting data for a SQL Remote database, it is highly recommended to run the according queries with isolation level 3 when extracting from an active database - cf. this quote from the DBXTRACT doc page:
This is understandable: Say, the remote would need data from table_1 to table_100, and would query the data in ascending "table name order". Then isolation level 3 ensures that no other transaction can modify data in any of the tables in a way that would affect the extracted result sets once they have been queried:
Obviously, isolation level 3 may limit oncurrent modifications.
Is there any other method to reliably extract data
(Note: I'm gonna try an answer myself...)
I have not tried this yet, and would like an expert like Reg to comment on that approach:
One might use isolation level "snapshot" to query the data (within one transaction).
This would ensure that the contents of table_1 to table_100 as of the state of the transaction's start are extracted, no matter whether another transaction would modify the data. I.e. even if another transaction would modify table_50 before the data of that table have been queried for extraction, the extraction would still get the same result set for table_50 as if it had been unchanged.
In contrast to using isolation level 3 (serializable) this won't put any locks on the queried tables.
However, there are two caveats:
If my understanding is correct, this might be a method to increase the throughput while extracting data on a busy system.