select for update [nowait | wait

起因

在平时的的项目中，我们可能会用到对与数据进行悲观锁，比如select for update的语法，然后该语句默认会等待其他事务完成（提交/回滚/超时）时，才会返回结果，在实际业务场景中，这不是理想的做法，理论上应该在超过指定时间没有获取到锁，则应该返回其他业务处理，而不是一直等待。

解决

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; 查询全局资源等待超时时间
• 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有效，目前，该参数正在提议准备修改单位为秒。

• 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)