Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » SEQUENCE table / row lock issue
icon5.gif  SEQUENCE table / row lock issue [message #748493] Mon, 24 October 2011 18:18 Go to next message
pop prem is currently offline pop prem
Messages: 20
Registered: June 2010
Junior Member
Hi All,

I'm using Spring transactions with eclipselink in tomcat, mysql enviornment.

I was getting lock timeout issues always when testing my web application and i was looking for issues with Spring transactions with eclipselink, but finally got my doubt cleared with the log statements.

I'm using ID generation type AUTO.
 @Id
 @Column(name = "some_id", unique = true, nullable = false)
 @GeneratedValue(strategy = GenerationType.AUTO)
 private Long id;
 


I can see that a tabled named SEQUENCE is created and it has only a value which is updated by all transactions. Seems like the lock is happening here.

  Internal Exception: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
Error Code: 1205
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
       bind => [50, SEQ_GEN]
Query: DataModifyQuery(name="SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")]       
 


How can i solve this issue?? Any advices are much appreciated.

Thanks.
Re: SEQUENCE table / row lock issue [message #748514 is a reply to message #748493] Mon, 24 October 2011 18:32 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 increase your sequence pre-allocation size to reduce the contention on the sequence table. You can also have each of your classes use a different sequence name, or even different sequence table (to prevent page locks).

Are you getting a deadlock, or just contention?

Ensure that you are using a sequence connection pool so that sequence values are allocated outside of the current transaction.
Are you using JTA? if so ensure you also set a nonJtaDataSource

See,
http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/config/PersistenceUnitProperties.html#CONNECTION_POOL_SEQUENCE


James : Wiki : Book : Blog : Twitter
Re: SEQUENCE table / row lock issue [message #748533 is a reply to message #748514] Mon, 24 October 2011 18:48 Go to previous messageGo to next message
pop prem is currently offline pop prem
Messages: 20
Registered: June 2010
Junior Member
Thanks for the reply James.

We are not using JTA. And also this doesn't seems to be a deadlock.

Quote:

You can increase your sequence pre-allocation size to reduce the contention on the sequence table. You can also have each of your classes use a different sequence name, or even different sequence table (to prevent page locks).

- Great, we will try both these solutions.

Quote:

Ensure that you are using a sequence connection pool so that sequence values are allocated outside of the current transaction.

- Can you please provide me an example on this???

I have added spring, eclipselink configurations used below:

Persistence.xml:
 <persistence-unit name="xxxxService" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <class>...</class>
    <class>...</class>
    <class>...</class>
    <properties> .... </properties>
   </persistence-unit> 
 


Spring-beans.xml
                 <bean id="jpaDialect" class="org.springframework.orm.jpa.vendor.EclipseLinkJpaDialect" />
	    
		<bean class="org.springframework.orm.jpa.JpaTransactionManager"
			id="transactionManager">
			<property name="entityManagerFactory" ref="entityManagerFactory" />
			<property name="jpaDialect" ref="jpaDialect" />
		</bean>
		<bean id="jpaVendorAdapter"
			class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter">
			<property name="showSql" value="true" />
			<property name="generateDdl" value="true" />
		<property name="databasePlatform" value="org.eclipse.persistence.platform.database.MySQLPlatform" />
		</bean>
		<bean
			class="org.springframework.orm.jpa.LocalEntityManagerFactoryBean"
			id="entityManagerFactory">
			<property name="jpaVendorAdapter" ref="jpaVendorAdapter" />
			<property name="jpaDialect" ref="jpaDialect" />
			<property name="persistenceUnitName" value="xxxxService" />
		</bean>		
		
		<tx:annotation-driven transaction-manager="transactionManager" />
		
		<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor"/>
 


Java class:
         @Transactional
	public void saveSumthg(Sumthg sumthg) throws Exception{
		someDAO.saveSumthg(sumthg);
	}

       @Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
	public List<Sumthg> findActiveSumthgs(String username) {
		List<Sumthg> sumthgs = someDAO.findActiveSumthgs(username);
		return sumthgs ;
	}
 
Re: SEQUENCE table / row lock issue [message #748538 is a reply to message #748514] Mon, 24 October 2011 18:48 Go to previous messageGo to next message
pop prem is currently offline pop prem
Messages: 20
Registered: June 2010
Junior Member
Thanks for the reply James.

We are not using JTA. And also this doesn't seems to be a deadlock.

Quote:
> You can increase your sequence pre-allocation size to reduce the contention on the sequence table. You can also have each of your classes use a different sequence name, or even different sequence table (to prevent page locks).

- Great, we will try both these solutions.

Quote:
> Ensure that you are using a sequence connection pool so that sequence values are allocated outside of the current transaction.

- Can you please provide me an example on this???

I have added spring, eclipselink configurations used below:

Persistence.xml:

<persistence-unit name="xxxxService" transaction-type="RESOURCE_LOCAL">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<class>...</class>
<class>...</class>
<class>...</class>
<properties> .... </properties>
</persistence-unit>


Spring-beans.xml

<bean id="jpaDialect" class="org.springframework.orm.jpa.vendor.EclipseLinkJpaDialect" />

<bean class="org.springframework.orm.jpa.JpaTransactionManager"
id="transactionManager">
<property name="entityManagerFactory" ref="entityManagerFactory" />
<property name="jpaDialect" ref="jpaDialect" />
</bean>
<bean id="jpaVendorAdapter"
class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter">
<property name="showSql" value="true" />
<property name="generateDdl" value="true" />
<property name="databasePlatform" value="org.eclipse.persistence.platform.database.MySQLPlatform" />
</bean>
<bean
class="org.springframework.orm.jpa.LocalEntityManagerFactoryBean"
id="entityManagerFactory">
<property name="jpaVendorAdapter" ref="jpaVendorAdapter" />
<property name="jpaDialect" ref="jpaDialect" />
<property name="persistenceUnitName" value="xxxxService" />
</bean>

<tx:annotation-driven transaction-manager="transactionManager" />

<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor"/>


Java class:

@Transactional
public void saveSumthg(Sumthg sumthg) throws Exception{
someDAO.saveSumthg(sumthg);
}

@Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
public List<Sumthg> findActiveSumthgs(String username) {
List<Sumthg> sumthgs = someDAO.findActiveSumthgs(username);
return sumthgs ;
}
Re: SEQUENCE table / row lock issue [message #753519 is a reply to message #748533] Thu, 27 October 2011 14:55 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

By default EclipseLink will allocate sequence ids in the current database transaction. If EclipseLink has not yet started a database transaction, then it will start a separate transaction for sequencing (and no locks will be held). EclipseLink does not start the database transaction when you start the JPA transaction, it deferrs it until it has to, so sequencing is normally non locking. If however you call flush() or triggered a flush() from a query, or executed SQL or update JPQL queries, then the database transaction will be started, and sequences acquired after this will hold locks.

You can avoid this be configuring a sequence connection pool, then EclipseLink will use a connection from this pool for sequencing, and never the active transaction.

You can configure a separate connection pool to be used by sequencing using the "eclipselink.connection-pool.sequence.nonJtaDataSource", etc. properties.

See,
http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/config/PersistenceUnitProperties.html#CONNECTION_POOL_SEQUENCE


James : Wiki : Book : Blog : Twitter
Re: SEQUENCE table / row lock issue [message #753521 is a reply to message #748533] Thu, 27 October 2011 14:55 Go to previous messageGo to next message
James is currently offline James
Messages: 272
Registered: July 2009
Senior Member
By default EclipseLink will allocate sequence ids in the current database transaction. If EclipseLink has not yet started a database transaction, then it will start a separate transaction for sequencing (and no locks will be held). EclipseLink does not start the database transaction when you start the JPA transaction, it deferrs it until it has to, so sequencing is normally non locking. If however you call flush() or triggered a flush() from a query, or executed SQL or update JPQL queries, then the database transaction will be started, and sequences acquired after this will hold locks.

You can avoid this be configuring a sequence connection pool, then EclipseLink will use a connection from this pool for sequencing, and never the active transaction.

You can configure a separate connection pool to be used by sequencing using the "eclipselink.connection-pool.sequence.nonJtaDataSource", etc. properties.

See,
http://www.eclipse.org/eclipselink/api/2.3/org/eclipse/persistence/config/PersistenceUnitProperties.html#CONNECTION_POOL_SEQUENCE
--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/
Re: SEQUENCE table / row lock issue [message #755217 is a reply to message #753519] Mon, 07 November 2011 17:41 Go to previous messageGo to next message
Luca Graf is currently offline Luca Graf
Messages: 17
Registered: September 2011
Junior Member
I am facing a similiar issue. Sequence allocation can cause deadlock's on application level when nested (ejb) transactions are used.
I tried to setup a seperate connection-pool for sequence allocation like desribed in the JavaDoc.

persistent.xml
...
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
<jta-data-source>jdbc/project</jta-data-source>
<properties>
   <property name="eclipselink.connection-pool.sequence.nonJtaDataSource" value="jdbc/project-sequencing"/>
   ...
</properties>
...


The configured datasource name points to a seperate connection pool defined in the application server (glassfish-3.1.1).
But it seems that the datasource will not be used for sequence allocation. Also if the datasource name is changed to a non exiting one, the application runs without error (application run into deadlock).

Is there futher configuration neccessary for system pools?
Can someone provide a configuration example configuration or point me to the right direction?

Greetings Luca
Re: SEQUENCE table / row lock issue [message #755241 is a reply to message #755217] Mon, 07 November 2011 18:43 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Your property looks correct, what version of EclipseLink are you using? This property is only support on later versions.

You could also try setting the sequence connection pool through a SessionCustomizer in code.


James : Wiki : Book : Blog : Twitter
Re: SEQUENCE table / row lock issue [message #755244 is a reply to message #755217] Mon, 07 November 2011 18:43 Go to previous messageGo to next message
James is currently offline James
Messages: 272
Registered: July 2009
Senior Member
Your property looks correct, what version of EclipseLink are you using? This property is only support on later versions.

You could also try setting the sequence connection pool through a SessionCustomizer in code.

--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/
Re: SEQUENCE table / row lock issue [message #756370 is a reply to message #755241] Sat, 12 November 2011 13:26 Go to previous message
Luca Graf is currently offline Luca Graf
Messages: 17
Registered: September 2011
Junior Member
Hi James,

i use the eclipselink version bundled with glassfish-3.1.1 (2.3.0.v20110604-r9504).

I have done some futher debuging and it seem's that the SequenceManager uses the wrong allocation strategy (Preallocation_Transaction_NoAccessor_State) also if a sequence connection pool is configured in persistence.xml. The Preallocation_Transaction_NoAccessor_State strategy not use seperate connections for sequence allocation, also it use locks that seems to be the cause for the deadlock.

I have created an issue with a more detailed description and a proposed patch.
https://bugs.eclipse.org/bugs/show_bug.cgi?id=363643

Greetings Luca

P.S. It seems that the old-style (deprecated as of EclipseLink 2.2) configuration 'eclipselink.jdbc.sequence-connection-pool.*' is not affected and can be used as workaround.

[Updated on: Sat, 12 November 2011 13:53]

Report message to a moderator

Previous Topic:SDO/POJO Bridge
Next Topic:update allocationSize of Sequence table
Goto Forum:
  


Current Time: Fri Sep 19 16:05:33 GMT 2014

Powered by FUDForum. Page generated in 0.03481 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software