title: pessimistic lock not work using spring data jpa
body: <p>I want to get serial numbers with the help of database.
  Here is what I want:</p>
  
  <p>1.read the entity from database and lock it</p>
  
  <p>2.increase the serial number and upadte the entity.</p>
  
  <p>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. </p>
  
  <p>below is my code:</p>
  
  <p>Repository:</p>
  
  <pre><code>public interface ActivityNoGeneratorRepository extends BaseRepository&lt;ActivityNoGenerator, Long&gt; {
      @Lock(LockModeType.PESSIMISTIC_WRITE)
      @Query(value = "select  generator from ActivityNoGenerator generator where id=:id")
      ActivityNoGenerator getGeneratorByIdForUpdate(@Param("id") Long id);
  }
  </code></pre>
  
  <p>Service:</p>
  
  <pre><code>@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;
      }
  }
  </code></pre>
  
  <p>Test:</p>
  
  <pre><code>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 &lt; 2; i++) {
              new Thread(new Runnable() {
                  @Override
                  public void run() {
                      System.out.println(activityNoGeneratorService.getActivityNo());
                  }
              }).start();
          }
      }
  }
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I debug my code and find the second thread executed without waiting the first thread commit.</p>
  
  <p>Any clue what mistake I make? Thanks very much!</p>