Re: Automatic transactions in SELECT...

On 02/09/2013 02:07 AM, Thomas Kellerer wrote:
> Tom Lisjac wrote on 09.02.2013 03:16:
>> We're in the process of deploying 9.2.1 to support an internal
>> application we've been working on for the last few months. One of our
>> programmers has recently complained that we get occasional hangs because
>> "postgres automatically runs SELECT statements in a transaction that
>> requires an explicit commit".
>
> This behaviour is true for most (if not all) DBMS - defintely for
> Oracle, SQL Server and MySQL.
>
> If no transaction is active, _any_ (DML) statement (including SELECT)
> will start a new transaction.
> This can either be controlled via explicit transaction handling (BEGIN
> ... END) or the
> autocommit property of the connection.

From a little googling, I believe the behavior you've described is
called "implicit" or "autocommit" transactions. A previous posting said
that Postgres doesn't support this, but perhaps that was regarding the
"explicit commit" that I included in my description. I don't see a way
for a SELECT in such a transaction to generate a hang from the
descriptions of the autocommit connection property I've seen.

>
> What exactly are those "occasional hangs"? How do they manifest
> themselves?
>
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.

Thanks for your reply.

-Tom