You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Daryl Stultz <da...@6degrees.com> on 2010/10/18 20:41:45 UTC
Sharing JDBC connection
Hello all,
A while back I asked if there was a way to share a connection between my
JDBC code and JPA code. It was suggested that I open an transaction on the
EM and then access the connection on the transaction. I'm sure this will
work, but I need to do the opposite.
I have a code block that gets a JDBC connection, loops over some things and
performs actions on one transaction per loop. In each loop some JPA action
may take place on an EM. The problem is that the JDBC connection is in a
transaction, and the queries run from the EM don't see the newly inserted
records.
I happen to be using a DataSource to supply OpenJPA with connections. The
JDBC connection is available via a ThreadLocal variable (as is one and only
one EM). I did a quick test where my DataSource simply provides a wrapper of
the JDBC connection. I override the close() method to do nothing so the
outer JDBC code is not killed. It works. The EM queries are performed on the
same JDBC connection. Naturally, I do not expect OpenJPA to "join" the JDBC
connection, nor do I expect to need to open a transaction on the EM in the
loop. (Interestingly, though, it does not crash when I do so.)
So given my simple test seems to work, I'm wondering if anyone can tell me
if I'll run into some major trouble later. Am I playing with fire?
Ultimately this is "transition" code from JDBC to JPA, but it may be around
for a while.
Thanks.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Sharing JDBC connection
Posted by Daryl Stultz <da...@opentempo.com>.
On Tue, Oct 19, 2010 at 9:40 AM, Kevin Sutter <kw...@gmail.com> wrote:
> You want your EM query to pick up the uncommitted changes on
> your JDBC connection.
Yes, and I'm doing this simply by using the same connection. Both the JDBC
connection and the EM are available on a ThreadLocal variable. The EM gets
its connection via a DataSource. The DataSource simply hands it the JDBC
connection.
> Have you tried adjusting the isolation level?
>
No, I've never played with such a thing.
> so I'm
not completely clear on how overriding the close() allows the EM to see your
> JDBC updates.
>
I hope I made it clear with the first paragraph above (same connection).
>
> I'm also assuming that you are not using any type of connection pooling,
> right?
Yes I am. I'm using "Proxool" which enhances the driver which causes
Connection.close() to return the connection to the pool.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Sharing JDBC connection
Posted by Daryl Stultz <da...@6degrees.com>.
Thanks to Kevin and Jean-Baptiste for help with this. It sounds like the
warnings I'm getting are all related to how I handle the shared connection.
This is actually the part of the equation I'm most familiar/comfortable
with. Aside from a JPA transaction within a JDBC transaction, no one has
presented any red flags, so I'll proceed with my experimentation.
Thanks!
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Sharing JDBC connection
Posted by Daryl Stultz <da...@opentempo.com>.
On Tue, Oct 19, 2010 at 9:51 AM, Jean-Baptiste BRIAUD -- Novlog <
j-b.briaud@novlog.com> wrote:
> I would add some (obvious ? if yes, sorry) things :
>
> Thanks for the tips, I think I've got these issues covered.
> 1. Ensure connection is at last closed or put back in the pool.
>
Yes, all threads have a "cleanup" routine that will rollback any open
transactions, free connection, close EM.
> 2. Test with and without a pool (or adjust value on pool) to force
> connection to be reused so you can test the code will have a long peaceful
> life on server.
>
Good point. My logs for the connection pool do show connection reuse
(resulting from unit test exercise).
> 3. heavy use of try {} finally{} block to ensure the flow control even in
> case of error in the other side.
>
Back to #1, cleanup routine has been in production a while, so pretty sure
it's solid.
> 4. Use of ThreadLocal may lead to memory leak, ensure connection is not
> only closed (1) but also remove from ThreadLocal at the end
>
>
I've taken some lumps with errors in my ThreadLocal implementation, but I
think the handling of clean up of both the JDBC connection and EM is solid.
--
Daryl Stultz
_____________________________________
6 Degrees Software and Consulting, Inc.
http://www.6degrees.com
http://www.opentempo.com
mailto:daryl.stultz@opentempo.com
Re: Sharing JDBC connection
Posted by Jean-Baptiste BRIAUD -- Novlog <j-...@novlog.com>.
I would add some (obvious ? if yes, sorry) things :
0. Yes, you're playing with fire, but that's really fun and very powerful as long as you don't start a forest fire :-)
1. Ensure connection is at last closed or put back in the pool.
2. Test with and without a pool (or adjust value on pool) to force connection to be reused so you can test the code will have a long peaceful life on server.
3. heavy use of try {} finally{} block to ensure the flow control even in case of error in the other side.
4. Use of ThreadLocal may lead to memory leak, ensure connection is not only closed (1) but also remove from ThreadLocal at the end
On 19 oct. 2010, at 15:40, Kevin Sutter wrote:
> Hi Daryl,
> You might be playing with fire, but if nobody gets hurt... :-) Seriously,
> as I was reading through your scenario, my first thought was to use Read
> Uncommitted isolation level. Not sure how this might affect your other
> database transactions, but this seems to be the scenario you are
> describing. You want your EM query to pick up the uncommitted changes on
> your JDBC connection. Have you tried adjusting the isolation level?
>
> If that won't work, then doing your hack of overriding the close() method is
> probably okay. Especially since your EM interactions are just queries. You
> don't mention when your transactions commit for either JDBC or JPA, so I'm
> not completely clear on how overriding the close() allows the EM to see your
> JDBC updates.
>
> I'm also assuming that you are not using any type of connection pooling,
> right? This sounds like a simple "migration" application that is not
> looking to scale, so just utilizing a single connection should be
> sufficient.
>
> Good luck,
> Kevin
>
> On Mon, Oct 18, 2010 at 1:41 PM, Daryl Stultz <da...@6degrees.com> wrote:
>
>> Hello all,
>>
>> A while back I asked if there was a way to share a connection between my
>> JDBC code and JPA code. It was suggested that I open an transaction on the
>> EM and then access the connection on the transaction. I'm sure this will
>> work, but I need to do the opposite.
>>
>> I have a code block that gets a JDBC connection, loops over some things and
>> performs actions on one transaction per loop. In each loop some JPA action
>> may take place on an EM. The problem is that the JDBC connection is in a
>> transaction, and the queries run from the EM don't see the newly inserted
>> records.
>>
>> I happen to be using a DataSource to supply OpenJPA with connections. The
>> JDBC connection is available via a ThreadLocal variable (as is one and only
>> one EM). I did a quick test where my DataSource simply provides a wrapper
>> of
>> the JDBC connection. I override the close() method to do nothing so the
>> outer JDBC code is not killed. It works. The EM queries are performed on
>> the
>> same JDBC connection. Naturally, I do not expect OpenJPA to "join" the JDBC
>> connection, nor do I expect to need to open a transaction on the EM in the
>> loop. (Interestingly, though, it does not crash when I do so.)
>>
>> So given my simple test seems to work, I'm wondering if anyone can tell me
>> if I'll run into some major trouble later. Am I playing with fire?
>> Ultimately this is "transition" code from JDBC to JPA, but it may be around
>> for a while.
>>
>> Thanks.
>>
>>
>> --
>> Daryl Stultz
>> _____________________________________
>> 6 Degrees Software and Consulting, Inc.
>> http://www.6degrees.com
>> http://www.opentempo.com
>> mailto:daryl.stultz@opentempo.com
>>
Re: Sharing JDBC connection
Posted by Kevin Sutter <kw...@gmail.com>.
Hi Daryl,
You might be playing with fire, but if nobody gets hurt... :-) Seriously,
as I was reading through your scenario, my first thought was to use Read
Uncommitted isolation level. Not sure how this might affect your other
database transactions, but this seems to be the scenario you are
describing. You want your EM query to pick up the uncommitted changes on
your JDBC connection. Have you tried adjusting the isolation level?
If that won't work, then doing your hack of overriding the close() method is
probably okay. Especially since your EM interactions are just queries. You
don't mention when your transactions commit for either JDBC or JPA, so I'm
not completely clear on how overriding the close() allows the EM to see your
JDBC updates.
I'm also assuming that you are not using any type of connection pooling,
right? This sounds like a simple "migration" application that is not
looking to scale, so just utilizing a single connection should be
sufficient.
Good luck,
Kevin
On Mon, Oct 18, 2010 at 1:41 PM, Daryl Stultz <da...@6degrees.com> wrote:
> Hello all,
>
> A while back I asked if there was a way to share a connection between my
> JDBC code and JPA code. It was suggested that I open an transaction on the
> EM and then access the connection on the transaction. I'm sure this will
> work, but I need to do the opposite.
>
> I have a code block that gets a JDBC connection, loops over some things and
> performs actions on one transaction per loop. In each loop some JPA action
> may take place on an EM. The problem is that the JDBC connection is in a
> transaction, and the queries run from the EM don't see the newly inserted
> records.
>
> I happen to be using a DataSource to supply OpenJPA with connections. The
> JDBC connection is available via a ThreadLocal variable (as is one and only
> one EM). I did a quick test where my DataSource simply provides a wrapper
> of
> the JDBC connection. I override the close() method to do nothing so the
> outer JDBC code is not killed. It works. The EM queries are performed on
> the
> same JDBC connection. Naturally, I do not expect OpenJPA to "join" the JDBC
> connection, nor do I expect to need to open a transaction on the EM in the
> loop. (Interestingly, though, it does not crash when I do so.)
>
> So given my simple test seems to work, I'm wondering if anyone can tell me
> if I'll run into some major trouble later. Am I playing with fire?
> Ultimately this is "transition" code from JDBC to JPA, but it may be around
> for a while.
>
> Thanks.
>
>
> --
> Daryl Stultz
> _____________________________________
> 6 Degrees Software and Consulting, Inc.
> http://www.6degrees.com
> http://www.opentempo.com
> mailto:daryl.stultz@opentempo.com
>