Connection Pool/Transaction Problem

[chris1978]

Version: Smart GWT Power 2.3 – nightly 2010.10.17

I’m observing a strange behaviour on my glassfish test environment with connection pool. The following code is working fine in my Eclipse environment without connection pool and on the glassfish without connection pool too.

In the code I write some data in a oracle temporary table and fetch the data with standard SmartGWT features. To read the data from the temp table I have to use the same database transaction.

Is the code executed on a Glassfish application server with utilizing the connection pool I receive no data.

To my mind there are only two reasons for that problem:
-A different DB session is used
-A different transaction is used


ServerDMI Code:

public DSResponse fetch(DSRequest dsRequest) throws Exception {

SQLTransaction.startTransaction(dsRequest.rpc);
Connection c = SQLTransaction.getConnection(dsRequest.rpc);

//authentification call
AuthentificationDAO.login(c, userName, password);


//the next two lines must be in one transaction
//this procedure is writing some data over a plsql procedure 
//in a oracle temporary table
//on a commit oracle deletes the data
SSMTreeDAO.generateSsmTree(c);
// in the execute methode a fetch is executed on the temp table
DSResponse response = dsRequest.execute();

SQLTransaction.endTransaction(dsRequest.rpc);
return response;
}

Datasource:

<?xml version="1.0" encoding="UTF-8"?>
<DataSource ID="ssm_tree" serverType="sql" tableName="sdm$vi_web_ssm_tree"
	titleField="sst_name">
	<fields>
		<field name="sst_id" type="integer" primaryKey="true" />
		<field name="sst_parent_sst_id" type="integer" foreignKey="ssm_tree.sst_id"
			rootValue="1" />
		<field name="sst_name" type="text" />
	</fields>


	<operationBindings>
		<operationBinding operationType="fetch"> 
			<serverObject className="de.sdom.frontend.server.dmi.SSMTreeDMI"
				lookupStyle="new" />
			<serverMethod>fetch</serverMethod>
		</operationBinding>
	</operationBindings>
</DataSource>

Datasource with Connection-Pooling (Pool-Name: jdbc/cti_sdom):

<?xml version="1.0" encoding="UTF-8"?>
<DataSource  dbName="jdbc/cti_sdom" ID="ssm_tree" serverType="sql" tableName="sdm$vi_web_ssm_tree"
	titleField="sst_name">
	<fields>
		<field name="sst_id" type="integer" primaryKey="true" />
		<field name="sst_parent_sst_id" type="integer" foreignKey="ssm_tree.sst_id"
			rootValue="1" />
		<field name="sst_name" type="text" />
	</fields>


	<operationBindings>
		<operationBinding operationType="fetch"> 
			<serverObject className="de.sdom.frontend.server.dmi.SSMTreeDMI"
				lookupStyle="new" />
			<serverMethod>fetch</serverMethod>
		</operationBinding>
	</operationBindings>
</DataSource>

More information required?

[chris1978]

I have another test case and now the proof that there is an issue with the usage of the connection pool. I’m using a Glassfish application server with connection pool and an Oracle database. The connection pool is configured in the datasource xml through the property dbName.

Server-Side DMI:

public DSResponse remove(DSRequest dsRequest) throws Exception {

	SQLTransaction.startTransaction(dsRequest.rpc);
	Connection c = SQLTransaction.getConnection(dsRequest.rpc);
	String userName = AuthentificationMgr
			.getUserNameFromSession(getSession(dsRequest));
	String password = AuthentificationMgr
			.getUserPasswordFromSession(getSession(dsRequest));
	//oracle package call – code is flaw free ;-)
	AuthentificationDAO.login(c, userName, password);
BigDecimal logId = ConvServer.toBigDecimal(dsRequest.getCriteria().get(
				GeneratedFieldConsts.F_LOG_ID));
	// simple sql delete statement – code is flaw free ;-)
	LoginDAO.deleteLoginSkillForLogId(c, logId);
	DSResponse dsResponse = dsRequest.execute();
	SQLTransaction.commitTransaction(dsRequest.rpc);

	return dsResponse;
}

In the „dsRequest.execute“ part a different connection is internally used than provided through “SQLConnection.getConnection”. I see this in the database session explorer. In this case the issue is causing a database deadlock.

If I switch off the connection pool and use the database over the server.properties the code is working fine.


With the usage of the connection pool the application is way more snappier. Could you provide some help?

Regards
Chris

[Isomorphic]

Can you clarify:

1) what are your server.properties settings? Are you using JNDI, Isomorphic-provided pooling, both at once?

2) how are you detecting that some other connection is being used aside from that returned from the pool?

3) for what SQL query is this other connection used?

4) what specifically do you mean by a "database deadlock" - are you seeing this external connection unable to complete a query because it's outside of the current transaction, or something like that?

[chris1978]

1) server.properties

webRoot: __AUTODETECT__
  
apps.adminConsole.location: $webRoot/frontend/tools
ui.adminConsole.location: $webRoot/frontend/tools


project.datasources: $webRoot/ds
project.ui: $webRoot/shared/ui
project.apps: $webRoot/shared/app


RPCManager.enabledBuiltinMethods: xmlToJS, uploadProgressCheck, downloadClientExport
sql.Oracle.driver.serverName: ***********
sql.Oracle.driver.portNumber: ***********
sql.Oracle.driver.databaseName: *****
sql.Oracle.driver.networkProtocol: tcp
sql.Oracle.driver: oracle.jdbc.pool.OracleDataSource
sql.Oracle.database.supportsSQLLimit: false
sql.Oracle.interface.type: dataSource
sql.Oracle.interface.credentialsInURL: true
sql.Oracle.pool.enabled: true
sql.Oracle.database.type: oracle
sql.Oracle.driver.context: _container_
sql.Oracle.driver.driverType: thin
sql.Oracle.driver.user: cti_sdom
sql.Oracle.driver.password: ***********

In the Glassfish environment I'm using a JNDI connection pool, but the above settings are still in the property file. I'm control the usage of the JNDI connection pool over the dbName property in the datasource.xml. I'm using in all datasource xml files the same JNDI connection pool.

If I omit the dbName property the above named settings are used.
The configuration of the JNDI connection pool of the adminConsole is due to bug in the console not possible.


2)/3) The problem ist not that a connection is used aside from the pool, but rather that in one request cycle a different connection (from pool) is used.

I observed the usage of different connections in a database session browser (Quest TOAD). I could see that the statements [a)-3 / a)-4] was executed in different database sessions. This is not the case if I omit the dbName from the datasource xml files.

Cases:
For both cases it's necessary that all actions executed within the same connection/transactions.

a)
1. start transaction - get connection
2. authentification db package call
3. delete / update - sql statements
4. dsRequest.execute()
5. end transaction

b)
1. start transaction - get connection
2. authentification db package call
3. package call which writes data in a oracle temp table
4. dsRequest.execute() (read temp table)
5. end transaction


4) Excactly as you said. Two dependent delete statements in different database connections (thus transactions).


You have written about an Isomorphic-provided connection pool. How can I configure settings like min/max pool size. As you see in the server.properties the pool should be enabled, but there are no connections pooled by default.
(Checked through TOAD)

Regards
Chris

[Isomorphic]

There was a JNDI-related bug in Admin Console but it's been corrected for a while and your build has the fix.

When you specify dbName in your .ds.xml file as "jdbc/cti_sdom", that means you are using JNDI. So what you seem to be saying is that when using JNDI via a dbName setting in .ds.xml, there is a problem with using the same connection throughout the transaction?

Can you try two things:

1) don't manually initiate the transaction (remove startTransaction() / commitTransaction() calls). It's automatic, so there's no need, but this might interfere with pooling

2) use the Admin Console to configure JNDI such that your JNDI settings appear in server.properties as the default database, and dbName can be removed from the .ds.xml while still causing it to use JNDI

[chris1978]

Thank's for your fast reply.

2)

The Admin Console is working now as you expected. I removed the dbName property from the datasource xml files and still using the JNDI connection pool.

But the problem is still there.

1)

I need a connection instance so that I can execute my custom statements.
If I don't initiate the transaction with startTransaction, the result of SQLTransaction.getConnection is NULL.

SQLTransaction.startTransaction(dsRequest.rpc);
Connection c = SQLTransaction.getConnection(dsRequest.rpc);

Therefore it's not possible for me to omit the startTransaction besides you can provide a different way to access the connection instance?

[chris1978]

Hi,

do you have some new informations about this issue? I would appreciate any help, because to go without the connection pool is a drastically performance loss.


I would like to try the isomorphic pool, but how can I set up the min/max pool size?

Regards
Chris

EDIT:
I was able to reproduce the issue in a simple Tomcat 6 environment. It's nothing Glassfish specific.

[Isomorphic]

Somebody is looking into it - please bear with us

[Isomorphic]

You say you can reproduce this in a Tomcat environment - please can you give more details? The pooling library used, additional entries required in web.xml or server.xml or a config file in /conf, what your server.properties looks like, etc. It will save time if we can set up exactly like you when trying to reproduce.

[chris1978]

Attached you'll find the files for the tomcat configuration. I'm using a plain configuration without specialities.

Regards
Chris

[chris1978]

It seems that I found a workaround for me. I’m utilizing the property serverConstructor in the datasource xml.

I extended the class SQLDataSource and override the methods “getConnection” and “getTransactionalConnection”.

public class PoolableDataSource extends SQLDataSource {


	@Override
	public Connection getConnection() throws Exception {
		Connection c = ConnectionThreadHelper.getConnectionFromThread();

		return c;
	}

	@Override
	public Connection getTransactionalConnection(DSRequest arg0)
			throws Exception {
		Connection c = ConnectionThreadHelper.getConnectionFromThread();

		return c;
	}

}

At the beginning of the call flow (IDACall Servlet) a connection is created with help of the class SQLTransaction. The created connection is stored within the current thread (ThreadLocal). At the end of the call flow the connection is closed.

SQLTransaction.startTransaction(dsRequest.rpc);
Connection c = SQLTransaction.getConnection(dsRequest.rpc);
ConnectionThreadHelper.saveConnectionInThread(c);

Do you see any side effects with this approach?

[Isomorphic]

That's the essence of what the built-in transaction-handling code is doing.

Having done some experiments with your config files in a plain Tomcat 6 environment, it seems that the problem is happening because the JNDI name is being used directly in the sql.defaultDatabase property; the SQL subsystem is able to auto-configure and use the JNDI datasource, but doesn't recognize it as something that might participate in a transaction. We'll look into addressing that, but in the meantime please can you remove this line from your server.properties file:

sql.defaultDatabase: jdbc/cti_sdom

And add these lines:

sql.defaultDatabase: cti_sdom

sql.cti_sdom.driver.driverType: thin
sql.cti_sdom.database.type: oracle
sql.cti_sdom.interface.type: jndi
sql.cti_sdom.driver.name: /jdbc/cti_sdom
sql.cti_sdom.autoJoinTransactions: true

And let us know if that solves the problem for you? Please note that your .ds.xml file should not include a dbName property (or, if it does, it should be "cti_sdom" rather than a JNDI name)

Thanks,
Isomorphic Software Support

[chris1978]

This new configuration solved my problem.

Thanks for your help

Chris

[chris1978]

In the nightly 24.11.2010 the problem appears again, but now in a simple Oracle configuration:

sql.Oracle.driver.serverName: 10.100.146.185
sql.Oracle.driver.portNumber: 51521
sql.Oracle.driver.databaseName: xxxx
sql.Oracle.driver.user: xxxx
sql.Oracle.driver.password: xxxx
sql.Oracle.driver.networkProtocol: tcp
sql.Oracle.driver: oracle.jdbc.pool.OracleDataSource
sql.Oracle.database.supportsSQLLimit: false
sql.Oracle.interface.type: dataSource
sql.Oracle.interface.credentialsInURL: true
sql.Oracle.pool.enabled: true
sql.Oracle.database.type: oracle
sql.Oracle.driver.context: _container_
sql.Oracle.driver.driverType: thin

[Isomorphic]

This will be happening because we very recently changed the default TransactionPolicy from ALL to ANY_CHANGE, so by default we now only construct a transaction if there is an update operation in the queue. There are a few ways to change this - on the DataSource or the OperationBinding, for example - but the quickest and easiest way to do it is to add this line to your database config in server.properties:

sql.Oracle.autoJoinTransactions: true