Thread: RE: [INTERFACES] Transaction support in 6.5.3/JDBC
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 ************
Peter Mount <petermount@it.maidstone.gov.uk> writes:
> 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.
This doesn't seem right to me --- the backend should detect deadlocks
(not right away, but within a few seconds or a minute at most). If
it doesn't, that's not JDBC's fault. More details please?
> 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.
Right. Again, this'd be a backend bug if it didn't happen. The backend
isn't supposed to rely on correct frontend behavior to ensure database
integrity.
> 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.
This one maybe can be blamed on JDBC. In the 2.0 protocol the BE
will send a cancel security key --- but I seem to recall that the
JDBC client still requests protocol 1.0?
regards, tom lane
> 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. Inside a transaction, the application should not even use commit/rollback on the JDBC connection, only through the transaction monitor API. This is easy to solve, I simply return a ClientConnection wrapper that prevents that. But someone can still send a commit/rollback statement directly through the JDBC driver. What I'm more afraid of is some operation that will cause a commit/rollback to occur, e.g. a failed update, a trigger or stored procedure. > 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. Thanks I'll try to look that out. I've minimized all the special requirements I need from the driver to three methods calls: 1. enbleSQLTransactions -- prevents a commit/rollback from being executed directly in SQL; you can never be too careful ;-) 2. prepare -- should return false if the transaction is read-only, true if it will commit, throw an exception if it will rollback 3. isCriticalError -- should tell me if a critical error occured in the connection and the connection is no longer useable How do I detect no. 3? Is there are certain range of error codes, should I just look at certain PSQLExceptions as being critial (e.g. all I/O related errors)? arkin > > arkin > > -- > ____________________________________________________________ > Assaf Arkin arkin@exoffice.com > CTO http://www.exoffice.com > Exoffice, The ExoLab Company tel: (650) 259-9796 > > ************ > > ************ -- ____________________________________________________________ Assaf Arkin arkin@exoffice.com CTO http://www.exoffice.com Exoffice, The ExoLab Company tel: (650) 259-9796
> 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. >From what I gather if an SQL statement failed, the CompletedResponse will show *ABORT STATUS*. That's an easy modification. I'm not sure about a warning being sent, I need to figure how to test this one. The BE also returns the name of the cursor. I assume this one should be returned from ResultSet.getCursorName()? arkin > > arkin > > -- > ____________________________________________________________ > Assaf Arkin arkin@exoffice.com > CTO http://www.exoffice.com > Exoffice, The ExoLab Company tel: (650) 259-9796 > > ************ > > ************ -- ____________________________________________________________ Assaf Arkin arkin@exoffice.com CTO http://www.exoffice.com Exoffice, The ExoLab Company tel: (650) 259-9796