Eclipse Community Forums: EclipseLink » SEQUENCE table / row lock issue

Home » Eclipse Projects » EclipseLink » SEQUENCE table / row lock issue

SEQUENCE table / row lock issue [message #748495]

Mon, 24 October 2011 14:18

Eclipse User

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 #748516 is a reply to message #748495]

Mon, 24 October 2011 14:32

Eclipse User

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 : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/

Previous Topic:	command line tool for generating JPA Entities?
Next Topic:	using a sequence connection pool in eclipselink

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Jul 22 20:14:36 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter