Autocommit, Implicit Transactions, and Connection Pools

Our ISV application can manage its own connection pool, using a modified
version of the Apache Commons DBCP. Customers would like the option of using
the WebSphere or WebLogic provided DataSource ConnectionPools.

When we manage our the connection pool ourselves, we set three properties on
the connection when we first get the connection and before we put it in the
pool: autocommit to false, readonly to false, and transactionisolation to
readCommitted.

WebSphere and WebLogic connection pools gets the connection from the
DataSource, and there doesn't appear to be any options in either product to
specify these connection properties. That means we need to issue these set
properties method calls on the connection each time we borrow one from the
external pool

My question is what is the implementation in the MS JDBC driver? Do any of
these Connection method calls result in database round trips? Or does the
driver handle these properties in how the transactions, etc., are being
managed? Is the driver smart enough to know if the properties are being
changed, that is, are being set to a different value, or to the value which
was previously set?

Wes Clark wrote:

> Our ISV application can manage its own connection pool, using a modified
> version of the Apache Commons DBCP. Customers would like the option of using
> the WebSphere or WebLogic provided DataSource ConnectionPools.
>
> When we manage our the connection pool ourselves, we set three properties on
> the connection when we first get the connection and before we put it in the
> pool: autocommit to false, readonly to false, and transactionisolation to
> readCommitted.
>
> WebSphere and WebLogic connection pools gets the connection from the
> DataSource, and there doesn't appear to be any options in either product to
> specify these connection properties. That means we need to issue these set
> properties method calls on the connection each time we borrow one from the
> external pool
>
> My question is what is the implementation in the MS JDBC driver? Do any of
> these Connection method calls result in database round trips? Or does the
> driver handle these properties in how the transactions, etc., are being
> managed? Is the driver smart enough to know if the properties are being
> changed, that is, are being set to a different value, or to the value which
> was previously set?

Hi Wes. In WebLogic you might be able to do what you want via the overloaded
'TestTable' parameter for the pool. You can specifiy any SQL to be sent to
the DBMS whenver the connection is reserved by setting the 'test table' to
"SQL set read-only false" (if that's the syntax SQLServer wants) etc.
Note though that any JDBC connection to SQLServer will be read-only false
and isolation level read-committed by default so you don't need to do those.
If you only want you change(s) done once, you can do it via the WebLogic initSQL
parameter. In either case, yo could make a stored procedure to do a long list
of stuff, and then call it via test table = "exec my_proc".
Lastly, I worry about your wanting autoCommit(false) as a default. Note that
anything the connection does to test the connection would start a transaction
and might lock data it reads until the connection gets a commit. Your application
would have to do a commit every time it uses a connection, and I hope it never
locks something someone else wants to update.

Joe Weinstein at BEA Systems