[hibernate-issues] [JIRA] (HHH-14043) Two concurrent element shifts in a list damage database integrity.

Björn Zurmaar ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5ecd86c... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiYjA5ZjlkY2Jh... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-14043?atlOrigin=eyJpIjoiYjA5Zj... ) HHH-14043 ( https://hibernate.atlassian.net/browse/HHH-14043?atlOrigin=eyJpIjoiYjA5Zj... ) Two concurrent element shifts in a list damage database integrity. ( https://hibernate.atlassian.net/browse/HHH-14043?atlOrigin=eyJpIjoiYjA5Zj... ) Change By: Björn Zurmaar ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5ecd86c... ) *Problem description:* Assume you have an entity Foo having a list of Bars. It's a unidirectional OneToMany  mapping with a @JoinColumn annotation. When shifting element in the lists from within two different transactions starting concurrently the database gets inconsistent and elements disappear when the range of affected elements overlaps. *Example:* when you have a list with three elements and shift element 0 to index 1 in one and element 2 to 1 in another transaction then you'll end up with a table with two indices 1 and one index 2. Reloading the collection in hibernate in a new transaction the element at index 0 is then 0. More generally speaking arbitrary list elements may vanish in this scenario when working with bigger lists and other elements to be shifted. There is also no way to reinsert the vanished element into the collection unless you have it cached somewhere else. Here is the relevant code from the attached reproducer. {code:java}  // Step 1: We create a Foo containing three bars and persist it. EntityManager entityManager = entityManagerFactory.createEntityManager(); entityManager.getTransaction().begin(); Foo foo = new Foo(); foo.bars.add(new Bar("bar1")); foo.bars.add(new Bar("bar2")); foo.bars.add(new Bar("bar3")); entityManager.persist(foo); entityManager.getTransaction().commit(); entityManager.close(); // Step 2: We start two transactions reading the Foo created in step 1. EntityManager e1 = entityManagerFactory.createEntityManager(); e1.getTransaction().begin(); Foo foo1 = e1.find(Foo.class,"foo"); EntityManager e2 = entityManagerFactory.createEntityManager(); e2.getTransaction().begin(); Foo foo2 = e2.find(Foo.class,"foo"); // In transaction 1 we move element 0 to index 1 and commit. Bar bar1 = foo1.bars.remove(0); foo1.bars.add(1,bar1); e1.getTransaction().commit(); e1.close(); // In transaction 2 we move element 2 to index 1 and commit. Bar bar2 = foo2.bars.remove(2); foo2.bars.add(1,bar2); e2.getTransaction().commit(); e2.close(); // Now when load Foo again and log its content. EntityManager e3 = entityManagerFactory.createEntityManager(); Foo foo3 = e3.find(Foo.class,"foo"); Logger.getLogger(getClass()).info(foo3); // We also log the indices found in the table. for(Object row : e3.createNativeQuery("SELECT bars_ORDER FROM bar;").getResultList()) { Logger.getLogger(getClass()).info(row); }{code} The output of the logging statements I inserted is {noformat}2020-05-27 12:37:43 INFO  JPAUnitTestCase:68 - [null, bar3, bar2] 2020-05-27 12:37:51 INFO  JPAUnitTestCase:72 - 1 2020-05-27 12:37:52 INFO  JPAUnitTestCase:72 - 2 2020-05-27 12:37:53 INFO  JPAUnitTestCase:72 - 1{noformat} *Analysis:* This behavior is caused by the way hibernate rewrites the list's indices. Only affected indices will be updated. When the shift affects n list elements only the indices of the n affected elements will be updated. Hibernate generates 2*n UPDATE statements for this. The first n to set all the affected indices to null, the second n statements to write the new index to the rows. This is a perfectly fine and clever optimization in a single transactioned / threaded environment but fails when multiple transactions are involved. That is because the transaction manager does not know about lists and applies both changes consecutively. It does not have a chance to detect the resulting conflict because the changes are incomplete from a transactional point of view. *Suggestion:* Hibernate should update _all_ list indices when it detects that elements of a collection were moved. Regardless of the transaction interleaving this would guarantee that database remains in a consistent state. The transaction that is closed last would then define the state without damaging database consistency. I have a attached a unit test exposing the undesired behavior. Sorry for the two attachments. Despite the name they are the same. ( https://hibernate.atlassian.net/browse/HHH-14043#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-14043#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100127- sha1:a588176 )