Embedded Connections & Transactions

[sroberts]

I am using the Teiid Embedded Connection. I have some questions about how the embedded server is using the Connection Factory and how transactions work in the embedded environment.

I have a Transaction Manager set in my Embedded Configuration and auto commit set to false in my JDBC Connector. I now try to execute two inserts using the JDBC connection. The embedded server will request a teiid BasicConnection from my connection factory for each JDBC execution and close each teiid BasicConnection after the execution. When I finally commit/rollback my JDBC connection, my connection factory has open and closed multiple connections.

It seems that the server should use one teiid basic connection for a transaction. It is difficult to track open and closed teiid connections, commit/rollback, and dispose of the basic connections appropriately. I suppose an embedded server is presumed to have one user. The transaction scope of the JDBC connection is the transaction scope of the entire server. I can just commit/rollback every connection that the embedded server has created using my transaction manager. This is not the case in JBoss, requiring divergent implementations on for each server.

How are other people using the Embedded Server? Can the Embedded Server stand up in a production environment? Is it reasonable to expect to write one connector and translator for both JBoss and Teiid embedded? If it is, how can I overcome issues with transactions?

[shawkins]

Teiid is not looking to duplicate connection pool / container behavior in either the server (where we rely on JBossAS) or embedded form.  In embedded you are providing the connection factory and will therefore have to provide something that implements the desired transactional behavior.  In this case you're looking for something similar to the JBoss connection pool "track connections by transaction" option.  There are several open source connection pool projects that may fit your needs if you are not running in an app server environment that can provide similar functionality.

Steve

[sroberts]

Steve, thank you for your reply.

The use of connections was surprising. I was not expecting connections to open and close with every operation.

In my case, I have an existing database API that I want to incorporate into an existing JBoss server. I would also like to provide a JDBC interface for my APIs using Embedded Teiid. It looks like I will need two connectors, one for my JBoss server and another for the embedded server. My connector for the JBoss server will implement the standard Teiid connector and let the server handle connection pooling with the “track connections by transaction” option.

I have to write connection tracking and transactions myself for the embedded case.  I benefit from a single user, with a single connection that has a single transaction context. I can change my connection factory to use a singleton, or to recycle connections. Since my JDBC connection is the only process creating connections, a commit/rollback on my JDBC connection is a commit or rollback on my connection factory.

The solution doesn’t seem like a proper use of Teiid. Is there a better way to solve this problem? Is there a way to implement the connection tracking and transaction behavior through the embedded server instead of the Teiid connector? Ideally, I would use the same connector for both JBoss and embedded servers.

[shawkins]

>The use of connections was surprising. I was not expecting connections to open and close with every operation.

That is a standard way of interacting with pooled connections.  You rely on the pool to keep the connections open, perform the transaction enlistment, track by transaction, etc.

> The solution doesn’t seem like a proper use of Teiid. Is there a better way to solve this problem? Is there a way to implement the connection tracking and transaction behavior through the embedded server instead of the Teiid connector? Ideally, I would use the same connector for both JBoss and embedded servers.

The translator/ExecutionFatory should be the same in both cases - that is really the Teiid conern.  We aren't looking to elevate additional concerns into Teiid embedded.  What envrionement are you looking to run embedded in?  If you already have a refernece to a TransactionManager are the other facilities for creating XA connection pools?