起因
在平时的的项目中，我们可能会用到对与数据进行悲观锁，比如
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)
网友评论