Re: For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMIT or ROLLBACK preferred?

On Sun, Aug 25, 2019 at 10:12 PM David Wall <d(dot)wall(at)computer(dot)org> wrote:
> The main issue is that if
> we do a SELECT and get a ResultSet that has no rows, if we do a commit
> or a rollback, it seems reasonable that these are identical as no
> changes were made. My inclination is to do a Connection.commit() on the
> connection because it wasn't in error or anything even if no rows were
> found, but wondered if a Connection.rollback() has any difference
> (positive/negative) in such a scenario.

Quite frankly I would redesign your application workflow. Sounds like
you are building a framework to issue queries, and I suggest you to
clearly mark transactions only when needed because, disregarding
performances, it does not make much sense to commit/rollback on a
"data quantity" discrimintation. At least, as far as you described it.

Moreover, as Tom pointed out, there could be a SELECT against a
function (that could return nothing at all) with side effects. How are
you going to discriminate such case?

Luca