mysql no wait,select for update [nowait | wait n] support Oracle，MySQL，PostgreSQL

起因

在平时的的项目中，我们可能会用到对与数据进行悲观锁，比如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有效，目前，该参数正在提议准备修改单位为秒。

// 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

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

* @param

* @param

*/

public class ScioJpaRepositoryFactoryBean, S, ID extends Serializable>

extends JpaRepositoryFactoryBean {

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 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 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 getHints(Method m) {

List result = new ArrayList();

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 {

@Override

public void onCreation(AbstractJpaQuery query) {

List list = getHints(query.getQueryMethod());

if (CollectionUtils.isNotEmpty(list)) {

list.stream().forEach(System.out::println);

}

}

protected List getHints(JpaQueryMethod query) {

List result = new ArrayList();

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)