Tuesday, August 14, 2012

Why does TOAD prompt me to Commit or Rollback?

I have been using Toad for awhile now and noticed that from time-to-time, it would present this dialog when I was closing a DB connection:

Being unsure, I would always click on 'Rollback' thinking I had some orphaned DML statement. I'm usually very deliberative on how and when I update rows so I thought this was odd at the time and continued on.

Turns out this is simply caused by performing a normal SELECT statement - while using a DB_Link to a remote server.  

Question you may be asking yourself: Why does a SELECT over a DB_Link require a commit after execution ?

Answer: When Oracle performs a distributed SQL statement Oracle reserves an entry in the rollback segment area for the two-phase commit processing.  This entry is held until the SQL statement is committed even if the SQL statement is a query. The requirement to commit distributed SQL exists even with automated undo management available with version 9i and newer.

Notice that the SELECT statement is noted here:

A review of our Developers' code, it should thus be a habit to always issue a COMMIT after any SELECT where a DB_Link was used to satisfy Oracle's two-phase commit processing - kind of in the same light of CLOSING/DISPOSING.

No comments:

Post a Comment