Hibernate使用query.list() 设置LockOptions.UPGRADE 对oracle数据无效而对postgresql数据库有效问题

现象：设置query.setLockOptions(LockOptions.UPGRADE）加锁查询数据 并对查到数据进行修改发现，连接oracle数据库ThreadB并发查询的时候得到的是ThreadA线程修改前的数据（有问题数据），连接postgresql数据库ThreadB并发查询的时候得到的是ThreadA线程修改后的数据（正确数据）；

总结：本人将5.0.11版本升至5.6.5版本解决这个问题（总结之所以放在上面是为了比较着急处理现场问题有不想看源码的小伙伴，如果想了解问题根源，需看源代码问题所在代码）

针对这一现象首先想到的是自己配置的事务隔离级别，read-only,事务传播属性等是否配置正确，经过一番猜想验证发现完全没有任何效果，没办法只能对hibernate进行源码调试，经过调试发现Loader类中shouldUseFollowOnLocking（）的if ( dialect.useFollowOnLocking() ) 是这个问题的关键所在，postgre返回的是false，oracle返回的是true，如下图所示：

protected boolean shouldUseFollowOnLocking(
      QueryParameters parameters,
      Dialect dialect,
      List<AfterLoadAction> afterLoadActions) {
   if ( dialect.useFollowOnLocking() ) {
      // currently only one lock mode is allowed in follow-on locking
      final LockMode lockMode = determineFollowOnLockMode( parameters.getLockOptions() );
      final LockOptions lockOptions = new LockOptions( lockMode );
      if ( lockOptions.getLockMode() != LockMode.UPGRADE_SKIPLOCKED ) {
         LOG.usingFollowOnLocking();
         lockOptions.setTimeOut( parameters.getLockOptions().getTimeOut() );
         lockOptions.setScope( parameters.getLockOptions().getScope() );
         afterLoadActions.add(
               new AfterLoadAction() {
                  @Override
                  public void afterLoad(SessionImplementor session, Object entity, Loadable persister) {
                     ( (Session) session ).buildLockRequest( lockOptions ).lock(
                           persister.getEntityName(),
                           entity
                     );
                  }
               }
         );
         parameters.setLockOptions( new LockOptions() );
         return true;
      }
   }
   return false;
}

 

 

 上面返回true就直接返回sql语句，返回false则执行applyLocksToSql（）方法对sql语句进行加for update 加锁

protected String applyLocks(
      String sql,
      QueryParameters parameters,
      Dialect dialect,
      List<AfterLoadAction> afterLoadActions) throws QueryException {
 
   final LockOptions lockOptions = parameters.getLockOptions();

   if ( lockOptions == null ||
         ( lockOptions.getLockMode() == LockMode.NONE && lockOptions.getAliasLockCount() == 0 ) ) {
      return sql;
   }
   if ( shouldUseFollowOnLocking( parameters, dialect, afterLoadActions ) ) {
      return sql;
   }
   final LockOptions locks = new LockOptions( lockOptions.getLockMode() );
   final Map<String, String[]> keyColumnNames = dialect.forUpdateOfColumns()
         ? new HashMap<>()
         : null;

   locks.setScope( lockOptions.getScope() );
   locks.setTimeOut( lockOptions.getTimeOut() );

   for ( Map.Entry<String, String> entry : sqlAliasByEntityAlias.entrySet() ) {
      final String userAlias = entry.getKey();
      final String drivingSqlAlias = entry.getValue();
      if ( drivingSqlAlias == null ) {
         throw new IllegalArgumentException( "could not locate alias to apply lock mode : " + userAlias );
      }
      
      final QueryNode select = (QueryNode) queryTranslator.getSqlAST();
      final Lockable drivingPersister = (Lockable) select.getFromClause()
            .findFromElementByUserOrSqlAlias( userAlias, drivingSqlAlias )
            .getQueryable();
      final String sqlAlias = drivingPersister.getRootTableAlias( drivingSqlAlias );

      final LockMode effectiveLockMode = lockOptions.getEffectiveLockMode( userAlias );
      locks.setAliasSpecificLockMode( sqlAlias, effectiveLockMode );

      if ( keyColumnNames != null ) {
         keyColumnNames.put( sqlAlias, drivingPersister.getRootTableIdentifierColumnNames() );
      }
   }
   // apply the collected locks and columns
   return dialect.applyLocksToSql( sql, locks, keyColumnNames );
}

//对sql语句加for update
public String applyLocksToSql(String sql, LockOptions aliasedLockOptions, Map<String, String[]> keyColumnNames) {
   return sql + new ForUpdateFragment( this, aliasedLockOptions, keyColumnNames ).toFragmentString();
}

protected final ResultSet getResultSet(
      final PreparedStatement st,
      final RowSelection selection,
      final LimitHandler limitHandler,
      final boolean autodiscovertypes,
      final SharedSessionContractImplementor session) throws SQLException, HibernateException {
   try {
//查看具体的sql语句
      ResultSet rs = session.getJdbcCoordinator().getResultSetReturn().extract( st );

      return preprocessResultSet( rs, selection, limitHandler, autodiscovertypes, session );
   }
   catch (SQLException | HibernateException e) {
      session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release( st );
      session.getJdbcCoordinator().afterStatementExecution();
      throw e;
   }
}

 从图中可以看出对oracle来说是不加锁查询的，然后将查询到的结果加锁，这就ThreadB获取到的数据是ThreadA修改前的数据；

经过对比新版本发现已经对这个BUG进行过修改，源码如下：