Re: Autocommit, Implicit Transactions, and Connection Pools
- From: Joe Weinstein <joeNOSPAM@xxxxxxx>
- Date: Thu, 06 Mar 2008 11:39:02 -0800
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
.
- Prev by Date: Optimizing performance
- Next by Date: Re: Autocommit, Implicit Transactions, and Connection Pools
- Previous by thread: Optimizing performance
- Next by thread: Re: Autocommit, Implicit Transactions, and Connection Pools
- Index(es):
Relevant Pages
|
Loading
