Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Deadlock exception while using jpa with eclipselink on SQL Server
Deadlock exception while using jpa with eclipselink on SQL Server [message #1826182] Mon, 20 April 2020 17:30 Go to next message
Vinit Sacheti is currently offline Vinit SachetiFriend
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 #1826229 is a reply to message #1826182] Tue, 21 April 2020 15:17 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1388
Registered: July 2009
Senior Member
Database thinks something was involved in a lock on that row, but the code you've shown isn't enough - you've only shown two processes/transactions that might only overlap if you have DB settings that require full table locks. Your best bet is to check with the database what it thinks is involved in the locking, somewhat described in this post: https://stackoverflow.com/a/5391427/496099 . Might want to check your related tables, like Tasks, as a foreign key constraint may cause it to check/lock the ticket table despite not making changes to the tickets directly - cascade delete constraint checks must prevent inserts from occurring.

You seem to have SQL logging enabled, and if this is the only app running, should show you the other SQL statements outside this transaction that are occurring at the same time and help track it down.
Re: Deadlock exception while using jpa with eclipselink on SQL Server [message #1826358 is a reply to message #1826229] Fri, 24 April 2020 00:56 Go to previous messageGo to next message
Vinit Sacheti is currently offline Vinit SachetiFriend
Messages: 2
Registered: April 2020
Junior Member
Thanks for your reply.

I never delete any tasks, so the task should not lock the ticket table. Also we don't use full table lock.

Also I did extensive debugging before, and didnt find any update on the same row, so as to lock that particular ticket. Thats why it was so hard to debug this issue.
We use a managed sql-server so its hard to debug this issue from the sql-server side but I will try that too.

I see this issue in multiple stack-overflow and was just wondering if it has anything do with combination of eclipse-link and sql-server. I have a doubt that maybe the lock is not getting released even after closing the EntityManager so when another
EntityManager is use to update the same Ticket object, its complains about the deadlock

We are also running multiple threads and they are all using same javax.persistence.EntityManagerFactory object. I read from multiple places that this object is ThreadSafe. Also reducing the parallelism greatly reduces the deadlock.

Please let me know if you need anything from my side to debug this issue. I am happy to share anything needed to help you in any way.
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 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1388
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
Previous Topic:Embeddable constants incorrectly initialize to the same value
Next Topic:EclipseLink 2.7.4 mixed approach - Native TopLinkProject and JPA approaches don't work together
Goto Forum:
  


Current Time: Wed Jun 16 02:58:26 GMT 2021

Powered by FUDForum. Page generated in 0.06601 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top