MyBatis hangs when inserting

Question

I am trying to insert a record into Oracle 11g database using MyBatis-Spring, but the insert hangs. Select works fine.

I need another set of eyes to help me figure out what is going on.

Here are the snippets of codes that matter:

Logging output: (it hangs forever on the last line)

Running persistence.PartyMapperUnitTest
DEBUG [main] - Cache Hit Ratio [persistence.mapper.PartyMapper]: 0.0
DEBUG [main] - ==>  Preparing: SELECT PARTY_ID, PARTY_SUBTYPE_CD, LIFECYCLE_CD, PARTY_STATUS_CD FROM PARTY WHERE PARTY_ID =2 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: INSERT INTO PARTY (PARTY_SUBTYPE_CD, LIFECYCLE_CD, PARTY_STATUS_CD, CREATED_BY) VALUES (?,?,?,?) 
DEBUG [main] - ==> Parameters: partySubtypeCode1438810529048(String), lifecycleCode(String), partyStatusCode(String), createdBy(String)
***==== The application hangs forever at this log line ====***

Testing.sql (this works fine)

INSERT INTO PARTY (PARTY_SUBTYPE_CD, LIFECYCLE_CD, PARTY_STATUS_CD, CREATED_BY)
VALUES ( 'a', 'b', 'c', 'd');

applicationContext.xml

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
</bean>

PartyMapper.java

public interface PartyMapper<PartyEntity> {
    public PartyEntity fetch(Object entityId);
    public int insert(PartyEntity entity);
}

PartyMapper.xml

<insert
    id="insert"
    parameterType="persistence.entity.PartyEntity"
    keyProperty="partyId"
    keyColumn="PARTY_ID"
    useGeneratedKeys="true">
    INSERT INTO PARTY
        (PARTY_SUBTYPE_CD, LIFECYCLE_CD, PARTY_STATUS_CD, CREATED_BY)
    VALUES
        (#{partySubtypeCode},#{lifecycleCode},#{partyStatusCode},#{createdBy})
</insert>

PartyMapperUnitTest.java

PartyEntity expectedParty = new PartyEntity();
expectedParty.setPartySubtypeCode("a");
expectedParty.setLifecycleCode("b");
expectedParty.setPartyStatusCode("c");
expectedParty.setCreatedBy("d");
partyMapper.insert(expectedParty);

=== EDIT === There are only two threads running when the UnitTest runs. I don't see anything wrong in here.

Added a Thread.dumpStack() before the insert, but did not see anything wrong with it:

Thread.dumpStack()

INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@77ab3f0: defining beans [transactionManager,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,sqlSessionFactory,dataSource,org.mybatis.spring.mapper.MapperScannerConfigurer#0,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,mapper,partyMapper,org.springframework.context.annotation.ConfigurationClassPostProcessor$ImportAwareBeanPostProcessor#0]; root of factory hierarchy
java.lang.Exception: Stack trace
    at java.lang.Thread.dumpStack(Thread.java:1365)
    at td.com.naccms.cps.persistence.PartyMapperUnitTest.insert(PartyMapperUnitTest.java:48)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
...
...

Answer 1

How many connections do you have in your connection pool? I've seen this happen often when the DB pool has 1 connection and there are 2 concurrent transactions (so MyBatis cannot get a DB connection, and the jdbc connection pool is the one actually hanging). You can debug your app and 'pause' it when it hangs. You should be able to see the threads and trace which one id blocked and where. Another option, but more rare, is that the table is locked. You can google for some queries that will show you all the current locks in your DB.

edit after your comment

To get a proper error when this happens, my suggestion is to set the defaultStatementTimeout in mybatis. This, at least, will throw an exception, rather than hang forever.

You also might want to configure some timeouts in your database connection pool too, as some pools wait forever by default (and that's a loooong time :).