10.8. Follow-on-locking

<div class="paragraph">

When using Oracle, the [`FOR UPDATE` exclusive locking clause](https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF55371) cannot be used with:

</div>
<div class="ulist">

• DISTINCT
• GROUP BY
• UNION

• inlined views (derived tables), therefore, affecting the legacy Oracle pagination mechanism as well. </div>

  For this reason, Hibernate uses secondary selects to lock the previously fetched entities.

  </div>

  Example 280. Follow-on-locking example

  `List<Person> persons = entityManager.createQuery(

  "select DISTINCT p from Person p", Person.class)
  

  .setLockMode( LockModeType.PESSIMISTICWRITE ) .getResultList();</pre> </div> </div> <div class="listingblock"> <div class="content"> <pre class="prettyprint highlight">SELECT DISTINCT p.id as id1_0, p."name" as name20 FROM Person p

  SELECT id FROM Person WHERE id = 1 FOR UPDATE

  SELECT id FROM Person WHERE id = 1 FOR UPDATE`</pre> </div> </div> </div> </div>

  </td>

  To avoid the N+1 query problem, a separate query can be used to apply the lock using the associated entity identifiers.

  </div> </td> </tr> </table> </div>

  Example 281. Secondary query entity locking

  `List<Person> persons = entityManager.createQuery(

  "select DISTINCT p from Person p", Person.class)
  

  .getResultList();

  entityManager.createQuery(

  "select p.id from Person p where p in :persons")
  

  .setLockMode( LockModeType.PESSIMISTICWRITE ) .setParameter( "persons", persons ) .getResultList();</pre> </div> </div> <div class="listingblock"> <div class="content"> <pre class="prettyprint highlight">SELECT DISTINCT p.id as id1_0, p."name" as name20 FROM Person p

  SELECT p.id as col0_0 FROM Person p WHERE p.id IN ( 1 , 2 ) FOR UPDATE`</pre> </div> </div> </div> </div>

  The lock request was moved from the original query to a secondary one which takes the previously fetched entities to lock their associated database records.

  </div>

  Prior to Hibernate 5.2.1, the the follow-on-locking mechanism was applied uniformly to any locking query executing on Oracle. Since 5.2.1, the Oracle Dialect tries to figure out if the current query demand the follow-on-locking mechanism.

  </div>

  Even more important is that you can overrule the default follow-on-locking detection logic and explicitly enable or disable it on a per query basis.

  </div>

  Example 282. Disabling the follow-on-locking mechanism explicitly

  `List<Person> persons = entityManager.createQuery(

  "select p from Person p", Person.class)
  

  .setMaxResults( 10 ) .unwrap( Query.class ) .setLockOptions(

  new LockOptions( LockMode.PESSIMISTIC_WRITE )
      .setFollowOnLocking( false ) )
  

  .getResultList();</pre> </div> </div> <div class="listingblock"> <div class="content"> <pre class="prettyprint highlight">SELECT * FROM (

  SELECT p.id as id1_0_, p."name" as name2_0_
  FROM Person p
  

  ) WHERE rownum <= 10 FOR UPDATE`</pre> </div> </div> </div> </div>

  </td>

  The follow-on-locking mechanism should be explicitly enabled only if the current executing query fails because the FOR UPDATE clause cannot be applied, meaning that the Dialect resolving mechanism needs to be further improved.

  </div> </td> </tr> </table> </div> </div> </div> </div>