I'm using PostgresSQL for a J2EE application with JBoss. The problem is as follows:
Sometimes, an update just hangs (it's always the same row, actually, it's seems to be the last row that needs to be updated), PostgreSQL just hangs. The Java-Thread that does the update is still runnable and not waiting for any monitors, so I don't think that's a JDBC issue.
In this situation, the postgres processes look like this:
$ ps auxww | grep ^postgres
postgres: stats buffer process
postgres: stats collector process
postgres: jboss jboss 127.0.0.1 idle in transaction
postgres: jboss jboss 127.0.0.1 UPDATE waiting
The two transactions that can be seen here both belong to the same JTA-Transaction, if I kill the first one (the "idle in transaction" one), I get a HeuristicRollbackException.
If I delete the last row manually, so that the this row gets inserted instead of updated, it hangs a row before.
Because PostgreSQL doesn't support nested transactions, I suppose that the idle transaction is locking the row for some strange reason. Also, after killing the process with the idle transaction, the waiting one will be completed, so it's quite sure, that it's actually the first transaction that holds the lock.
Any ideas what JBoss is doing there?
I hava exactly the same problems and can't find a solution.
I played arround with the settings in JBoss
and PostgreSQL but nothing helped solving my problem.
I am using JBoss 2.4.4 and PostgreSQL 7.2.
Did you get a solution to the problem in the mean time?