Performance of rollback on nothing to commit connection

Question

I have a context in which I have a connection pool in which connections are freed with no guarantee of having been committed or rolled back.

In this context I'm thinking on implementing in the pool itself a rollback on every connection that gets freed but I guess what performance impact this action could have.

This question is specifically about Oracle DB. What actions does Oracle do when a rollback is performed in a transaction with no pending inserts nor updates? For example, what happens (in performance means) if you rollback two consecutive times or do a commit and immediately a rollback?

Answer 1

There will be no performance impact of unnecessarily rolling back every session. Compared to opening and closing a session, a rollback is practically free. For example, the below PL/SQL block rolls back one million times in 6 seconds on my machine.

begin
    for i in 1 .. 1000000 loop
        rollback;
    end loop;
end;
/

Oracle writes every change to the UNDO tablespace before anything is committed or rolled back. There are some significant costs associated with that approach, but it means when you rollback, Oracle does not have to check every table in the database, it only has to check the UNDO data for anything related to the current transaction. If nothing is found, then nothing needs to be done. I would guess that every rollback requires at worst one index lookup. Which is not something you need to worry about if it only occurs once per session.