起因

在平時的的項目中瞳收，我們可能會用到對與數(shù)據(jù)進行悲觀鎖，比如select for update的語法厢汹，然后該語句默認會等待其他事務完成（提交/回滾/超時）時螟深，才會返回結(jié)果，在實際業(yè)務場景中烫葬，這不是理想的做法界弧，理論上應該在超過指定時間沒有獲取到鎖，則應該返回其他業(yè)務處理搭综，而不是一直等待垢箕。

解決

oracle

oracle的語法中，支持直接在select for update語句后面跟上[nowait | wait n]兑巾，nowait表示獲取不到鎖立即返回資源繁忙錯誤条获，wait n，n表示嘗試等待n秒后蒋歌，獲取不到鎖則返回資源繁忙錯誤帅掘。

mysql

在mysql中，select id,user_name from user_info where id=1 for update no wait; 會提示語法錯誤堂油，因為mysql不支持修档，那么mysql中有個全局變量@@innodb_lock_wait_timeout，單位為秒府框，該變量表示事務等待獲取資源等待的最長時間吱窝，超過這個時間還未分配到資源則會返回應用失敗。那么這正是我們想要的迫靖，該變量可以全局指定癣诱，也可以針對每個session指定。

• select @@innodb_lock_wait_timeout; 查詢?nèi)仲Y源等待超時時間
• set session innodb_lock_wait_timeout=0; 設置當前會話的資源等待超時時間

# wait 1 second
SET SESSION innodb_lock_wait_timeout = 1;

SELECT id, user_name FROM user_info WHERE id = 1 FOR UPDATE;

spring data jpa

在spring data jpa中袜香，如果使用了注解@Lock(LockModeType.PESSIMISTIC_WRITE)撕予，如果需要設置超時，可以使用查詢暗語@QueryHints(value = {@QueryHint(name = "javax.persistence.lock.timeout", value = "5000")})蜈首，然后以上的設置实抡，目前只針對oracle有效，目前欢策，該參數(shù)正在提議準備修改單位為秒吆寨。

• https://support.oracle.com/knowledge/Middleware/2101956_1.html
• http://jpwh.org/examples/jpwh2/jpwh-2e-examples-20151103/examples/src/test/java/org/jpwh/test/concurrency/Locking.java

// The next query's lock attempt must fail at _some_ point, and
// we'd like to wait 5 seconds for the lock to become available:
//
// - H2 fails with a default global lock timeout of 1 second.


// - Oracle supports dynamic lock timeouts, we set it with
//   the 'javax.persistence.lock.timeout' hint on the query:
//
//      no hint == FOR UPDATE
//      javax.persistence.lock.timeout 0ms == FOR UPDATE NOWAIT
//      javax.persistence.lock.timeout >0ms == FOR UPDATE WAIT [seconds]


// - PostgreSQL doesn't timeout and just hangs indefinitely if
//   NOWAIT isn't specified for the query. One possible way to
//   wait for a lock is to set a statement timeout for the whole
//   connection/session.
//   connection.createStatement().execute("set statement_timeout = 5000");

// - MySQL also doesn't support query lock timeouts, but you
//   can set a timeout for the whole connection/session.
//  connection.createStatement().execute("set innodb_lock_wait_timeout = 5;");

spring boot custom support MySQL & PostgreSQL

• https://github.com/rench/scio
• ScioJpaRepositoryFactoryBean.java

package com.scio.cloud.jpa;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.QueryHint;

import org.aopalliance.intercept.MethodInterceptor;
import org.aopalliance.intercept.MethodInvocation;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.dialect.PostgreSQL81Dialect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.aop.framework.ProxyFactory;
import org.springframework.core.annotation.AnnotatedElementUtils;
import org.springframework.data.jpa.repository.QueryHints;
import org.springframework.data.jpa.repository.query.AbstractJpaQuery;
import org.springframework.data.jpa.repository.query.JpaQueryMethod;
import org.springframework.data.jpa.repository.support.JpaRepositoryFactory;
import org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.core.RepositoryInformation;
import org.springframework.data.repository.core.support.QueryCreationListener;
import org.springframework.data.repository.core.support.RepositoryFactorySupport;
import org.springframework.data.repository.core.support.RepositoryProxyPostProcessor;
import org.springframework.util.ReflectionUtils;
/**
 * Custom JpaJpaRepository Bean
 *
 * @author Wang.ch
 * @qq 18565615@qq.com
 * @date 2019-03-07 17:16:17
 * @param <T>
 * @param <S>
 * @param <ID>
 */
public class ScioJpaRepositoryFactoryBean<T extends Repository<S, ID>, S, ID extends Serializable>
    extends JpaRepositoryFactoryBean<T, S, ID> {

  public ScioJpaRepositoryFactoryBean(Class<? extends T> repositoryInterface) {
    super(repositoryInterface);
  }
  /**
   * we can custom JpaRepositoryFactory class like addRepositoryProxyPostProcessor or
   * addQueryCreationListener etc.
   */
  @Override
  protected RepositoryFactorySupport createRepositoryFactory(EntityManager entityManager) {
    // return super.createRepositoryFactory(entityManager);
    JpaRepositoryFactory factory = new ScioJpaRepositoryFactory(entityManager);
    // factory.addQueryCreationListener(new ScioQueryCreationListener());
    factory.addRepositoryProxyPostProcessor(new ScioRepositoryProxyPostProcessor());
    return factory;
  }
  /**
   * Custom JpaRepositoryFactory
   *
   * @author Wang.ch
   * @date 2019-03-07 17:17:58
   */
  public class ScioJpaRepositoryFactory extends JpaRepositoryFactory {

    public ScioJpaRepositoryFactory(EntityManager entityManager) {
      super(entityManager);
    }
  }
  /**
   * Custom RepositoryProxyPostProcessor add advice to RepositoryProxy
   *
   * @author Wang.ch
   * @date 2019-03-07 17:18:13
   */
  public class ScioRepositoryProxyPostProcessor implements RepositoryProxyPostProcessor {

    @Override
    public void postProcess(ProxyFactory factory, RepositoryInformation repositoryInformation) {
      factory.addAdvice(LockTimeoutAdvice.INSTANCE);
    }
  }
  /**
   * LockTimeoutAdvice for MySQL and PostgreSQL
   *
   * @author Wang.ch
   * @date 2019-03-07 17:18:54
   */
  public enum LockTimeoutAdvice implements MethodInterceptor {
    INSTANCE;

    private static final Logger LOG = LoggerFactory.getLogger(LockTimeoutAdvice.class);
    /** hand invocation */
    @Override
    public Object invoke(MethodInvocation invocation) throws Throwable {
      List<QueryHint> list = getHints(invocation.getMethod());
      String str =
          list.stream()
              .filter(qh -> qh.name().equals("javax.persistence.lock.timeout"))
              .map(qh -> qh.value())
              .findFirst()
              .orElse(null);
      int lockTimeout = -1;
      if (StringUtils.isNotBlank(str)) {
        lockTimeout = NumberUtils.createInteger(str);
      }
      if (lockTimeout != -1) {
        Object target = invocation.getThis();
        // get EntityManager
        EntityManager em = getBeanProperty("em", target);
        Session session = em.unwrap(Session.class);
        SessionFactory factory = session.getSessionFactory();
        // get Dialect
        Dialect dialect = getBeanProperty("dialect", factory);
        String sql = null;
        if (MySQLDialect.class.isAssignableFrom(dialect.getClass())) {
          sql = "set innodb_lock_wait_timeout = " + (lockTimeout / 1000) + ";";
        } else if (PostgreSQL81Dialect.class.isAssignableFrom(dialect.getClass())) {
          sql = "set statement_timeout = " + lockTimeout;
        }
        if (StringUtils.isNotBlank(sql)) {
          final String lockTimeoutSql = sql;
          if (LOG.isDebugEnabled()) {
            LOG.debug("prepare to set locktimeout : {}", lockTimeoutSql);
          }
          session.doWork(s -> s.createStatement().execute(lockTimeoutSql));
        }
      }
      Object obj = invocation.proceed();
      return obj;
    }

    @SuppressWarnings("unchecked")
    private <T> T getBeanProperty(String name, Object target) throws NoSuchFieldException {
      Field field = target.getClass().getDeclaredField(name);
      ReflectionUtils.makeAccessible(field);
      Object em = ReflectionUtils.getField(field, target);
      return (T) em;
    }
    /**
     * find method hints
     *
     * @param m
     * @return
     */
    protected List<QueryHint> getHints(Method m) {
      List<QueryHint> result = new ArrayList<QueryHint>();
      QueryHints hints = AnnotatedElementUtils.findMergedAnnotation(m, QueryHints.class);
      if (hints != null) {
        result.addAll(Arrays.asList(hints.value()));
      }
      return result;
    }
  }
  /**
   * Query creation Listener
   *
   * @author Wang.ch
   * @date 2019-03-07 17:23:40
   */
  public class ScioQueryCreationListener implements QueryCreationListener<AbstractJpaQuery> {
    @Override
    public void onCreation(AbstractJpaQuery query) {
      List<QueryHint> list = getHints(query.getQueryMethod());
      if (CollectionUtils.isNotEmpty(list)) {
        list.stream().forEach(System.out::println);
      }
    }

    protected List<QueryHint> getHints(JpaQueryMethod query) {
      List<QueryHint> result = new ArrayList<QueryHint>();
      Field field = null;
      try {
        field = JpaQueryMethod.class.getDeclaredField("method");
      } catch (NoSuchFieldException | SecurityException e) {
        e.printStackTrace();
      }
      if (field == null) {
        return Collections.emptyList();
      }
      ReflectionUtils.makeAccessible(field);
      Method m = (Method) ReflectionUtils.getField(field, query);
      QueryHints hints = AnnotatedElementUtils.findMergedAnnotation(m, QueryHints.class);
      if (hints != null) {
        result.addAll(Arrays.asList(hints.value()));
      }
      return result;
    }
  }
}

• @EnableJpaRepositories on bootstrap class

@EnableJpaRepositories(repositoryFactoryBeanClass=ScioJpaRepositoryFactoryBean.class)