I have a situation and would like to ask for your attention...
Environment
JEE application running under JBossAS and using PostgreSQL 8.4.3 as backend. In some transactions we use JMS queues and read/write database operations. To have atomic transactions, we use 2PC/XA protocol that controls the distributed commit/rollback across the resources. The database connection is done through a xa-datasource set to use org.postgresql.xa.PGXADataSource, which is the specific driver class for distributed transactions.
Issue
Sometimes i notice that some "Prepared Transactions" are dead in the database without a commit/rollback operation, that is, from the "pg_prepared_xacts" view i can see that the transaction is very old, it is not a very long running transaction, i am sure. Checking another view, "pg_locks", i can see the transaction in a "ShareLock" mode and, associated with the same PID, another transaction in a "ExclusiveLock" mode. These transactions use some database registers and keep these registers in this lock state forever, preventing another new transaction to use these same registers. As we have a Prepared Transactions blocked without a commit or rollback, the new transactions stay waiting forever for the lock release. I see, through a "ps" Linux command, many Postgres process with a "waiting" status.
As we have a minimum occurrence of this issue, i was not able to identify the real reason yet. Maybe it is bug related to my software, in Postgres, in the driver version or even a specific Postgres configuration.
Nowadays, we have to perform a "ROLLBACK PREPARED" to clean the "dead" Prepared Transaction, but it is not the right approach.
Do you have any tips or ideas about this situation?