Transactions spanning multiple schemas in a database

Question

I have a single connection which does DML on objects in two different schemas of an Oracle database. I complete a transaction involving objects in schema A and then start a second transaction to work with objects in schema B. If any errors occur when I am working in the second transaction, I want to roll back the changes for both transactions. Is that possible?

Answer 1

If you want the changes that are made to both schemas to be committed or rolled back together, you need to use a single transaction. Once you commit the first transaction, you cannot then roll it back if the second transaction fails (well, potentially, you could using flashback technology but it gets way more involved). If you used a single transaction for the work you are doing on both schemas, however, you'd get the behavior you desire that all the changes would be committed or rolled back atomically.

Answer 2

Set your Connection so it doesn't autocommit (which it does by default), e.g.:

conn.setAutoCommit(false);

Then you can run the DML against the first schema and, using the same connection object, run your DML against the second schema. If something fails, roll everything back, e.g:

conn.rollback();

Otherwise, if everything succeeded, commit:

conn.commit();