Spring Transaction management with tomcat and mysql

[popprem]

Hi all,

I've got lot of confusions after googling for spring transactions with eclipselink, tomcat and mysql. Please consider the following questions and guide me on this topic.

1. Can i run spring transactions with eclipseLink, tomcat and mysql enviornment? if so how is the config? i have used the following config and i get lock exceptions always.

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 ;
	}

Am i doing anything wrong here? I'm not sure whether spring transaction handling works correctly with tomcat since i'm not using JTA transactions.


2. With EclipseLInk and mysql, Id generation strategy goes with Sequence table and in the table only one row is updated for all transactions. I suspect that this causes lock issues. Am i correct? If so, how can i avoid this?

ID generation config in a Domain class is like this:

    @Id
    @Column(name = "some_id", unique = true, nullable = false)
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

In mysql schema, a new table named SEQUENCE is created and a value is stored in it. Each time when a row is inserted, the id is taken from here i think. Since the same value is read and updated, i suspect that this can cause locking issues.
If i'm correct, how can i avoid this issue??

Looking forward for your answers.

[popprem]

I'm stuck with this issue.. your answers are much appreciated...

[Marten Deinum]

I suggest a read of the reference guide which explains transactions. There is no problem using mysql, eclipselink in a Tomcat environment. If you get lock exceptions either transactions aren't applied or you have to much concurrent threads and to long running transactions. (Check your stack trace, there should be a transaction interceptor if it isn't you basically aren't using transactions).

[popprem]

Thanks Marten.

I can see the transaction interceptor in the stack trace, so it is clear that transactions are being applied.

According to the following statements i saw in logs today, i understand that this is something to do with my second question which is based on generation strategy.

 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 = ?")]

The ID generation strategy AUTO in eclipselink, works by updating the only value available in SEQUENCE table. When multiple updates goes for this value in transactions, lock occurs i guess.

How can i solve this issue?

Thanks.

[Marten Deinum]

If your transactions take to long to complete you get locks. Which still makes that your transaction management is incorrectly configured IMHO either that or you are doing something which is generating new transactions/locks because you are using jpa in a wrong way.

[popprem]

you are doing something which is generating new transactions/locks because you are using jpa in a wrong way

I have mentioned my spring - eclipselink configurations in the question. In Dao layer, i'm using eclipselink like this:

        private EntityManager entityManager;

	@PersistenceContext
	public void setEntityManager(EntityManager entityManager) {
		this.entityManager = entityManager;
	}

        public void saveSumthg(Sumthg sumthg) throws AppException {
		try {
             entityManager.persist(sumthg);
		} catch (Exception e) {
			logger.debug("Error in saving. Reason: " + e.getMessage());
			throw new AppException (e.getMessage());
		}
	}

Please point out if i'm doing anything wrong.

Thanks.

[asavasamuel]

Marten Deinum


Here is an ORM that works with MySQL
https://www.kellermansoftware.com/p-...ess-layer.aspx