GeoTools

Allow transactions to work against an externally provided connection

Details

  • Type: Improvement Improvement
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 2.5.3
  • Fix Version/s: 2.5.9
  • Component/s: jdbc
  • Labels:
    None

Description

When the jdbc datastore are integrated with a larger application working against a database we have the case in which the external app is in control of the connection pool and the transaction is controlled outside.
The current jdbc-ng code already avoids grabbing a connection and closing it provided the current transaction already has a connection associated to it.
In the case of external control it would be nice to have a method like createJ2eeTransaction(Connection cx) that has the following features:

  • builds a transaction with a user provided connection
  • never tries to grab another connection for methods that do get the transaction as a param, and from all the methods in FeatureSource/FeatureStore provided the connection is set
  • never closes the connection manually (this is already happening provided the transaction is not AUTO_COMMIT)
  • never calls directly commit/rollback
    The latter can be achieved if we build a special transaction that never actually calls commit/rollback on the connection. Actually, it would be sufficient that the user never manually calls commit/rollback on the transaction, but let's try to be extra sure about it.

From what I see this would be sufficient to perform an integration into a J2EE enviroment where the pool and transaction lifecycles are controlled by a container (provided there is a way to get the current transactions's connection, something that can be done with Spring, not sure with standard J2EE).
Of course it would also be good for any app that has its own (eventually manual) transaction demarcation and connection pool handling

Issue Links

depends upon

Bug - A problem which impairs or prevents the functions of the product. GEOT-2009 Fix transaction and connection handling in JDBCDataStore

  • Major - Major loss of function.
  • Closed - The issue is considered finished, the resolution is correct. Issues which are not closed can be reopened.

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Christian Mueller added a comment -
I am happy to see this issue, if you can remember, we had some discussions some months ago.

I want to mention some facts.

1) 2 different scenarios

  a) We have jdbc connections managed by a global JDBC transaction
     (In an EJB container and, if supported, in a Servlet Container)
  b) We have standalone jdbc connections managed by usercode, not participating in any global Transaction.
    (in an EJB container , in a servlet container, in a java program,....)
     
  For a) it is forbidden to use commit/rollback, for b) user code is responsible for commit/rollback

2) We can ignore the global transaction, it is of no interest for jdbc-ng. If jdbc-ng has an error condition, throw
  a checked exception (I think we do this). Never throw an unchecked exception because when this exception crosses the container
  boundary, a rollback of the global transaction is executed (Uaaaaaaaaahhhhhh).

  Btw, leaving the container with a checked exception causes a commit on the global transaction. E. g. passing an SQLException
  from the ejb container to the servlet engine does a commit on the global transaction. This is a common error by j2ee programmers,
  they have to use EJBContext>>setTransactionRollbackOnly before leaving the EJB container.

3) Andrea, you wrote

  "provided there is a way to get the current transactions's connection, something that can be done with Spring, not sure with standard J2EE".

   No, there is no way to get the current transactions's connection (The global transaction may have many jdbc connections) .
   Think the other way around, a connection has an associated jdbc transaction and may be enrolled in a global transaction ,
   ok, but these transactions are not of interest for jdbc-ng.

4) A good EJB Design avoids having statefull EJBs. I should be possible to create a JDBCFeatureStore (Source) and reuse
    it with different jdbc connections. I am not sure if we can handle this.
Show
Christian Mueller added a comment - I am happy to see this issue, if you can remember, we had some discussions some months ago. I want to mention some facts. 1) 2 different scenarios   a) We have jdbc connections managed by a global JDBC transaction      (In an EJB container and, if supported, in a Servlet Container)   b) We have standalone jdbc connections managed by usercode, not participating in any global Transaction.     (in an EJB container , in a servlet container, in a java program,....)         For a) it is forbidden to use commit/rollback, for b) user code is responsible for commit/rollback 2) We can ignore the global transaction, it is of no interest for jdbc-ng. If jdbc-ng has an error condition, throw   a checked exception (I think we do this). Never throw an unchecked exception because when this exception crosses the container   boundary, a rollback of the global transaction is executed (Uaaaaaaaaahhhhhh).   Btw, leaving the container with a checked exception causes a commit on the global transaction. E. g. passing an SQLException   from the ejb container to the servlet engine does a commit on the global transaction. This is a common error by j2ee programmers,   they have to use EJBContext>>setTransactionRollbackOnly before leaving the EJB container. 3) Andrea, you wrote   "provided there is a way to get the current transactions's connection, something that can be done with Spring, not sure with standard J2EE".    No, there is no way to get the current transactions's connection (The global transaction may have many jdbc connections) .    Think the other way around, a connection has an associated jdbc transaction and may be enrolled in a global transaction ,    ok, but these transactions are not of interest for jdbc-ng. 4) A good EJB Design avoids having statefull EJBs. I should be possible to create a JDBCFeatureStore (Source) and reuse     it with different jdbc connections. I am not sure if we can handle this.
Hide
Permalink
Andrea Aime added a comment -
Christian, the whole jdbc-ng module revolves around the availability of a connection object, so if we cannot get one, there is no hope to ever integrate jdbc-ng in a J2EE enviroment.
Reading on the internet the very few scraps of information related to using low level JDBC in a J2EE enviroment it seems all you have to do is to grab the connection from an XA aware DataSource, and that connection will participate into the current global transaction. The only thing we need to add is to pay attention and never call manually commit/rollback.

If this is true, working in a J2EE enviroment is even easier than working in an enviroment in which the connection is managed by an external, non J2EE application. Apparently all we have to do is:
- setup the JDBCDataStore with a container provided, XA aware DataSource (this can be already done today coding things by hand, we just need to add a factory)
- add a flag to JDBCDataStore that avoids direct calls to commit/rollback. This basically means the JDBCTransactionState class will have to be altered with a flag that forbids
  the call to such methods

And that should be it...
Show
Andrea Aime added a comment - Christian, the whole jdbc-ng module revolves around the availability of a connection object, so if we cannot get one, there is no hope to ever integrate jdbc-ng in a J2EE enviroment. Reading on the internet the very few scraps of information related to using low level JDBC in a J2EE enviroment it seems all you have to do is to grab the connection from an XA aware DataSource, and that connection will participate into the current global transaction. The only thing we need to add is to pay attention and never call manually commit/rollback. If this is true, working in a J2EE enviroment is even easier than working in an enviroment in which the connection is managed by an external, non J2EE application. Apparently all we have to do is: - setup the JDBCDataStore with a container provided, XA aware DataSource (this can be already done today coding things by hand, we just need to add a factory) - add a flag to JDBCDataStore that avoids direct calls to commit/rollback. This basically means the JDBCTransactionState class will have to be altered with a flag that forbids   the call to such methods And that should be it...
Hide
Permalink
Christian Mueller added a comment -

1) We can agree on the fact that we need a mechanism to avoid jdbc commit and rollbacks --> 0K
2) If jdbc-ng gets connections from an XA Data Source and 1) is assured , anything is ok, the global transaction does the job -->OK

But we get problems when not using connections from XA Data Sources.

Scenario:

1) User code gets a connection from a none XA Datasource or the Driver Manager
2) User code manipulates some tables
3) User code uses jdbc.ng and manipulates features
4) User code wants to do a commit/rollback for actions done in 2) and 3)

I think we have to make it possible to pass a jdbc connection to jdbc-ng and prevent jdbc-ng from creating or closing connections (As we plan to do it with commit/rollback).

Andrea, do you agree on this scenario ?

christian














Show
Christian Mueller added a comment - 1) We can agree on the fact that we need a mechanism to avoid jdbc commit and rollbacks --> 0K 2) If jdbc-ng gets connections from an XA Data Source and 1) is assured , anything is ok, the global transaction does the job -->OK But we get problems when not using connections from XA Data Sources. Scenario: 1) User code gets a connection from a none XA Datasource or the Driver Manager 2) User code manipulates some tables 3) User code uses jdbc.ng and manipulates features 4) User code wants to do a commit/rollback for actions done in 2) and 3) I think we have to make it possible to pass a jdbc connection to jdbc-ng and prevent jdbc-ng from creating or closing connections (As we plan to do it with commit/rollback). Andrea, do you agree on this scenario ? christian
Hide
Permalink
Andrea Aime added a comment -
Hum, I'm having troubles understanding what you mean in your last comment.
Let's try a summary.

1) Normal operation
The datastore has its own DataSource, prepared by the factory or provided by the user, and it's free to call commit/rollback on these. Exactly what we do today.

2) J2EE operation
The datastore is built with a XA DataSource, a flag is raised so that the commit/rollback methods are never called. This does not seem to present any issue either

3) Working off client code provided connections
The user has an application with its own connection handling code, and wants the datastore to use the same connection he's already using in the rest of the code. For this we expose a DataStore.buildTransaction(Connection) that builds a transaction around the user provided connection instead of grabbing the connection from the JDBCDataSTore DataSource (which must be there anyways, since we use it for metadata).
In this case we just bypass the normal connection grabbing code. commit/rollback do not need to be inhibited, since the Transaction object returned by buildTransaction is under the control of the user, that is free to decide wheter to call commit/rollback directly, or not.

Does this make sense?
Show
Andrea Aime added a comment - Hum, I'm having troubles understanding what you mean in your last comment. Let's try a summary. 1) Normal operation The datastore has its own DataSource, prepared by the factory or provided by the user, and it's free to call commit/rollback on these. Exactly what we do today. 2) J2EE operation The datastore is built with a XA DataSource, a flag is raised so that the commit/rollback methods are never called. This does not seem to present any issue either 3) Working off client code provided connections The user has an application with its own connection handling code, and wants the datastore to use the same connection he's already using in the rest of the code. For this we expose a DataStore.buildTransaction(Connection) that builds a transaction around the user provided connection instead of grabbing the connection from the JDBCDataSTore DataSource (which must be there anyways, since we use it for metadata). In this case we just bypass the normal connection grabbing code. commit/rollback do not need to be inhibited, since the Transaction object returned by buildTransaction is under the control of the user, that is free to decide wheter to call commit/rollback directly, or not. Does this make sense?
Hide
Permalink
Christian Mueller added a comment -
Yep, these are the 3 scenarios.

Unfortunately, the questions do not end here. ( I know I am annoying)

4) Is jdbc-ng thread-safe ? I think not because of the transaction object. Each thread has to create his own FeatureStore(FeatureSource).
As you know, the situation of multiple threads is common in a server environment.

5) If 4) holds true, can we assure to build the meta information only once, implementing a thread safe caching mechanism. Otherwise
each thread(request) would execute the whole meta information stuff.


Show
Christian Mueller added a comment - Yep, these are the 3 scenarios. Unfortunately, the questions do not end here. ( I know I am annoying) 4) Is jdbc-ng thread-safe ? I think not because of the transaction object. Each thread has to create his own FeatureStore(FeatureSource). As you know, the situation of multiple threads is common in a server environment. 5) If 4) holds true, can we assure to build the meta information only once, implementing a thread safe caching mechanism. Otherwise each thread(request) would execute the whole meta information stuff.
Hide
Permalink
Andrea Aime added a comment -
Christian, sorry for the delay, I'm swamped with other work.
So, jdbc-ng is not thread safe and cannot be, as you point out, because jdbc connections are not.
The way datastores are designed, we can only partially work out with just the user provided transaction, the datastore will have to get a hold of a connection pool to be used to get extra connections anyways. FeatureSource/Feature store are covered, they own a transactio, but all methods of DataStore that do not get a transaction as a parameter will need to grab extra connections, unless we expand the API of JBDCDataStore and provide clones of those methods that to get a transaction as a parameter.

Building the meta information "only once" is not a good policy, because the table structure can change on your back (pretty common in GeoServer when people are setting up data, especially empty tables that are meant to be populated via WFS-T). Getting it every time is a bad idea too, as building this information is pretty expensive.
So what we need is a thread safe cache, but one that has expiring built in, and we probably want to expose some methods to allow the user to forcefully wipe out the cache contents for a certain feature type.

So, just to dump an initial set of extra methods we'd need to expose, we should add:
- JDCBDataStore.getTypeNames(Transaction)
- JDCBDataStore.getSchema(Transaction)
- JDCBDataStore.getFeatureSource(String, Transaction)
- Transaction JDBCDataStore.buildUserTransaction(Connection conn) (builds a transaction with the user provided connection)

An alternative that _might_ work is to provide the datastore a fake data soruce that always returns the same user provided connection.
Upside, no api changes. Downsides, the datastore would no more be a reusable object, every thread should build his own based on the j2ee connection/transaction associated to the thread. So expect slowdowns due to lack of caching.

Ideas, preferences?
Show
Andrea Aime added a comment - Christian, sorry for the delay, I'm swamped with other work. So, jdbc-ng is not thread safe and cannot be, as you point out, because jdbc connections are not. The way datastores are designed, we can only partially work out with just the user provided transaction, the datastore will have to get a hold of a connection pool to be used to get extra connections anyways. FeatureSource/Feature store are covered, they own a transactio, but all methods of DataStore that do not get a transaction as a parameter will need to grab extra connections, unless we expand the API of JBDCDataStore and provide clones of those methods that to get a transaction as a parameter. Building the meta information "only once" is not a good policy, because the table structure can change on your back (pretty common in GeoServer when people are setting up data, especially empty tables that are meant to be populated via WFS-T). Getting it every time is a bad idea too, as building this information is pretty expensive. So what we need is a thread safe cache, but one that has expiring built in, and we probably want to expose some methods to allow the user to forcefully wipe out the cache contents for a certain feature type. So, just to dump an initial set of extra methods we'd need to expose, we should add: - JDCBDataStore.getTypeNames(Transaction) - JDCBDataStore.getSchema(Transaction) - JDCBDataStore.getFeatureSource(String, Transaction) - Transaction JDBCDataStore.buildUserTransaction(Connection conn) (builds a transaction with the user provided connection) An alternative that _might_ work is to provide the datastore a fake data soruce that always returns the same user provided connection. Upside, no api changes. Downsides, the datastore would no more be a reusable object, every thread should build his own based on the j2ee connection/transaction associated to the thread. So expect slowdowns due to lack of caching. Ideas, preferences?
Hide
Permalink
Christian Mueller added a comment -
Do not bother about the delay, I see your work on the mailing lists, puuuuuuuhhhhhhhh

Fist, the easy one.

1) I agree on the tread safe cache, the most simple approach is to access the cache in a "synchronized" manner, providing a "reload" method as an API.
If we do it more intelligent, we should use the the lock possibilities given by Java 5, (I mean the concurrent packages) and introduce read and write locks.
I would prefer the intelligent one :-)

2) I would not use a fake datastore, no good design.

I did a deeper look into the JDBCFeatureXXXX stuff including transaction handling and state. To tell the truth, I got confused. Making these classes J2EE/user transaction ready would complicate the code (to write and to understand) and would enlarge the API (which method should I use).
I think we would produce something like a Gordian knot.

If I would have to resolve this issue, I would split some of the JDBCFeatureXXXX classes into to 2 classes, one for the state and one for the logic.

e.g

create a new class JDBCFeatureSourceStateless

This class must meet the following requirements

a) No knowledge of state and transactions objects
b) No JDBC getConnection, close, commit or rollback calls

Of course, the JDBC connection must be passed in as a parameter.


Afterwards put the logic from JDBCFeatureSource to JDBCFeatureSourceStateless, modifying the logic to use JDBC connections.

Modify JDBCFeatureSource to handle the state and rely on JDBCFeatureSourceStateless for the logic.

The result should be:

- Leaving JDBCFeatureStore API and behavior as it is
- Having a JDBCFeatureSourceStateless class which can be used for user or container managed transactions
- Avoiding the Gordian knot (-:

That is the way I would ride the attack.









Show
Christian Mueller added a comment - Do not bother about the delay, I see your work on the mailing lists, puuuuuuuhhhhhhhh Fist, the easy one. 1) I agree on the tread safe cache, the most simple approach is to access the cache in a "synchronized" manner, providing a "reload" method as an API. If we do it more intelligent, we should use the the lock possibilities given by Java 5, (I mean the concurrent packages) and introduce read and write locks. I would prefer the intelligent one :-) 2) I would not use a fake datastore, no good design. I did a deeper look into the JDBCFeatureXXXX stuff including transaction handling and state. To tell the truth, I got confused. Making these classes J2EE/user transaction ready would complicate the code (to write and to understand) and would enlarge the API (which method should I use). I think we would produce something like a Gordian knot. If I would have to resolve this issue, I would split some of the JDBCFeatureXXXX classes into to 2 classes, one for the state and one for the logic. e.g create a new class JDBCFeatureSourceStateless This class must meet the following requirements a) No knowledge of state and transactions objects b) No JDBC getConnection, close, commit or rollback calls Of course, the JDBC connection must be passed in as a parameter. Afterwards put the logic from JDBCFeatureSource to JDBCFeatureSourceStateless, modifying the logic to use JDBC connections. Modify JDBCFeatureSource to handle the state and rely on JDBCFeatureSourceStateless for the logic. The result should be: - Leaving JDBCFeatureStore API and behavior as it is - Having a JDBCFeatureSourceStateless class which can be used for user or container managed transactions - Avoiding the Gordian knot (-: That is the way I would ride the attack.
Hide
Permalink
Andrea Aime added a comment -
Christian, I might get some funding to get this one moving forward, but only partially, there are not nearly enough funds to sponsor the refactoring you proposed above.

What I can get sponsoring for is to add a

JDBCDataStore.buildTransaction(Connection) -> GT2 Transaction

that would allow FeatureStore to work with a user provided connection (that of course would have its own transaction management, we would never call commit/rollback on the connection in that case).

Yet the datastore would still have to be built with its own DataSource allowing it to use it for grabbing metadata, that is, the list of feature types and the structure of each feature type.

It looks like a step forward anyways.
Show
Andrea Aime added a comment - Christian, I might get some funding to get this one moving forward, but only partially, there are not nearly enough funds to sponsor the refactoring you proposed above. What I can get sponsoring for is to add a JDBCDataStore.buildTransaction(Connection) -> GT2 Transaction that would allow FeatureStore to work with a user provided connection (that of course would have its own transaction management, we would never call commit/rollback on the connection in that case). Yet the datastore would still have to be built with its own DataSource allowing it to use it for grabbing metadata, that is, the list of feature types and the structure of each feature type. It looks like a step forward anyways.
Hide
Permalink
Christian Mueller added a comment -
Yes, it is a step forward and it does not prevent the refactoring I proposed.

I have configured a virtual machine hosting db2,mysql,oracle and postgis. I will try to get an sqlserver running. My plan is to have online tests for all jdbc-ng modules. After this, we will talk about the refactoring.

If I can support you, please tell me.
Show
Christian Mueller added a comment - Yes, it is a step forward and it does not prevent the refactoring I proposed. I have configured a virtual machine hosting db2,mysql,oracle and postgis. I will try to get an sqlserver running. My plan is to have online tests for all jdbc-ng modules. After this, we will talk about the refactoring. If I can support you, please tell me.
Hide
Permalink
Andrea Aime added a comment -
Here is a patch that, building on the work that fixes transaction handling in GEOT-2009, add a method allowing the user to provide an external connection, and to also control it directly: no call to commit/rollback/close will ever be made on the connection. Added a test case proving this works.

The datastore still needs a reference to a datasource to carry on its basic functinoalities, such as listing feature types and the like. Allowing those methods to work against an external connection is out of scope for this jira, but can be tackled in another one.
Show
Andrea Aime added a comment - Here is a patch that, building on the work that fixes transaction handling in GEOT-2009 , add a method allowing the user to provide an external connection, and to also control it directly: no call to commit/rollback/close will ever be made on the connection. Added a test case proving this works. The datastore still needs a reference to a datasource to carry on its basic functinoalities, such as listing feature types and the like. Allowing those methods to work against an external connection is out of scope for this jira, but can be tackled in another one.
Hide
Permalink
Andrea Aime added a comment -
Ah, btw, if you're not familiar applying git diffs, it's just like an svn generate one, but it has an extra element in the path that can be ignored using "patch -p1" on the command line, or asking Eclipse to do so when applying the patch
Show
Andrea Aime added a comment - Ah, btw, if you're not familiar applying git diffs, it's just like an svn generate one, but it has an extra element in the path that can be ignored using "patch -p1" on the command line, or asking Eclipse to do so when applying the patch
Hide
Permalink
Christian Mueller added a comment -
Applied the patch on trunk, DB2 tests are fine.
Show
Christian Mueller added a comment - Applied the patch on trunk, DB2 tests are fine.
Hide
Permalink
Andrea Aime added a comment -
This patch was actualy commited over a year ago, closing
Show
Andrea Aime added a comment - This patch was actualy commited over a year ago, closing

People

  • Assignee:
    Andrea Aime
    Reporter:
    Andrea Aime
Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved: