pessimistic lock not work using spring data jpa

Question

I want to get serial numbers with the help of database. Here is what I want:

1.read the entity from database and lock it

2.increase the serial number and upadte the entity.

I am asuming the first thread can lock the record and the other thread won't work until the first thread commits its transaction, however, I got the opposite of what I want.

below is my code:

Repository:

public interface ActivityNoGeneratorRepository extends BaseRepository<ActivityNoGenerator, Long> {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query(value = "select  generator from ActivityNoGenerator generator where id=:id")
    ActivityNoGenerator getGeneratorByIdForUpdate(@Param("id") Long id);
}

Service:

@Service
public class ActivityNoGeneratorServiceImpl implements IActivityNoGeneratorService {
    @Autowired
    private ActivityNoGeneratorRepository activityNoGeneratorRepository;

    @Override
    @Transactional
    public String getActivityNo() {
        ActivityNoGenerator activityNoGenerator = activityNoGeneratorRepository.getGeneratorByIdForUpdate(1L);
        System.out.println(1);
        Integer currentValue = activityNoGenerator.getCurrentValue() + 1;
        if (!StringUtils.equals(DateFormatUtils.format(new Date(), "yyyyMM"), DateFormatUtils.format(activityNoGenerator.getLastAccessTime(), "yyyyMM"))) {
            currentValue = 1;
        }
        String serialNum = String.format("%0" + activityNoGenerator.getWidth() + "d", currentValue);
        String activityNo = activityNoGenerator.getPrefix() + activityNoGenerator.getPlatformCode() + DateFormatUtils.format(new Date(), "yyyyMM") + serialNum;

        activityNoGenerator.setCurrentValue(currentValue);
        activityNoGenerator.setLastAccessTime(new Date());
        activityNoGeneratorRepository.save(activityNoGenerator);

        return activityNo;
    }
}

Test:

public class IActivityNoGeneratorServiceTest {

    public static void main(String[] args) {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("classpath*:spring/applicationContext.xml");
        final IActivityNoGeneratorService activityNoGeneratorService = applicationContext.getBean(IActivityNoGeneratorService.class);

        for (int i = 0; i < 2; i++) {
            new Thread(new Runnable() {
                @Override
                public void run() {
                    System.out.println(activityNoGeneratorService.getActivityNo());
                }
            }).start();
        }
    }
}

Result:

Hibernate: 
    select
        activityno0_.id as id1_3_,
        activityno0_.current_value as current_2_3_,
        activityno0_.last_access_time as last_acc3_3_,
        activityno0_.platform_code as platform4_3_,
        activityno0_.platform_name as platform5_3_,
        activityno0_.prefix as prefix6_3_,
        activityno0_.step as step7_3_,
        activityno0_.width as width8_3_ 
    from
        activity_no_generator activityno0_ 
    where
        activityno0_.id=? for update

Hibernate: 
    select
        activityno0_.id as id1_3_,
        activityno0_.current_value as current_2_3_,
        activityno0_.last_access_time as last_acc3_3_,
        activityno0_.platform_code as platform4_3_,
        activityno0_.platform_name as platform5_3_,
        activityno0_.prefix as prefix6_3_,
        activityno0_.step as step7_3_,
        activityno0_.width as width8_3_ 
    from
        activity_no_generator activityno0_ 
    where
        activityno0_.id=? for update

1
1
Hibernate: 
    update
        activity_no_generator 
    set
        current_value=?,
        last_access_time=?,
        platform_code=?,
        platform_name=?,
        prefix=?,
        step=?,
        width=? 
    where
        id=?
Hibernate: 
    update
        activity_no_generator 
    set
        current_value=?,
        last_access_time=?,
        platform_code=?,
        platform_name=?,
        prefix=?,
        step=?,
        width=? 
    where
        id=?
Exception in thread "Thread-6" org.springframework.orm.jpa.JpaSystemException: commit failed; nested exception is org.hibernate.TransactionException: commit failed
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:333)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:521)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:483)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:290)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    at com.sun.proxy.$Proxy44.getActivityNo(Unknown Source)
    at com.lemall.srd.pop.activity.oa.service.IActivityNoGeneratorServiceTest$1.run(IActivityNoGeneratorServiceTest.java:18)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.TransactionException: commit failed
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:187)
    at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:77)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517)
    ... 10 more
Caused by: org.hibernate.TransactionException: unable to commit against JDBC connection
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doCommit(JdbcTransaction.java:116)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:180)
    ... 12 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:950)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
    at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1614)
    at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:355)
    at com.zaxxer.hikari.pool.HikariProxyConnection.commit(HikariProxyConnection.java)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doCommit(JdbcTransaction.java:112)
    ... 13 more
HD1020012017050631

Process finished with exit code 0

I debug my code and find the second thread executed without waiting the first thread commit.

Any clue what mistake I make? Thanks very much!

Answer 1

What I see is that you are missing the @Transactional annotation in your repository method which might be the reason that your service method and repo method are running in two different transactions. Also use the Propagation.Required option in your transactional annotation.