Re: Is it thread-safe to pool the PreparedStatement?

Ok, so sharing the connection between threads is not recommended.

How about a single thread that makes multiple overlapping queries off
the same Connection?  (We'll disregard for now the fact that perhaps the
database structure could be changed to relieve the necessity to do
this.  It's an existing database that resists change.)  I'm just trying
to get a handle on what is and is not appropriate here.

In regards to performance, the code I inherited was building the whole
JDBC connection (including getting the BasicDataSource) for each
statement query.  Clearly *that* was not performant.  In cutting that
out, I just went to the shared connection approach.  It *seems* to work,
but of course in all things multi-threaded, that is certainly no
guarantee that it is correct.

thanks,
Linus


Brian Goetz wrote:
> I would say the answer is no.  There's nothing in the spec that requires
> that a JDBC connection be thread-safe.  Doing what you suggest would
> require the JDBC connection to be thread-safe.
>
> You can cache prepared statements, but since statements are tied to a
> connection, your key must be (connection, sqlString) rather than just
> sqlString.  Many JDBC drivers do this for you already.
>
> More importantly, do you have evidence that JDBC calls (as opposed to
> time waiting for the database) are actually the source of any
> performance problem, or is this just optimizing for its own sake?
>
> Linus Kamb wrote:
>> Is it legitimate and/or appropriate to share a JDBC Connection between
>> threads and have each thread call connection.prepareStatement() ?
>>
>> It seems like that is the way one should do it, but I have read
>> conflicting posts.  I recall (though I may be confused) reading that
>> connections may cache prepared statements. (Not sure I want to say that
>> lest I'm incorrect and add to the confusion...)
>>
>> Are there limits or scalability issues to such an approach?
>>
>> Linus
>>
>>
>> Brian Maso wrote:
>>> At first I thought this would violate JTA requirements -- but looking
>>> at the JTA spec it seems that multithreaded access to a resource
>>> manager is supported, so there's no problem there.
>>>
>>> The JDBC spec says that a PreparedStatement can only be involved in a
>>> single database interaction at a time. This means that, while reading
>>> the ResultSet response to a SELECT query put forth through a
>>> particular PreparedStatement, you can't send another query through the
>>> same PreparedStatement because the existing ResultSet will be closed
>>> automatically. So your PreparedStatement pool needs to ensure
>>> exclusive access to a single thread until the thread is done with the
>>> statement, before returning the statement back to the pool. Sounds
>>> like that kind of assurance might not be there.
>>>
>>> Brian Maso
>>>
>>> At 09:46 AM 8/20/2007, you wrote:
>>>> I just join a project, this existing team member of this project
>>>> concern performance a lot so their pool all PreparedStatements into a
>>>> map (probably not a nice optimization, but I have no say about that)
>>>> when the application start. And then reuse those preparedstatement
>>>> again and again.
>>>>
>>>> I haven't get time to check all method call using those
>>>> preparedstatement are synchronized properly. If that is not the case,
>>>> is it thread-safe to pool the PreparedStatement?
>>>>
>>>> ===================================
>>>> This list is hosted by DevelopMentor®  http://www.develop.com
>>>>
>>>> View archives and manage your subscription(s) at
>>>> http://discuss.develop.com
>>>
>>> ===================================
>>> This list is hosted by DevelopMentor®  http://www.develop.com
>>>
>>> View archives and manage your subscription(s) at
>>> http://discuss.develop.com
>>>
>>
>> ===================================
>> This list is hosted by DevelopMentor®  http://www.develop.com
>>
>> View archives and manage your subscription(s) at
>> http://discuss.develop.com
>
> ===================================
> This list is hosted by DevelopMentor®  http://www.develop.com
>
> View archives and manage your subscription(s) at
> http://discuss.develop.com

===================================
This list is hosted by DevelopMentor®  http://www.develop.com

View archives and manage your subscription(s) at http://discuss.develop.com