Simple spring data jpa transaction to small table on mysql database is hanging indefinitely

Question

I have a spring data jpa query that reads a row from a mysql table with a pessimistic lock. In my environment, this table only contains one row so it should be pretty fast.

The spring data jpa function is named FindTopByIsCurrent(boolean iscurrent) which should just retrieve the only element of the table.

When the command runs, it hangs indefinitely. Checking the output of SHOW ENGINE INNODB STATUS

---TRANSACTION 170279307,

ACTIVE 3859 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 11665, OS thread handle 140237095196416, query id 770252923 10.51.5.32 root
TABLE LOCK table `mydb`.`mytable` trx id 170279307 lock mode IS
RECORD LOCKS space id 722 page no 3 n bits 72 index PRIMARY of table `mydb`.`mytable` trx id 170279307 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 34303030; asc 4000;;
 1: len 4; hex 80000000; asc     ;;
 2: len 6; hex 0000000bf801; asc       ;;
 3: len 7; hex b2000001260110; asc     &  ;;
 4: len 1; hex 01; asc  ;;

This table is only for use by this one application and there are no other transactions in the list from show engine. The performance schema thread table doesn’t have a thread with id 11665. Additionally, when i log onto the db and run

select * from mytable

I immediately receive any information I need, so the table isn’t being locked in a way that prevents reads.

Here is the table (is_current is a true bit)

*************************** 
1. row 
***************************

version: 4000
local_index: 0

 is_current: 

1 row in set (0.00 sec)

Additionally, this is a random issue. In about 3/4 of cases this error does not appear. In 1/4 this appears in every transaction the service attempts. Further debugging has shown that the hang occurs in the middle of the query, the block occurs before we have the opportunity to commit

Answer 1

So apparently it is a deadlock, but it has nothing to do with SQL.

I was running another transaction in another thread. The transaction involved reading from a different table in a different database (same server). Additionally, both transactions I was running occurred in a PostConstruct method of their spring beans. I kind of tunnel visioned on SQL related causes and ignored the possibility that the unrelated read could cause an issue

These transactions apparently need to lock a specific SingletonSupplier and ConcurrentHashmap, and they each locked one of the two and deadlocked against each other.

at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getSingletonFactoryBeanForTypeCheck(AbstractAutowireCapableBeanFactory.java:1006)
- waiting to lock <0x00000000c189bf40> (a java.util.concurrent.ConcurrentHashMap)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.getTypeForFactoryBean(AbstractAutowireCapableBeanFactory.java:907)
at org.springframework.beans.factory.support.AbstractBeanFactory.isTypeMatch(AbstractBeanFactory.java:637)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doGetBeanNamesForType(DefaultListableBeanFactory.java:583)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.getBeanNamesForType(DefaultListableBeanFactory.java:550)
at org.springframework.beans.factory.BeanFactoryUtils.beanNamesForTypeIncludingAncestors(BeanFactoryUtils.java:265)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.findAutowireCandidates(DefaultListableBeanFactory.java:1555)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1352)
at org.springframework.beans.factory.support.DefaultListableBeanFactory$DependencyObjectProvider.getObject(DefaultListableBeanFactory.java:1988)
at org.springframework.boot.actuate.autoconfigure.metrics.data.RepositoryMetricsAutoConfiguration$$Lambda$891/1598942787.get(Unknown Source)
at org.springframework.util.function.SingletonSupplier.get(SingletonSupplier.java:97)
- locked <0x00000000c2ca1c40> (a org.springframework.util.function.SingletonSupplier)
at org.springframework.boot.actuate.metrics.data.MetricsRepositoryMethodInvocationListener.lambda$afterInvocation$0(MetricsRepositoryMethodInvocationListener.java:87)
at org.springframework.boot.actuate.metrics.data.MetricsRepositoryMethodInvocationListener$$Lambda$1121/652710755.accept(Unknown Source)
at org.springframework.boot.actuate.metrics.AutoTimer.apply(AutoTimer.java:109)
at org.springframework.boot.actuate.metrics.data.MetricsRepositoryMethodInvocationListener.afterInvocation(MetricsRepositoryMethodInvocationListener.java:86)
at org.springframework.data.repository.core.support.RepositoryInvocationMulticaster$DefaultRepositoryInvocationMulticaster.notifyListeners(RepositoryInvocationMulticaster.java:76)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:148)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)