CODEWITHSUNDEEP
mysqlspringdeadlockspring-transactions
Jeremy S.
Jeremy S.

Reputation: 6623

Spring @Transactional Deadlock

I have the following setup.

To save a record in the DB via Hibernate I have the following workflow

  1. send JSON {id:1,name:"test",children:[...]} to Spring MVC App and use Jackson to transform it into an object graph (if it is an existing instance the JSON has the proper ID of the record in the DB set

  2. save the object in DB via service layer call (details below)

    • the save function of service layer interface SomeObjectService has the @Transactional annotation on it with readOnly=false and Propagation REQUIRED

    • the implementation of this service layer SomeObjectServieImpl calls the DAO save method

    • the DAO saves the new data via a call of hibernate's merge e.g. hibernateTempate().merge(someObj)

    • hibernate merge loads the object first from the DB via SELECT

    • I have a EntityListener who is wired to spring (I used this technique Spring + EntityManagerFactory +Hibernate Listeners + Injection) and listens to @PostLoad

    • The listener uses a LockingServie to updates one field of someObject to set it as locked (this should actually only happen when someObject is loaded via Hibernate HQL,SQL or Criteria calls but gets called also on merge)

    • the LockingServie has a function lock(someObj,userId) which is also annotated with @Transactional with readOnly=false and REQUIRED

    • the update happens via a call of Query query = sess.createQuery("update someObj set lockedBy=:userId"); and then query.executeUpdate();

    • after merge has loaded the data it start with updating someObject and inserting relevant children (<= exacely here is the point where the deadlock happens)

  3. return JSON result (this also includes the newly created object ID) back to client.

The problem seems for me that first

  1. the record gets loaded in a transaction
  2. then gets changed in another (inner-)transaction
  3. and then should get updated again with the data of the outer transaction but can't get updated because it is locked.

I can see via MySQL's 

SHOW OPEN TABLES

that a child table (that is part of the object graph) is locked.

Interesting fact is that the deadlock doesn't occur on the someObj table but rather on a table that represents a child.

I am a bit lost here. Any help is more than welcome.

BTW can maybe the isolation level get me out of this problem here?

Upvotes: 3

Views: 6577

Answers (2)

Jeremy S.
Jeremy S.

Reputation: 6623

I ended up using @Bozho's HibernateExtendedJpaDialect

which is explained here >> Hibernate, spring, JPS & isolation - custom isolation not supported

To set the isolation to READ_UNCOMMITED

@Transactional(readOnly = false, propagation = Propagation.REQUIRED, isolation=Isolation.READ_UNCOMMITTED)
public Seizure merge(Seizure seizureObj);

Not a very nice solution I know but at least this solved my problem.

If somebody wanna have a detailed description please ask...

Upvotes: 1

Bozho
Bozho

Reputation: 597016

I don't know the solution to the problem, but I would not have a transactional lock method. If at all you need to lock something manually, make it within another transactional service method.

Upvotes: 1

Related Questions