| We recently upgraded from Hibernate 3.x to Hibernate 5.1 and were shocked to find locking is broken. The implemented solution to
 HHH-1168 - Problem combining locking and paging on Oracle Complete (Problem combining locking and paging on Oracle) seems to be the cause. As pointed out in this comment https://hibernate.atlassian.net/browse/HHH-8135?focusedCommentId=60556&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-60556 , first reading data and locking it afterwards inherently leads to concurrency issues around locking because data might have changed between read and lock, which leads to working with stale data. We're developing a financial application and our tests immediately pointed us at the broken locking behavior as transactions were working on stale data. This is absolute worst case and frankly makes locking useless altogether. I read through
HHH-1168 - Problem combining locking and paging on Oracle Complete and understand the issue with Oracle being difficult at what query combination to allow with locking, but the implemented solution makes it worse for everyone because it breaks locking for every query, not only those with pagination. A proposed solution would be:
- First select candidates with pagination parameters but without locking.
- Next, select by ID (use ID's found in previous query), select full row and lock.
- Make sure all IDs from the first query were selected in the second one. If not, retry.
Use data from the second query (instead of the first)! This avoids selecting stale data. While I'm aware this still doesn't solve the issue (data may still vary between the first and the second query), it makes it less critical for the vast majority of queries. As a workaround, we rolled back to the 10g dialect (which still used rownums) and have overwritten the FollowOnLocking flag in it - which is the only viable solution for us - and, as a matter of fact, for everyone requiring locking that actually works... Please reconsider the follow-on locking implementation! |