Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.3.v20110304-r9073): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock
Error Code: 2049
Call: UPDATE FACILITY_ADDRESSES SET CITY = ?, DATE_COORDINATES_UPDATED = ? WHERE (ADDRESS_ID = ?)
bind => [Saint Petersburg, 2012-11-09 08:16:03.058, 256808]
We need t o go to production soon, Please help
The error doesn't really indicate a deadlock, just that it not obtain the lock before the transaction timed out. You need more information to determine why it is timing out, such as monitoring the load and other transactions that are occuring around that time period.
If it is because the transaction is a long running process, you may need to up the transaction timeout value so that it can complete in time, or break up the transaction into smaller parts. If it is because of other long running transactions or load, increasing the timeout might make things worse if it allows requests to start piling up waiting rather than timing out. You'll need to figure out what is going wrong and tune your application and servers according to usage patterns.
The Oracle TopLink documentation has some information and links on performance tuning here that might help:
Are you sure it is a database deadlock? Do you have other threads that have locks on the same row?
You could try setting the persistence unit property,
This will make the row access order for updates consistent, which can avoid deadlocks.
You could also consider using pessimistic locking for highly contended objects. If each thread locks the same object upfront before acquiring other locks, then the deadlock can be avoided.