Transaction support in 6.5.3/JDBC

For the past few weeks we've been developing an open source Java
transaction monitor. Our RDBMS of choice is PostgreSQL and we're using
Peter's JDBC driver, which works for us.

In order to use JDBC inside a transaction server it must support an
interface beyond the basic commit/rollback for dealing with distributed,
shared and suspended transactions. We're using the XAResource interface
which implements X/Open XA compatible distributed transaction
capabilities.

While implementing X/Open XA support on top of the JDBC driver, I ran
into a few issues with the back end. I'm using PostgreSQL 6.5.3 on
RedHat 6.0, Java 1.2 and hacked the JDBC driver to show FE-BE
communication.

My questions so far are:

1. When a dead-lock occurs trying to update the same row from multiple
threads, there does not seem to be any timeout and both connections hang
forever. Is there any dead-lock detection in 6.5 and will there be on in
6.6 or 7.0?

2. In the event of such a dead-lock, the XA layer will attempt to
terminate one of the connections. Right now the only recourse is to
shutdown the connection forcefully, hoping that the transaction is
rolledback and all locks are released. Am I safe to assume that?

3. I would rather cancel the pending update/insert, and according to the
interface specs there is a way to recieve a pid/key on startup and use
it to cancel an operation in progress. However, at the end of the
authentication process that happens at startup, no pid/key are send to
the FE, nor does the BE acknowledge that a query can be made.

4. The XA layer is responsible for beginning and commiting the
transactions directly and does so by sending the proper
begin/commit/rollback commands to the BE. It is possible that anywhere
between a begin and commit/rollback the transaction could be commited or
rolledback and another transaction started in its place not through a
commit/rollback SQL command?

5. Is it possible to determine the state of the transation prior to
issuing a commit operation and determine whether the transaction will
commit or rollback once a commit is issued?

arkin



-- 
____________________________________________________________
Assaf Arkin                               arkin@exoffice.com
CTO                                  http://www.exoffice.com
Exoffice, The ExoLab Company             tel: (650) 259-9796