Ids not being used

[cl333r]

Folks, a simple example, after I insert one or more entities into the table the next id value starts with a 50 offset, and it happens every time. Hence it looks like a lot of id values will never be used. Is it true?
Is there a "wiser" way to "spend" id numbers (except the "identity" solution)?

Using MySQL 5.0 on Ubuntu, JPA with EclipseLink. Happens for all generation types except IDENTITY but can't use it because it lets me know the ID of a newly created entity only after I commit it to the db.

[tch]

The default allocation size is 50 to save roundtrips to the db, you
can change it, by adding allocationSize=1 to your sequence annotation.

./tch



On Sun, Jul 26, 2009 at 9:17 AM, cl333r<cl333r@...> wrote:

>
> Folks, a simple example, after I insert one or more entities into the table
> the next id value starts with a 50 offset, and it happens every time. Hence
> it looks like a lot of id values will never be used. Is it true?
> Is there a "wiser" way to "spend" id numbers (except the "identity"
> solution)?
>
> Using MySQL 5.0 on Ubuntu, JPA with EclipseLink. Happens for all generation
> types except IDENTITY but can't use it because it lets me know the ID of a
> newly created entity only after I commit it to the db.
> --
> View this message in context: http://www.nabble.com/Ids-not-being-used-tp24644801p24644801.html
> Sent from the EclipseLink - Users mailing list archive at Nabble.com.
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@...
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>

_______________________________________________
eclipselink-users mailing list
eclipselink-users@...
https://dev.eclipse.org/mailman/listinfo/eclipselink-users

[James Sutherland]

50 is the default preallocation size, but all the preallocated ids should be used.  Are you creating a new EntityManagerFactory each time? Please include your config.

cl333r wrote:

Folks, a simple example, after I insert one or more entities into the table the next id value starts with a 50 offset, and it happens every time. Hence it looks like a lot of id values will never be used. Is it true?
Is there a "wiser" way to "spend" id numbers (except the "identity" solution)?

Using MySQL 5.0 on Ubuntu, JPA with EclipseLink. Happens for all generation types except IDENTITY but can't use it because it lets me know the ID of a newly created entity only after I commit it to the db.

[sonavor]

I have also run into this problem.  I don't know if it is a problem or my lack of understanding about the preallocated ids.  For now I have changed the allocation size to 1.  I was thinking that using a higher value like the default value of 50 would be more efficient (less times to read the ID table).

In my situation I am using EclipseLink with Spring and a Struts application.  My insert and update operations are only in a Spring business object and the entity manager is provided by Spring through this configuration in the Spring applicationContext.xml -

<!-- defines the JPA vendor adapter ... EclipseLink in this case -->
<bean id="vendorAdapter" class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter">
        <property name="databasePlatform" value="org.eclipse.persistence.platform.database.MySQLPlatformPlatform" />
        <property name="showSql" value="true" />
</bean>

<!-- defines the datasource from an expected JNDI name provided by the server -->
<jee:jndi-lookup id="dataSource" jndi-name="jdbc/cimdb" />

<!-- defines the JPA EntityManagerFactory and injects the defined datasource -->
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="jpaVendorAdapter" ref="vendorAdapter"/>
        <property name="dataSource" ref="dataSource" />
        <property name="jpaPropertyMap">
            <map>
                <entry key="eclipselink.jdbc.native-sql" value="true"/>
                <entry key="eclipselink.logging.level" value="FINEST"/>
                <entry key="eclipselink.logging.exceptions" value="true"/>
                <entry key="eclipselink.target-database" value="MYSQL"/>
                <entry key="eclipselink.weaving" value="static"/>
            </map>
        </property>
</bean>

Then in my Spring business objects the EntityManager is injected with a method -
//EntityManager will be auto-injected due to @PersistenceContext
@PersistenceContext(unitName = "CiMJpaLibPU")
private EntityManager em;
public void setEntityManager(EntityManager em) {
        this.em = em;
}

Usage of the EntityManager in the Spring business class is then just like this -

Query q = em.createNamedQuery("MediaResolution.findAll");
List<MediaResolution> rList = q.getResultList();

or in the case of an insert like this -

MediaListing ml = new MediaListing; // a JPA entity
(pseudo code) ml.<set various attributes>
em.persist(ml);

All of this works fine except that the generated primary keys jump by the default count of 50 unless I set the allocation size smaller.  As I do here -

@Id
@GeneratedValue(generator="MLTable")
@TableGenerator(name="MLTable", table="KEY_SEQUENCE",
            pkColumnName="KEY_SEQ_NAME", valueColumnName="KEY_INDEX",
            pkColumnValue="com.cim.jpa.MediaListing", allocationSize=1)
@Basic(optional = false)
@Column(name = "MEDIA_ID", nullable = false)
private Integer mediaId;

I have noticed that in the case of a OneToMany relationship that while looping through and inserting the child records (i.e. media items for the MediaListing JPA class) that the MediaItems JPA children do get primary key values in increments of "1" even with the allocationSize set to the default 50.

The difference there is that the child records are being inserted at the same time as the parent and are all in one transaction.  While the parent inserts are always created in a new transaction.  Does that mean the Spring framework is creating a new EntityManager for each transaction then?







50 is the default preallocation size, but all the preallocated ids should be used.  Are you creating a new EntityManagerFactory each time? Please include your config.

cl333r wrote:

Folks, a simple example, after I insert one or more entities into the table the next id value starts with a 50 offset, and it happens every time. Hence it looks like a lot of id values will never be used. Is it true?
Is there a "wiser" way to "spend" id numbers (except the "identity" solution)?

Using MySQL 5.0 on Ubuntu, JPA with EclipseLink. Happens for all generation types except IDENTITY but can't use it because it lets me know the ID of a newly created entity only after I commit it to the db.

[James Sutherland]

My guess it is some kind of Spring configuration issue, but I'm no Spring expert, so perhaps someone else can tell you more.  You could try the same outside of Spring to see if the sequence ids get reused.  Also ensure that you are using the lastest EclipseLink, I remember a similar issue but it was either before EclipseLink 1.0, or 1.1.



I have also run into this problem.  I don't know if it is a problem or my lack of understanding about the preallocated ids.  For now I have changed the allocation size to 1.  I was thinking that using a higher value like the default value of 50 would be more efficient (less times to read the ID table).

In my situation I am using EclipseLink with Spring and a Struts application.  My insert and update operations are only in a Spring business object and the entity manager is provided by Spring through this configuration in the Spring applicationContext.xml -

<!-- defines the JPA vendor adapter ... EclipseLink in this case -->
<bean id="vendorAdapter" class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter">
        <property name="databasePlatform" value="org.eclipse.persistence.platform.database.MySQLPlatformPlatform" />
        <property name="showSql" value="true" />
</bean>

<!-- defines the datasource from an expected JNDI name provided by the server -->
<jee:jndi-lookup id="dataSource" jndi-name="jdbc/cimdb" />

<!-- defines the JPA EntityManagerFactory and injects the defined datasource -->
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="jpaVendorAdapter" ref="vendorAdapter"/>
        <property name="dataSource" ref="dataSource" />
        <property name="jpaPropertyMap">
            <map>
                <entry key="eclipselink.jdbc.native-sql" value="true"/>
                <entry key="eclipselink.logging.level" value="FINEST"/>
                <entry key="eclipselink.logging.exceptions" value="true"/>
                <entry key="eclipselink.target-database" value="MYSQL"/>
                <entry key="eclipselink.weaving" value="static"/>
            </map>
        </property>
</bean>

Then in my Spring business objects the EntityManager is injected with a method -
//EntityManager will be auto-injected due to @PersistenceContext
@PersistenceContext(unitName = "CiMJpaLibPU")
private EntityManager em;
public void setEntityManager(EntityManager em) {
        this.em = em;
}

Usage of the EntityManager in the Spring business class is then just like this -

Query q = em.createNamedQuery("MediaResolution.findAll");
List<MediaResolution> rList = q.getResultList();

or in the case of an insert like this -

MediaListing ml = new MediaListing; // a JPA entity
(pseudo code) ml.<set various attributes>
em.persist(ml);

All of this works fine except that the generated primary keys jump by the default count of 50 unless I set the allocation size smaller.  As I do here -

@Id
@GeneratedValue(generator="MLTable")
@TableGenerator(name="MLTable", table="KEY_SEQUENCE",
            pkColumnName="KEY_SEQ_NAME", valueColumnName="KEY_INDEX",
            pkColumnValue="com.cim.jpa.MediaListing", allocationSize=1)
@Basic(optional = false)
@Column(name = "MEDIA_ID", nullable = false)
private Integer mediaId;

I have noticed that in the case of a OneToMany relationship that while looping through and inserting the child records (i.e. media items for the MediaListing JPA class) that the MediaItems JPA children do get primary key values in increments of "1" even with the allocationSize set to the default 50.

The difference there is that the child records are being inserted at the same time as the parent and are all in one transaction.  While the parent inserts are always created in a new transaction.  Does that mean the Spring framework is creating a new EntityManager for each transaction then?

James Sutherland wrote:

50 is the default preallocation size, but all the preallocated ids should be used.  Are you creating a new EntityManagerFactory each time? Please include your config.

cl333r wrote:

Folks, a simple example, after I insert one or more entities into the table the next id value starts with a 50 offset, and it happens every time. Hence it looks like a lot of id values will never be used. Is it true?
Is there a "wiser" way to "spend" id numbers (except the "identity" solution)?

Using MySQL 5.0 on Ubuntu, JPA with EclipseLink. Happens for all generation types except IDENTITY but can't use it because it lets me know the ID of a newly created entity only after I commit it to the db.

[sonavor]

I am using EclipseLink 1.1.2

You are probably right that it is likely a Spring issue.  I ran a couple more tests and verified that if I added child records individually (in separate transactions) that the generated ID bumps by the default 50.  While adding child records in one transaction increments the ID values by just one.  So it looks like a new transaction is causing the EntityManager to re-initialize.

I'll try to find some time to test this in a non-Spring environment to see if I get the same result.  For now I can live with setting the allocationSize to 1.  


My guess it is some kind of Spring configuration issue, but I'm no Spring expert, so perhaps someone else can tell you more.  You could try the same outside of Spring to see if the sequence ids get reused.  Also ensure that you are using the lastest EclipseLink, I remember a similar issue but it was either before EclipseLink 1.0, or 1.1.



I have also run into this problem.  I don't know if it is a problem or my lack of understanding about the preallocated ids.  For now I have changed the allocation size to 1.  I was thinking that using a higher value like the default value of 50 would be more efficient (less times to read the ID table).

In my situation I am using EclipseLink with Spring and a Struts application.  My insert and update operations are only in a Spring business object and the entity manager is provided by Spring through this configuration in the Spring applicationContext.xml -

<!-- defines the JPA vendor adapter ... EclipseLink in this case -->
<bean id="vendorAdapter" class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter">
        <property name="databasePlatform" value="org.eclipse.persistence.platform.database.MySQLPlatformPlatform" />
        <property name="showSql" value="true" />
</bean>

<!-- defines the datasource from an expected JNDI name provided by the server -->
<jee:jndi-lookup id="dataSource" jndi-name="jdbc/cimdb" />

<!-- defines the JPA EntityManagerFactory and injects the defined datasource -->
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="jpaVendorAdapter" ref="vendorAdapter"/>
        <property name="dataSource" ref="dataSource" />
        <property name="jpaPropertyMap">
            <map>
                <entry key="eclipselink.jdbc.native-sql" value="true"/>
                <entry key="eclipselink.logging.level" value="FINEST"/>
                <entry key="eclipselink.logging.exceptions" value="true"/>
                <entry key="eclipselink.target-database" value="MYSQL"/>
                <entry key="eclipselink.weaving" value="static"/>
            </map>
        </property>
</bean>

Then in my Spring business objects the EntityManager is injected with a method -
//EntityManager will be auto-injected due to @PersistenceContext
@PersistenceContext(unitName = "CiMJpaLibPU")
private EntityManager em;
public void setEntityManager(EntityManager em) {
        this.em = em;
}

Usage of the EntityManager in the Spring business class is then just like this -

Query q = em.createNamedQuery("MediaResolution.findAll");
List<MediaResolution> rList = q.getResultList();

or in the case of an insert like this -

MediaListing ml = new MediaListing; // a JPA entity
(pseudo code) ml.<set various attributes>
em.persist(ml);

All of this works fine except that the generated primary keys jump by the default count of 50 unless I set the allocation size smaller.  As I do here -

@Id
@GeneratedValue(generator="MLTable")
@TableGenerator(name="MLTable", table="KEY_SEQUENCE",
            pkColumnName="KEY_SEQ_NAME", valueColumnName="KEY_INDEX",
            pkColumnValue="com.cim.jpa.MediaListing", allocationSize=1)
@Basic(optional = false)
@Column(name = "MEDIA_ID", nullable = false)
private Integer mediaId;

I have noticed that in the case of a OneToMany relationship that while looping through and inserting the child records (i.e. media items for the MediaListing JPA class) that the MediaItems JPA children do get primary key values in increments of "1" even with the allocationSize set to the default 50.

The difference there is that the child records are being inserted at the same time as the parent and are all in one transaction.  While the parent inserts are always created in a new transaction.  Does that mean the Spring framework is creating a new EntityManager for each transaction then?

James Sutherland wrote:

50 is the default preallocation size, but all the preallocated ids should be used.  Are you creating a new EntityManagerFactory each time? Please include your config.

cl333r wrote:

Folks, a simple example, after I insert one or more entities into the table the next id value starts with a 50 offset, and it happens every time. Hence it looks like a lot of id values will never be used. Is it true?
Is there a "wiser" way to "spend" id numbers (except the "identity" solution)?

Using MySQL 5.0 on Ubuntu, JPA with EclipseLink. Happens for all generation types except IDENTITY but can't use it because it lets me know the ID of a newly created entity only after I commit it to the db.