[eclipselink-users] Deadlock errors using EclipseLink 2.1.2 JPA with SQL

[David Allen <David.Allen@xxxxxxxxxxxx>]

We have an enterprise product that uses EclipseLink JPA for persistence, using Oracle 11g as the underlying database.  We are attempting also to support Microsoft SQL Server 2008R2, but have run into an intermittent but quite common error that makes it unusable.  We are currently using EclipseLink 2.1.2 because it is the version supplied with Oracle WebLogic 10.3.3.  Database updates sometimes throw an exception due to a deadlock reported by the database.  We believe this is related to the granularity of locking – Oracle typically locks affected rows, but with SQL Server page or table locks may be used, leading to increased chance of deadlock.

 

When using Microsoft SQL Server through EclipseLink, there is no control on the granularity of database locks and no opportunity to retry an update after a deadlock.  EclipseLink decides  when to perform table locks, page locks, or individual row locks.

 

It is possible to supply hints to the SQL Server by including "WITH ROWLOCK" in the SQL statement

e.g UPDATE Products (WITH ROWLOCK) SET ProductCat = ‘Machine’ WHERE ProductSubCat = ‘Mac’

 

This may avoid the problem. 

 

Are there any recommendations for configuring EclipseLink to work reliably with SQL Server, avoiding deadlocks or possibilities for customisation that would allow us to override the default behaviour?

 

Best regards,

 

David

 

 

------------------------
David Allen, Development Manager – Core Technology
1Spatial Group Limited
Telephone: +44 (0)1223 420414
Fax:              +44 (0)1223 420044

david.allen@xxxxxxxxxxxx

 

Keep up to date with the latest 1Spatial news and events

http://www.1spatial.com/improve_your_data/

1Spatial Group Limited; Registered in England No. 4785688 VAT Reg. No. 816329821; Registered Office: Tennyson House; Cambridge Business Park; Cambridge; CB4 0WZ; United Kingdom

IMPORTANT NOTICE
 
This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the sender immediately and delete this e-mail from your system. Please note that any views or opinions presented in this e-mail are solely those of the author and do not necessarily represent those of 1Spatial Group Limited, its subsidiaries or associated companies, except where the author specifically states them to be the views of 1Spatial Group Limited, its subsidiaries or associated companies.
 
1Spatial Group Limited, its subsidiaries and associated companies will not be held liable for any legally binding obligations that are not the subject of an official 1Spatial purchase order or as part of a contract signed by a director of one of the aforementioned companies.
 
Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. You should understand and accept that, when communicating with us by e-mail, it is not a totally secure communications medium.