Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » pool: 1 connection
pool: 1 connection [message #974736] Wed, 07 November 2012 04:31 Go to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 806
Registered: July 2009
Senior Member
I'm using Eclipselink inside a Swing client, this means that connection pooling has a lesser performance advantage, however unnecessary connections have a big RDBMS license impact.

Is it possible to configure EL to only use one connection? I now have this setup:

lOptions.put(PersistenceUnitProperties.JOIN_EXISTING_TRANSACTION, "true"); // reads and write should go through the same connection, otherwise batchtransfer will have connection conflicts
lOptions.put(PersistenceUnitProperties.JDBC_SEQUENCE_CONNECTION_POOL , "true"); // this is deprecated, but the replacement CONNECTION_POOL_SEQUENCE does not work on 3.1.2-M1 // force sequences to use a separate pool, so rollback do not undo counter increments
lOptions.put(PersistenceUnitProperties.CONNECTION_POOL_INITIAL, "1"); // for the RDBMS licenses
lOptions.put(PersistenceUnitProperties.CONNECTION_POOL_MAX, "1"); // for the RDSBMS licenses

This results in two connections when I ask the RDBMS, I suspect one for data and one for the sequence?
If I set it up like this (replace JDBC_SEQUENCE_CONNECTION_POOL).

lOptions.put(PersistenceUnitProperties.JOIN_EXISTING_TRANSACTION, "true"); // reads and write should go through the same connection, otherwise batchtransfer will have connection conflicts
lOptions.put(PersistenceUnitProperties.CONNECTION_POOL_SEQUENCE , "true"); // force sequences to use a separate pool, so rollback do not undo counter increments
lOptions.put(PersistenceUnitProperties.CONNECTION_POOL_INITIAL, "1"); // for the RDBMS licenses
lOptions.put(PersistenceUnitProperties.CONNECTION_POOL_MAX, "1"); // for the RDSBMS licenses

There now only is one connection, but that scares me a bit, because not using the deprecated option should not change the behavior.

So:
1. Is there any way to see what a certain connection is intended for?
2. Is it possible to setup a pool with a single (or even 0) permanent connections, but still have the sequences done in a separate transaction (opening a separate connection if needed)?

Tom
Re: pool: 1 connection [message #974741 is a reply to message #974736] Wed, 07 November 2012 04:36 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 806
Registered: July 2009
Senior Member
I forgot to mention that I'm using EL 2.4.0 on J7.
Re: pool: 1 connection [message #974869 is a reply to message #974736] Wed, 07 November 2012 06:51 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 806
Registered: July 2009
Senior Member
About the CONNECTION_POOL_SEQUENCE; there is an open issue on that.
Re: pool: 1 connection [message #982960 is a reply to message #974869] Tue, 13 November 2012 10:13 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

I would not use, JOIN_EXISTING_TRANSACTION, but instead use, EXCLUSIVE_CONNECTION_MODE="Always"

Do you want 1 connection, or 2 (1 for access, and 1 for sequence)?

In general this is a very bad idea as you will only be able to support a single thread, but I assume you know the limitations.

To use 0 connections you can try setting CONNECTION_POOL_INITIAL to 0 and CONNECTION_POOL_MIN to 0, but this will need to connect for every transaction, so will be very inefficient.

If you use JTA, then if you remove your sequencing options a the same connection will be used for sequencing (you will only use 1 connection).

If you want 2 connections, one for transactions, and one for sequencing then using the sequence connection pool of size 1 is correct.





James : Wiki : Book : Blog : Twitter
Re: pool: 1 connection [message #983346 is a reply to message #982960] Tue, 13 November 2012 16:40 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 806
Registered: July 2009
Senior Member
On 2012-11-13 16:13, James Sutherland wrote:
> I would not use, JOIN_EXISTING_TRANSACTION, but instead use, EXCLUSIVE_CONNECTION_MODE="Always"

I'll take a look at the exclusive_connection_mode, but the problem the join_existing_transaction solves is that I have some native SQL going on, dating back to the direct JDBC version, that does some explicit locking on tables. And that needs to happen within the same transaction.


> Do you want 1 connection, or 2 (1 for access, and 1 for sequence)?

Actually I want one pool, with one active connection as the basis, with the option to temporarily open more.


> In general this is a very bad idea as you will only be able to support a single thread, but I assume you know the limitations.

It is a matter of performance vs money. Do note that we're not talking about an application server, but individual fat Swing clients.
It uses a single thread to store the information anyhoew.
But it would not be a problem that it temporarily creates a additional connections, as long as they are closed again after use.


> To use 0 connections you can try setting CONNECTION_POOL_INITIAL to 0 and CONNECTION_POOL_MIN to 0, but this will need to connect for every transaction, so will be very inefficient.

Tried that, no good.
The reason I want this is because you can specify two pools (one for data one for sequences), but each pool keeps at least one connection open.
If I do not use a separate pool, then a rollback also rollback the increments on the sequence table. These should never rollback.
It would be much better if the sequences would use the same pool, but not the same connection/transaction.
There is no SEQUENCE_SEPARATE_CONNECTION setting, right?

> If you use JTA, then if you remove your sequencing options a the same connection will be used for sequencing (you will only use 1 connection).

I do not use JTA, but direct EM transactions.


> If you want 2 connections, one for transactions, and one for sequencing then using the sequence connection pool of size 1 is correct.

Yes, but it creates two pools, each with one connection.
I would like to see one pool, with one open connection and the possibility to temporarily open more.

Tom
Re: pool: 1 connection [message #986233 is a reply to message #983346] Mon, 19 November 2012 09:37 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 806
Registered: July 2009
Senior Member
Exclusive connection is not what I need.

There basically is one thing that prevents lowering the connection footprint, namely the fact that there are only two options for sequences:
- share connection (and transaction) or
- separate pool.

The first results in unwanted rollbacks on sequences, the latter results in an additional set of connections being open. The intermediate (and IMHO more than sufficient) option "separate connection" does not exist. Why?

Tom
Re: pool: 1 connection [message #986489 is a reply to message #986233] Tue, 20 November 2012 10:56 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You can use the same pool for both the read/write pool and for the sequence pool.

I don't think there is a persistence unit property for it (other than using a shared DataSource pool), but you can use a SessionCustomizer for it,

something like,

session.getSequencingControl().setConnectionPool(session.getDefaultConnectionPool());

It is not normally a good idea in a server environment, as you can deadlock getting a sequence if the pool is maxed out.


James : Wiki : Book : Blog : Twitter
Re: pool: 1 connection [message #986791 is a reply to message #986489] Wed, 21 November 2012 11:07 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom Eugelink
Messages: 806
Registered: July 2009
Senior Member
I did this:

org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl lEntityManagerFactoryImpl = (org.eclipse.persistence.internal.jpa.EntityManagerFactoryImpl)lEntityManagerFactory;
lEntityManagerFactoryImpl.getDatabaseSession().getSequencingControl().setConnectionPool( lEntityManagerFactoryImpl.getServerSession().getDefaultConnectionPool() );

And it does not throw any exceptions. But I still see 2 connection open, but max is configured to 0:

lOptions.put(PersistenceUnitProperties.CONNECTION_POOL_MAX, "1");

So this may not be working (I'm still setting JDBC_SEQUENCE_CONNECTION_POO to "true" prior to the two lines above, so it will try to fetch its own connection).
But knowing how you guys build Eclipselink, creating my own pool probably is very simple, requiring just an interface to be implemented. I did not find much documentation about how to provide your own pool aside from doing it via a DataSource:

http://onpersistence.blogspot.de/2008/04/eclipselink-and-datasources.html

Is there any information on this?

Tom
Re: pool: 1 connection [message #986794 is a reply to message #986791] Wed, 21 November 2012 11:20 Go to previous message
Tom Eugelink is currently offline Tom Eugelink
Messages: 806
Registered: July 2009
Senior Member
Hold on! Setting the JDBC_SEQUENCE_CONNECTION_POOL to "false" and then use the two lines works!

False without lines: one transaction for both sequence and insert.
True without lines: two pools.
True with lines: two pools.
False with lines: one pool but separate connections for sequence and insert.

This is great! No the only missing thing is an overflow features (temporarily allow more connections, which are closed again immediately).

Tom
Previous Topic:Write List<Object> to file using Eclipselink Moxy
Next Topic:newbie: Native SQL Query
Goto Forum:
  


Current Time: Fri Apr 18 01:36:57 EDT 2014

Powered by FUDForum. Page generated in 0.01997 seconds