| Deadlock exception while using jpa with eclipselink on SQL Server [message #1826182] |
Mon, 20 April 2020 17:30  |
 Vinit Sacheti
Messages: 2
Registered: April 2020 |
Junior Member |
|
|
I am repeatedly getting lot of deadlock exception.
I am trying to be as detailed as possible and taking out all the code which is not relevant.
**Database: SQL Server**
**Dependencies**
javax.persistence:javax.persistence-api:2.2
org.eclipse.persistence:eclipselink:2.7.0
**Entity Classes**
I just have two entity classes, Ticket and Tasks, and one Ticket can have many tasks.
```
@Entity
class Ticket {
//Ticket doesnt have any mention to task
...
String status;
//getters and setters
}
@Entity
class Task {
//There is no cascade for updates
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "request_id")
@MapsId("requestId")
private Ticket ticket;
}
```
**Service Class**
```
public void insertTicketAndTasks(Ticket ticket, ...) throws Exception {
//entityManagerFactory is a singleton initialized at startup
EntityManager entitymanager = entityManagerFactory.createEntityManager();
entitymanager.getTransaction().begin();
entitymanager.persist(ticket);
for (some loop) {
Task task = new Task(....);
entitymanager.persist(task);
}
entitymanager.getTransaction().commit();
entitymanager.close();
}
private List<Ticket> updateTickets(List<Ticket> tickets) {
////entityManagerFactory is a singleton initialized at startup
EntityManager entitymanager = entityManagerFactory.createEntityManager();
List<Ticket> ret = new ArrayList<>();
for (Ticket ticket : tickets) {
ticket.updateLastUpdatedByAndDate(LocalDateTime.now());
ret.add(entityManager.merge(ticket));
}
entitymanager.close();
return ret;
}
```
**Business Class**
```
Ticket ticket = new Ticket(....)
insertTicketAndTasks(ticket, ....)
//do some operation and set status based on that operation
ticket.setStatus(...);
updateTickets(Collections.singletonList(ticket));
```
Here is the exception trace
```
01:30:40.095 [main] DEBUG eclipselink.logging.sql - UPDATE ticket SET last_updated_date = ?, status = ?, VERSION = ? WHERE ((request_id = ?) AND (VERSION = ?))
bind => [2020-03-25T01:30:40.094, READY_TO_PUBLISH, 2, 001aff45-1f40-413a-be67-2167a4055854, 1]
01:30:51.197 [main] DEBUG eclipselink.logging.sql - SELECT 1
01:30:51.201 [main] WARN eclipselink.logging.all -
org.eclipse.persistence.exceptions.DatabaseException:
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Error Code: 1205
Call: UPDATE ticket SET last_updated_date = ?, status = ?, VERSION = ? WHERE ((request_id = ?) AND (VERSION = ?))
bind => [2020-03-25T01:30:40.094, READY_TO_PUBLISH, 2, 001aff45-1f40-413a-be67-2167a4055854, 1]
Query: UpdateObjectQuery(db.entity.Ticket@351ede23)
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:905)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:967)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:637)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:564)
```
I am sure there is no other process working on this ticket object.
Based on some other related posts, I update the SQL Server isolation settings to
```
SET ALLOW_SNAPSHOT_ISOLATION ON
SET READ_COMMITTED_SNAPSHOT ON
```
But that didn't help much.
|
|
|
|
|
| Re: Deadlock exception while using jpa with eclipselink on SQL Server [message #1826610 is a reply to message #1826358] |
Mon, 27 April 2020 17:43  |
Chris Delahunt
Messages: 1389
Registered: July 2009 |
Senior Member |
|
|
EclipseLink logging at finest levels should show every thing it does with the connection, so would rule it out as the culprit. But if you suspect a lock obtained in one EntityManager isn't getting released despite closing the EntityManager - you'd want to look at the code to make sure transactions are open/closed, and how connections are obtained and closed. Unless you are obtaining a cursor which requires an active connection, or some specialized (non-standard to JPA functionality) like having isolated client sessions that might obtain and hold onto a connection for their lifecycle, or using some cursor, EclipseLink is pretty good at releasing connections back to pools immediately after a query execution completes when it is responsible for doing so.
JTA transaction though are the responsibility of the container (or user) - EclipseLink doesn't start or commit them, and only listens for actions made to them. Most common though are DB config settings and driver issues, which unfortunately, I know little which would help.
Best Regards,
Chris
|
|
|
Powered by
FUDForum. Page generated in 0.02123 seconds
] 
Search
Help
Register
Login
Home
