Re: Automatic transactions in SELECT...

Tom Lisjac <netdxr(at)gmail(dot)com> wrote:

> I wish I had more specifics. I'm working from a rambling paper
> that claims Postgres uniquely runs SELECT statements inside
> transactions which is causing the app to hang when there's no
> explicit commit/rollback provided afterward. The paper also
> claims that "other databases" don't behave that way and have
> "solved this problem long ago". None of that makes any sense and
> my goal is to understand and clarify the real issues so we can
> steer the team toward solving the problem instead of throwing
> rocks at Postgres.
>
> As Tom Lane pointed out, the JDBC/JBoss/Hibernate client-side
> frameworks we're running could be contributing to this problem by
> being overly helpful with transaction management. A google for
> "hibernate hangs on select" returns a large number of hits, so
> this appears to be a good area to start troubleshooting.

As Tom said, there is no way to turn on "chained" transactions (or
turn off "autocommit") on the server side -- all multi-statement
transactions must be explicitly started and ended by the client.
So it is impossible for the problem to be anywhere except the
client side.

I have seen a couple points of confusion by application programmers
which might contribute to what you describe, which I'll explain in
hopes that it will help you sort things out.

(1) If autocommit is off in the PostgreSQL JDBC driver, it tracks
whether an explicit transaction is pending on the connection, and
if you execute a statement when a transaction is not pending it
starts one by executing a BEGIN statement before executing the
requested statement.  This transaction will stay open until you
invoke the Connection.commit() method.  Some programmers seem to
feel that if they issue a simple SELECT in this mode it should not
start a transaction, but that would leave transaction boundaries
ill-defined.  What if you want to run multiple SELECT statements
and see a consistent view of the data, or use the results of the
first query to determine whether some other action should occur --
without keeping the transaction open, you can't assume it is
*still* true when you get to the next statement.  Assuming that a
simple SELECT was not the first statement in a transaction would be
a fragile assumption based on what functions, rules, and triggers
can do.  If nothing else, they may acquire locks which need to be
held to ensure the integrity of a subsequent operation.  Unless the
JDBC driver honors the autocommit setting even for a simple SELECT,
there are useful things you just can't do.

(2) I have seen programmers try to deal with this by setting
autocommit on before they run some SELECT statements, and set
autocommit off for cases which they determine need that setting.
So far so good.  If they do that correctly, all is well.  But then
they forget to commit() some transaction(s) which leads many
problems. The work of the transaction is not visible to other
transactions until commit, locks are held until commit (blocking
other transactions), and the work of vacuum processes is
compromised because old versions of rows can't be cleaned up until
any transaction still able to see the rows commit.  So they add an
unconditional commit somewhere to ensure that this doesn't happen.

If the point where they put the unconditional commit can be reached
with either autocommit setting, the problem point is here:

http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.
html#commit%28%29

Note the documentation says:

| This method should be used only when auto-commit mode has been
| disabled.

| Throws:
|     SQLException - [...] if this method is called on a closed
|                    conection or this Connection object is in
|                    auto-commit mode

To solve this problem, the code like this:

    connection.commit();

would need to be changed to:

    if (!connection.getAutoCommit())
        connection.commit();

Believe it or not, I have heard of programmers who refuse to deal
with this conforming behavior because some other driver ignores the
API requirement.  Personally, I tend to think the API makes sense
because it makes it harder to accidentally use non-transactional
behavior when you really wanted transactional behavior; you have to
make the mistake in both places or the JDBC driver keeps you
honest.  The behavior required by the API catches some sloppy
programming and generates an error for it rather than letting
incorrect behavior go unnoticed.

The problem could be somewhere else in the client-side stack, but
since I have seen these issues I figured I should pass along the
info in hopes that it will help.

-Kevin