Re: Automatic transactions in SELECT...

On 02/09/2013 10:51 AM, Kevin Grittner wrote:
> 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
Hi Kevin,

Thanks for the detailed information in this excellent posting. The
clarifications you've provided will be very useful to us in our next
troubleshooting steps and anyone else that visits the list archives with
similar questions.

The compelling volume of information at this point should be enough to
convince our team to focus on the client side stack. Many thanks to
everyone who responded with insights and suggestions!

Best regards,

-Tom