Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » SEQUENCE table / row lock issue
SEQUENCE table / row lock issue [message #748495] 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 #748516 is a reply to message #748495] Mon, 24 October 2011 18:32 Go to previous message
James is currently offline James
Messages: 272
Registered: July 2009
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 : 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:
  


Current Time: Thu Oct 23 12:01:18 GMT 2014

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

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