What does below exceptions standfor ? we are seeing the below errors randomly.

1) org.teiid.jdbc.TeiidSQLException: Transaction already associated with request. at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113) at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70) at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:618) at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:62) at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:533) at

1) May occur if you are using the TeiidDriver or a non-XA managed TeiidDataSource with a local connection and a transaction already exists on the thread. There will add an option in 8.1 for teiid to use any thread bound transaction it finds for execution with a local connection, but I'm not sure that will make it into 7.7.1. If you are not using socket connections, or you are using a managed TeiidDataSource, then there is an issue and we'd need more information to track it down.

2) That may be due to an earlier issue, which you would hopefully see earlier in the log, or if you are using transaction timeouts, the transaction may have already been invalidated due to timeout.

I suspect that Teiid connection objects can't be shared across threads. More precisely, connection objects can't be enlisted in two different transactions at once. Normally, an application wouldn't do this but since we need to maintain Teiid temporary tables across user requests, we are stuffing the connection object into the web session. Obviously, this opens us up to the potential that the same connection will be involved in multiple transactions.

If you think this is the problem, I've got two questions:

Do you think using XA connections will avoid the problem? We're planning to switch to XA connections to make use of Teiid local connections

I think you mentioned there's a JIRA ticket regarding global temp tables but I couldn't find it. Is there one?

Txn issues were mentioned in https://issues.jboss.org/browse/TEIID-1865 We may need to understand this more about what you are trying to do to know what is going on. Are you creating competing local connections (for example calling Connection.setAutoCommit(false)) in concurrent threads? This would be susceptible to a race condition if un-synchronized, but if you had a consistent notion of a unit-of-work across threads, then adding synchronization around the start would prevent starting a txn when one is already present.

Or is autoCommitTxn left at the default of DETECT and we are automatically creating a transaction under the covers for concurrent requests? If so, we're making an assumption in the engine that transactional work on a connection will be performed serially. You could either serialize transaction requests, or if autoCommitTxns are not needed, then you could work-around by using the OFF setting.

Switching to XA in a typical container managed scenario could address this issue if you set the pool to track by transaction, then usage of that connection under a transaction will be serialized. So the question here would be is the perfermance hit too great?

As for global temporary tables, I couldn't find the issue either. Can you create one? The major issues with implementing that feature with-in Teiid are with cluster safety/performance and consistency under concurrently modifying transactions. More could be done to simplify the scenario, such as delegating to an external db.

As for global temporary tables, I couldn't find the issue either. Can you create one? The major issues with implementing that feature with-in Teiid are with cluster safety/performance and consistency under concurrently modifying transactions. More could be done to simplify the scenario, such as delegating to an external db.

Created TEIID-2067 for global temp tables. From our point of view, materializing the temp tables in an external database (I imagine the mechanism would work similarly to external materialization) is desirable.

A bit of background: The only reason we're sharing the connection object between threads is to achieve a very limited version of global temp tables. In our web app, one user click causes a result set to materialize to a temp table. In another set of clicks, the user is browsing through the result set. Because temp tables are scoped to the Teiid session, we cannot use a typical connection pooling pattern. Instead, we put the connection object into the user's web session and try to apply proper synchronization when using it.

From the info in TEIID-1865 (which I had forgotten), I can see that our recent use of setAutoCommit(false) is probably the culprit here. From the data source definition, auto commit is true. When we populate the temp tables, we turn auto commit to false. Although I would like to turn auto commit off for the entire app, that is not feasible at the moment. Instead, I think we can leave auto commit on when populating the temp tables.

Looks like you are trying to store the application's data in the Teiid in form of temp table. Why not store the in application context or save locally some other way to avoid issues until the global tables feature is available.

Yes, that's exactly right. Originally, we wanted to use those temp tables for more than just browsing result sets including joining to other Teiid tables so the temp table approach within Teiid seemed ideal. Due to time constraints, we no longer need to join so keeping the data outside of Teiid becomes possible (although, undesirable).