Thread: Modifying the concurrent transaction limit

I've been running into a problem with a multithreaded, long-running
processing application. The problem occurs when the number of open
transactions reaches the magic number of '50'. When this occurs, all
further database access blocks and the application has to be restarted.

I am using Hibernate with the simple JDBC Transaction Factory:

       <property name="transaction.factory_class">
            org.hibernate.transaction.JDBCTransactionFactory</property>

I keep track of the number of open concurrent transactions using a
static method on my hibernate session factory, and sure enough, any time
I see a 'stuck' application and check the logs, I will see:

2007.03.06 09:10:05 DEBUG SessionOwnerImpl
broker_zip_market_heat_quartiles subreport filler - Current trxn counter: 50

What I would like to know is:

A. Is there really a built-in limit to the number of concurrent
transactions?
B. Can this value be configured?

I have done what I can to try and ensure that these transactions are
cleaned up when processing completes, but sometimes garbage collection
does not occur soon enough and I accumulate 50 open transactions,
wedging the process. If there is something else that I should be doing
to avoid this type of problem, please suggest!

Thanks,
Jason

PS I am using v8.1 of the driver with Postgres 8.1.x.

Jason L. Buberel wrote:
> A. Is there really a built-in limit to the number of concurrent
> transactions?
> B. Can this value be configured?

There certainly isn't in PostgreSQL. There is a configurable limit on
the number of connections (max_connections in postgresql.conf), and
another limit on the number of two-phase commit transactions in prepared
state (max_prepared_transactions).

You'll need to look at your application and Hibernate...

> I have done what I can to try and ensure that these transactions are
> cleaned up when processing completes, but sometimes garbage collection
> does not occur soon enough and I accumulate 50 open transactions,
> wedging the process. If there is something else that I should be doing
> to avoid this type of problem, please suggest!

The usual pattern for closing resources is to release finite resources
manually in a finally-clause, instead of waiting for the garbage
collector to deal with it.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com