issue with setting transaction isolation level to read-committed
Has any had issues when setting the transaction isolation level for a connection pool?
My environment is:
SJSAS 8.2
jdk 1.5.0_08
MySQL Connector J 3.1.13
MySQL 5.0.22-standard
OS: Windows XP (application server) and Solaris 10 (MySQL database)
Here is my connection pool configuration:
General Settings
Name: MYSQL_EDGE
Datasource Classname: com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
Resource Type: javax.sql.ConnectionPoolDataSource
Description: DIY Edge Database
Pool Settings
Initial and Minimum Pool Size: 8
Maximum Pool Size: 32
Pool Resize Quantity: 2
Idle Timeout: 300
Max Wait Time: 60000
Connection Validation
Connection Validation: Required (checked)
Validation Method: table
Table Name: DUAL
On Any Failure: Close All Connections (unchecked)
Transaction Isolation
Transaction Isolation: read-committed
Isolation Level: Guaranteed (unchecked)
The problem only occurs when connection pool resource type is set to:
Resource Type: javax.sql.ConnectionPoolDataSource
Then, if i set the Transaction Isolation level to anything (other than default which is blank), I get the following error from appserver:
[#|2006-09-06T17:26:43.574-0400|SEVERE|sun-appserver-pe8.2|javax.enterprise.res ource.resourceadapter|_ThreadID=12;|jdbc.exc_tx_level|#]
[#|2006-09-06T17:26:43.576-0400|WARNING|sun-appserver-pe8.2|javax.enterprise.re source.resourceadapter|_ThreadID=12;|RAR5117 : Failed to obtain/create connection. Reason : The isolation level could not be set: Transaction isolation level NONE not supported by MySQL|#]
[#|2006-09-06T17:26:43.577-0400|WARNING|sun-appserver-pe8.2|javax.enterprise.re source.resourceadapter|_ThreadID=12;|RAR5114 : Error allocating connection : [Error in allocating a connection. Cause: The isolation level could not be set: Transaction isolation level NONE not supported by MySQL]|#]
If i set the resource type to:
Resource Type: javax.sql.DataSource
I don't have this problem. I can set the Transaction Isolation level to whatever i want and get no warnings and everything appears to work correctly.
I initially thought the problem was with the MySQL Connector J driver. I posted to their support forum and got the following response:
<mysqlResponse>
Alan,
That's a very strange error, given that the only way you can get that exception is if something _outside_ our JDBC driver passes in Connection.TRANSACTION_ISOLATION_NONE to setTransactionIsolation()
(here's the code in question)
<pre>
switch (level) {
case java.sql.Connection.TRANSACTION_NONE:
throw SQLError.createSQLException("Transaction isolation level "
+ "NONE not supported by MySQL");
case java.sql.Connection.TRANSACTION_READ_COMMITTED:
sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED";
break;
...
default:
throw SQLError.createSQLException("Unsupported transaction "
+ "isolation level '" + level + "'",
SQLError.SQL_STATE_DRIVER_NOT_CAPABLE);
}
</pre>
Given that ConnectionPoolDataSource isn't actually a pool (it's to be used by a connection pool), I'd look into if Sun's appserver can pool connections from a regular data source (most appservers can and [docs.sun.com] leads me to belive it does support this), or alternatively try and figure out what's passing the NONE transaction isolation level into Connector/J from inside SJAS, maybe it does this by default when you use a ConnectionPoolDataSource?.
I can't explain the difference in behavior via inspection of our code, since the connection that's returned from our ConnectionPoolDataSource is just a wrapper around our connection, and just does the following:
checkClosed();
try {
this.mc.setTransactionIsolation(level);
} catch (SQLException sqlException) {
checkAndFireConnectionError(sqlException);
}
We don't have many SJAS users who hang out here, have you tried asking on the Sun forums for SJAS, maybe this is a known issue over there? If you do find out something over there, let us know, and we'll see if there's anything that's fixable in our driver.
-Mark
Mark Matthews
MySQL AB, Software Development Manager - Client Connectivity
www.mysql.com
</mysqlResponse>
I really want to take advantage of connection pooling and must have the transaction isolation level be read-committed for the database sessions instantiated by the application. Does anyone have any ideas as to what I might be doing wrong?
thanks in advance for your guidance,
- Alan
