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

[James Sutherland <jamesssss@xxxxxxxxx>]

>> EclipseLink decides  when to perform table locks, page locks, or
individual row locks.

EclipseLink does not do any locking it just execute DML, SQL Server is
deciding what type of locking to use.

You may need to configure SQL Server on the database to change the
transaction isolation level or type of locking used by default.

It is possible to change the update SQL, you would need to use a
DescriptorCustomizer to set the updateSQLString on the
DescriptorQueryManager.  You will use #<column> to define the parameters.

Some database are better at handling concurrency than others, so you may
need to reduce your concurrency of your application or live with the
deadlock errors if switching databases.

You could also try using pessimistic locking to attempt to control access to
the objects.


David Allen-15 wrote:
> 
> 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<mailto:david.allen@xxxxxxxxxxxx>
> 
> 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.
> 
> 
> 


-----
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
Blog:  http://java-persistence-performance.blogspot.com/ Java Persistence
Performance 
-- 
View this message in context: http://old.nabble.com/Deadlock-errors-using-EclipseLink-2.1.2-JPA-with-SQL-Server-2008R2-tp32333411p32380006.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.