0

we have a table called job which has a self referencing key. We are using JPA and eclipselink as the JPA provider. Sometimes we are getting the following exception

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.sybase.jdbc3.jdbc.SybSQLException: Your server command (family id #0, process id #384) encountered a deadlock situation. Please re-run your command.

We have an action in our UI which when performed a JSM message will go to some external component and a record will be created in our job table and then the job id will be sent to client and he will be redirected to the jobs view which lists all jobs in the table. After he is redirected the client will send an ajax request to list all jobs. While this operation is going we will receive notifications from external components and then we update the jobs table records.

I strongly believe that while the select operation is going we are trying to update the table and this is happening. Can anyone please tell me how to solve this problem.

Thank you all in advance good day.

Krishna Chaitanya
  • 2,533
  • 4
  • 40
  • 74
  • do you have index in your jobs table? try to add it if you have it. – Felquir Aug 22 '13 at 10:23
  • index? I dint get you. Do you mean primary key by index. And where should I add it? Here in the comment? Are you asking me to give the table description – Krishna Chaitanya Aug 22 '13 at 10:54
  • The tables involved in the process, could be useful – Felquir Aug 22 '13 at 11:18
  • I still cannot understand you. There is only one table involved in this process. If you don't mind please explain me a bit detail. I am sorry if I ask you the same thing each time – Krishna Chaitanya Aug 22 '13 at 11:52
  • the index will help to resolve the situation, read this http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html – Felquir Aug 22 '13 at 12:00
  • Thank you I will check and get back to you. Thanks for the support – Krishna Chaitanya Aug 22 '13 at 12:10
  • Actually I introduced a lock on the job table while fetching the jobs. Its a share lock I introduced. Now while a process is reading from the job table and the if the application receives a message from JMS, that process is again trying to get a lock on the same table and it will not happen. Once the first process finishes the updation takes place. I think this will work. Any comments? – Krishna Chaitanya Aug 23 '13 at 15:09
  • Adding the above lock while reading created some problems with the update functionality – Krishna Chaitanya Aug 23 '13 at 15:58

1 Answers1

0

You may be able to get around the select/update conflict by changing the locking scheme for the table, in addition to having good indexes.

Sybase has good documentation on this here:

Performance and Tuning Series: Locking and Concurrency Control

Mike Gardner
  • 6,611
  • 5
  • 24
  • 34