Menu
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):
Case 2 (OK):
Case 3 (NOT OK):
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;
...
}
764
Avoid setting data source connection isolation level more restrictive than it requires. Isolation level has the same affect that pessimistic lock semantics do at the connection level. JPA requires the minimum of "read-committed" isolation level to achieve basic data integrity objective.
5.2 Deadlock Detection. InnoDB automatically detects transaction deadlocks and rolls back a transaction or transactions to break the deadlock. InnoDB tries to pick small transactions to roll back, where the size of a transaction is determined by the number of rows inserted, updated, or deleted.
To reduce the possibility of deadlocks, use transactions rather than LOCK TABLES statements; keep transactions that insert or update data small enough that they do not stay open for long periods of time; when different transactions update multiple tables or large ranges of rows, use the same order of operations (such ...
With the implementation of the InnoDB engine, MySQL offers a simplified and easy way to diagnose and better understand such deadlocks. The Innodb engine automatically detects it and kills one of the transactions, allowing one transaction to proceed and populating an error on the transaction that was rolled back.
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.
74
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
