Deadlock not detected during MySQL Hibernate JPA transaction

Question

WARNING!!! TL;DR

MySQL 5.6.39  
mysql:mysql-connector-java:5.1.27
org.hibernate.common:hibernate-commons-annotations:4.0.5.Final  
org.hibernate.javax.persistence:hibernate-jpa-2.1-api:1.0.0.Final  
org.hibernate:hibernate-core:4.3.6.Final
org.hibernate:hibernate-entitymanager:4.3.6.Final  
org.hibernate:hibernate-validator:5.0.3.Final

HTTP Method: POST, API path: /reader

Entity "reader" Engine: innoDB

id
name
total_pages_read

Class Mapping:

@Entity
@Table(name = "reader")
public class Reader{
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "total_pages_read")
    private Long total_pages_read;
    
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "reader", orphanRemoval = true)
    private Set<Book_read> book_reads;

    ...
}

I use method createEntity() and recalculateTotalPageRead() in a Reader write service class:

@Service
public class ReaderWritePlatformServiceJpaRepositoryImpl{
    private final ReaderRepositoryWrapper readerRepositoryWrapper;
   
    ...

    @Transactional
    public Long createEntity(final Long id, final String name, final Long total_pages_read){
        try {
            final Reader reader = new Reader(id, name, total_pages_read);
            this.readerRepositoryWrapper.saveAndFlush(reader);

            return 1l;
        } catch (final Exception e) {
            return 0l;
        }
    }
    
    ...
}

HTTP Method: POST, API path: /bookread

Entity "book_read" Engine: innoDB

id  
reader_id  
book_title  
number_of_pages 

Class Mapping:

@Entity
@Table(name = "book_read")
public class Book_read{
    @Column(name = "id")
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "reader_id")
    private Reader reader;

    @Column(name = "book_title")
    private String book_title;

    @Column(name = "number_of_pages")
    private Long number_of_pages;
    
    ...
}

I use method createEntity() and recalculateTotalPageRead() in a Book_read write service class:

@Service
public class Book_readWritePlatformServiceJpaRepositoryImpl{
    private final ReaderRepositoryWrapper readerRepositoryWrapper;
    private final Book_readRepositoryWrapper bookReadRepositoryWrapper;
    
    ...

    @Transactional
    public Long createEntity(final Long id, final Long reader_id, final String book_title, final Long number_of_pages){
        try {
            final Reader reader = this.readerRepositoryWrapper.findOneWithNotFoundDetection(reader_id);

            final Book_read book_read = new Book_read(id, reader, book_title, number_of_pages);
            this.bookReadRepositoryWrapper.saveAndFlush(book_read);

            this.recalculateTotalPageRead(reader);

            return 1l;
        } catch (final Exception e) {
            return 0l;
        }
    }

    private void recalculateTotalPageRead(final Reader reader){
        Long total_pages_read =  Long.valueOf(0);
        Set<Book_read> book_reads = reader.getBook_reads();
        for (Book_read book_read : book_reads){
            total_pages_read += book_read.getNumber_of_pages();
        }

        reader.setTotal_pages_read(total_pages_read);
        this.readerRepositoryWrapper.saveAndFlush(reader);
    }

    ...
}

When I try to create both entities:

Sample "reader" :

id | name       | total_pages_read
-----------------------------------
1  | Foo Reader | 0(by default)

Sample "book_read": 2 separated POST method calls

id | reader_id | book_title | number_of_pages 
---------------------------------------------
1  | 1         | Foo Book   | 2
2  | 1         | Bar Book   | 3

Expecting change on entity "reader" after creating "book_read"-s as above sample:

Sample Reader:

id | name       | total_pages_read
-----------------------------------
1  | Foo Reader | 5

But from what I've been experiencing there happens to be 3 cases while creating those 2 "book_read" records concurrently:

Case 1 (OK):

• 1st "book_read" finished creating
• Get any existing "book_read" of "reader" id 1 into a list "book_reads". "book_reads" size = 1.
• Add number_of_pages of each "book_read" in the list to total_pages_read of "reader" id 1. Current total_pages_read = 2.
• Start creating 2nd "book_read"
• 2nd "book_read" finished creating
• Get any existing "book_read" of "reader" id 1 into a list "book_reads". "book_reads" size = 2.
• Add number_of_pages of each "book_read" in the list to total_pages_read of "reader" id 1.
• Final result: total_pages_read = 5.

Case 2 (OK):

• (Transaction 1) Start creating 1st "book_read"
• (Transaction 2) Start creating 2nd "book_read"
• (Transaction 1) 1st "book_read" finished creating
• (Transaction 2) 2nd "book_read" finished creating
• (Transaction 1) Get any existing "book_read" of "reader" id 1 into a list "book_reads". "book_reads" size = 1.
• (Transaction 2) Get any existing "book_read" of "reader" id 1 into a list "book_reads". "book_reads" size = 1.
• (Transaction 1) Add number_of_pages of each "book_read" in the list to total_pages_read of "reader" id 1. Current total_pages_read = 2.
• (Transaction 2) Add number_of_pages of each "book_read" in the list to total_pages_read of "reader" id 1. Deadlock Exception Thrown.
• Retry (Transaction 2) Start creating 2nd "book_read"
• (Transaction 2) 2nd "book_read" finished creating
• (Transaction 2) Get any existing "book_read" of "reader" id 1 into a list "book_reads". "book_reads" size = 2.
• Add number_of_pages of each "book_read" in the list to total_pages_read of "reader" id 1.
• Final result: total_pages_read = 5.

Case 3 (NOT OK):

• 1st "book_read" finished creating
• Get any existing "book_read" of "reader" id 1 into a list "book_reads". "book_reads" size = 1.
• Add number_of_pages of each "book_read" in the list to total_pages_read of "reader" id 1. Current total_pages_read = 2.
• Start creating 2nd "book_read"
• 2nd "book_read" finished creating
• Get any existing "book_read" of "reader" id 1 into a list "book_reads". "book_reads" size = 1.
• Add number_of_pages of each "book_read" in the list to total_pages_read of "reader" id 1. Current total_pages_read = 3. Not Detecting Deadlock.
• Final result: total_pages_read = 3.

How do I fix case 3?

Cheers, Happy programming :D

SOLVED!

Optimistic Locking Implementation on data model

@Entity
@Table(name = "reader")
public class Reader{
    @Version
    @Column(name = "version")
    private int version;
    
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @Column(name = "total_pages_read")
    private Long total_pages_read;
    
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "reader", orphanRemoval = true)
    private Set<Book_read> book_reads;

    ...
}

Tman

Sounds like you need your java functions to instigate a wrapped SQL transaction, because without it hibernate is just generating SQL without any transaction content and not subject to repeatable read constraints. What SQL is the total_pages_read updating mechamism using? – Demure

@Demure Sorry for omitting @Transactional annotation above my createEntity() methods. I've jst added them into the code snippet. Thanks for pointing out. By the way, I don really get your last question about the updating mechanism. P.S: m still new to Java EE, thanks for tolerating :D – Tman

Show us the generated SQL. – Overlarge

Which isolation level are you using in MySQL? Is it REPEATABLE READ? – Calondra

Answer 1

What you have experienced is called a lost update and it's really not a JPA-level problem, you can easily reproduce this in MySQL shell. I'm assuming you did not do any changes in the database itself, so your default transaction isolation level is REPEATABLE READ.

In MySQL, REPEATABLE READ does not detect possible lost updates (even though that's the common understanding of this isolation level). You can check this answer on SO and the comments thread to learn more.

Basically with the use of MVCC, MySQL tries to avoid contention and deadlocks. In your case you will have to make a trade-off and choose to sacrifice some speed for the sake of consistency.

Your options are to use a SELECT ... FOR UPDATE statement or to set a more strict isolation level, which is SERIALIZABLE (you can do this for individual transaction). Both of these options will block the reads until the concurrent transaction commit/rollback. Thus you will see the consistent view of your data, just a bit later (or a lot later, depending on the application's requirements).

You can also read up on this here, here and here.

Concurrency is hard. :)

UPDATE: After thinking about comments below, there is actually another option that you have: implement Optimistic Locking for your data model. JPA has a support for this, please take look here and here. What you achieve is basically the same, but with a bit different approach (you will have to restart the transactions that failed to to mismatched versions) and less contention due to less locking.

Calondra

MySQL is pessimistic locking. Optimistic is things like Galera that fail on commit time. – Demure

@Demure Basically, MVCC is an extension of optimistic locking approach. I'm not sure if it's technically correct to say so, but that's my general perception. – Calondra

MVCC is a concept that can be implemented using either optimistic or pessimistic approachs. The innodb exclusive locks and myisam table locks are an example of pessimistic prevention of conflicts. By contrast galera doesn't take notice of transactions on other nodes and optimisticly assumes the transaction will succeed. Its only at commit time that certification is done to validate this optimistic assumption. – Demure

@Demure You are right sir. I've confused OL and OCC (optimistic concurrency control) which is a different thing. – Calondra

Answer 2

I think the problem you are facing is that locking of attributes for FK relationships is controlled by the owning side of the relationship.

Since the book_reads collection is annotated with @OneToMany(mappedBy = "reader") it does not control the locks so the other side does, which means you get two separate locks when updating the collection which don't really recognize each other.

Removing Book_read.reader and the mappedBy annotation should fix that.

All this actually applies to optimistic locking with a version attribute, which is the recommended way to do things anyway.

See also this article by Vlad Mihalcea about the topic: https://vladmihalcea.com/hibernate-collections-optimistic-locking/

Deliberative

javax.persistence only gives mappedBy to @OneToMany. And as I explained, there's a case that lock is being detected. Or do I get ur explanation wrong? xD. Please enlighten me more. Thanks. – Tman

You are right, hm. I guess that leaves the choice of making it a unidirectional relationship. I updated my answer accordingly. I don't think the deadlock is a sign of something partially working as you seem to suggest but of something broken in a bad way. Which in my opinion, is the fact that locks happen on multiple objects in different order. All the locking should really be controlled by what I consider the Aggregate root: Reader. – Deliberative