RE: [INTERFACES] Transaction support in 6.5.3/JDBC - Mailing list pgsql-interfaces

Due to having to suffer MS Outlook here at work, my comments are
prefixed with PM :-)

Peter

-- 
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.



-----Original Message-----
From: Assaf Arkin [mailto:arkin@exoffice.com]
Sent: Wednesday, December 08, 1999 1:08 AM
To: pgsql-interfaces@hub.org
Subject: [INTERFACES] 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?

PM: I'm not sure if there is/will be in the backend. I'm planning on
making the OOB abort stuff working for 7.0, but I'm not certain on
dead-lock's.

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?

PM: Someone correct me if I'm wrong, but the transaction should roll
back if the connection is closed whilst it's open, regardless of the
interface.

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.

PM: Do you mean the pid of the backend running a particular connection?
If so, I can see some security headaches if one connection can cancel an
operation running on another. As for the BE not acknowledging that a
query can be made, thats just how the current protocol works.

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?

PM: JDBC based code should never issue begin/commit/rollback commands,
and should use the similarly named methods in Connection. This is
because a JDBC driver could be issuing these statements internally, and
it would be confused. With our driver, you could currently get away with
it, but it's not guaranteed to stay that way.

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?

PM: Hmmm, in theory if a transaction is in a dead state (ie: an SQL
statement failed, so anything else is ignored until the rollback), there
should be a message in the notify queue. Our JDBC driver keeps these in
the warnings queue, so you could read them prior to calling commit()
yourself.

arkin



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

************