title: Hibernate:LockAcquisitionException when trying to update
body: <p>I have 2 tables, <code>Product</code> and <code>ProductCategory</code>. The <code>Product</code> table has the foreign key of <code>ProductCategory</code>.</p>
  
  <p>I am building a REST API and I am trying to delete the <code>Product</code>s by <code>ProductCategory</code>. I am using <code>Hibernate</code> and my database is <code>MySQL</code>.</p>
  
  <p>Please check the below code.</p>
  
  <p><strong>Product.java</strong></p>
  
  <pre><code>public class Product  implements java.io.Serializable {
  
  
       private Integer idproduct;
       private ProductCategory productCategory;
       private String productName;
       private Date deleteTimestamp;
       private Date dateCreated;
       private Date lastUpdated;
  
      public Product() {
      }
  
  
      public Product(ProductCategory productCategory, String productName, Date lastUpdated) {
          this.productCategory = productCategory;
          this.productName = productName;
          this.lastUpdated = lastUpdated;
      }
      public Product(ProductCategory productCategory, String productName, Date deleteTimestamp, Date dateCreated, Date lastUpdated) {
         this.productCategory = productCategory;
         this.productName = productName;
         this.deleteTimestamp = deleteTimestamp;
         this.dateCreated = dateCreated;
         this.lastUpdated = lastUpdated;
      }
  
      public Integer getIdproduct() {
          return this.idproduct;
      }
  
      public void setIdproduct(Integer idproduct) {
          this.idproduct = idproduct;
      }
      public ProductCategory getProductCategory() {
          return this.productCategory;
      }
  
      public void setProductCategory(ProductCategory productCategory) {
          this.productCategory = productCategory;
      }
      public String getProductName() {
          return this.productName;
      }
  
      public void setProductName(String productName) {
          this.productName = productName;
      }
      public Date getDeleteTimestamp() {
          return this.deleteTimestamp;
      }
  
      public void setDeleteTimestamp(Date deleteTimestamp) {
          this.deleteTimestamp = deleteTimestamp;
      }
      public Date getDateCreated() {
          return this.dateCreated;
      }
  
      public void setDateCreated(Date dateCreated) {
          this.dateCreated = dateCreated;
      }
      public Date getLastUpdated() {
          return this.lastUpdated;
      }
  
      public void setLastUpdated(Date lastUpdated) {
          this.lastUpdated = lastUpdated;
      }
  
  }
  </code></pre>
  
  <p><strong>ProductCategory.java</strong></p>
  
  <pre><code>public class ProductCategory  implements java.io.Serializable {
  
  
       private Integer idproductCategory;
       private String categoryName;
       private Date deleteTimestamp;
       private Date dateCreated;
       private Date lastUpdated;
  
      public ProductCategory() {
      }
  
  
      public ProductCategory(String categoryName, Date lastUpdated) {
          this.categoryName = categoryName;
          this.lastUpdated = lastUpdated;
      }
      public ProductCategory(String categoryName, Date deleteTimestamp, Date dateCreated, Date lastUpdated) {
         this.categoryName = categoryName;
         this.deleteTimestamp = deleteTimestamp;
         this.dateCreated = dateCreated;
         this.lastUpdated = lastUpdated;
      }
  
      public Integer getIdproductCategory() {
          return this.idproductCategory;
      }
  
      public void setIdproductCategory(Integer idproductCategory) {
          this.idproductCategory = idproductCategory;
      }
      public String getCategoryName() {
          return this.categoryName;
      }
  
      public void setCategoryName(String categoryName) {
          this.categoryName = categoryName;
      }
      public Date getDeleteTimestamp() {
          return this.deleteTimestamp;
      }
  
      public void setDeleteTimestamp(Date deleteTimestamp) {
          this.deleteTimestamp = deleteTimestamp;
      }
      public Date getDateCreated() {
          return this.dateCreated;
      }
  
      public void setDateCreated(Date dateCreated) {
          this.dateCreated = dateCreated;
      }
      public Date getLastUpdated() {
          return this.lastUpdated;
      }
  
      public void setLastUpdated(Date lastUpdated) {
          this.lastUpdated = lastUpdated;
      }
  
  }
  </code></pre>
  
  <p>Below code is the logic, it is divided into 3 sections. <code>REST</code> Layer, <code>Service</code> Layer and <code>DAO</code> Layer. The browser calls the <code>REST</code> layer, it will call the <code>Service</code> layer and that will call <code>DAO</code> layer.</p>
  
  <p><strong>Rest Layer Code</strong></p>
  
  <pre><code>@POST
      @Path("/softDeleteProductByCategory")
      @Consumes(MediaType.APPLICATION_JSON)
      public Response softDeleteProductByCategory(ProductCategory category)
      {
          ProductService service= new ProductService();
  
          String result = service.softDeleteProductByCategory(category);
          return Response.status(Response.Status.OK).entity(result).build();
      }
  </code></pre>
  
  <p><strong>Service Layer Code</strong></p>
  
  <pre><code>public String softDeleteProductByCategory(ProductCategory category)
      {
          Session session = getSession();
          Transaction transaction = null;
          String result = "";
  
          try
          {
              transaction = getTransaction(session);
  
              pInterface.softDeleteProductByCategory(category.getIdproductCategory(), Common.getSQLCurrentTimeStamp(), session);
              transaction.commit();
  
              result = Common.SAVE_SUCCESS;
  
          }
          catch(Exception e)
          {
              e.printStackTrace();
  
              if(transaction!=null)
              {
                  transaction.rollback();
                  result = Common.SAVE_ROLLBACK;
              }
              else
              {
                  result = Common.SAVE_ROLLBACK_FAILED;
              }
          }
          finally
          {
              session.close();
          }
  
          return result;
      }
  </code></pre>
  
  <p><strong>DAO Layer Code</strong></p>
  
  <pre><code> @Override
      public void softDeleteProductByCategory(int categoryID, Timestamp deleteTime, Session session)
      {
          Query query = session.createQuery("UPDATE Product SET deleteTimestamp = :deleteTimestamp");
          query.setParameter("deleteTimestamp", deleteTime);
          query.executeUpdate();
      }
  </code></pre>
  
  <p>If you are interested in looking at the database diagram, below it is.</p>
  
  <p><a href="https://i.stack.imgur.com/CKN4U.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CKN4U.png" alt="enter image description here"></a></p>
  
  <p>The problem is, when I executed the above code (when I called the <code>/softDeleteProductByCategory</code>) I get the below error.</p>
  
  <pre><code>19-Feb-2019 15:28:35.223 WARN [http-nio-8080-exec-300] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions SQL Error: 1205, SQLState: 40001
  19-Feb-2019 15:28:35.224 ERROR [http-nio-8080-exec-300] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions Lock wait timeout exceeded; try restarting transaction
  org.hibernate.exception.LockAcquisitionException: could not execute statement
      at org.hibernate.dialect.MySQLDialect$1.convert(MySQLDialect.java:451)
      at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
      at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:190)
      at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:109)
      at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:78)
      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:445)
      at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:347)
      at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1282)
      at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:118)
      at xx.xxx.api.dao.product.ProductDAOImpl.softDeleteProductByCategory(ProductDAOImpl.java:116)
      at xx.xxx.api.service.ProductService.softDeleteProductByCategory(ProductService.java:366)
      at xx.xxx.api.rest.ProductJSONService.softDeleteProductByCategory(ProductJSONService.java:94)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
      at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.base/java.lang.reflect.Method.invoke(Method.java:566)
      at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory.lambda$static$0(ResourceMethodInvocationHandlerFactory.java:76)
      at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:148)
      at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:191)
      at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:200)
      at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:103)
      at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:493)
      at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:415)
      at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:104)
      at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:277)
      at org.glassfish.jersey.internal.Errors$1.call(Errors.java:272)
      at org.glassfish.jersey.internal.Errors$1.call(Errors.java:268)
      at org.glassfish.jersey.internal.Errors.process(Errors.java:316)
      at org.glassfish.jersey.internal.Errors.process(Errors.java:298)
      at org.glassfish.jersey.internal.Errors.process(Errors.java:268)
      at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:289)
      at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:256)
      at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:703)
      at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:416)
      at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:370)
      at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:389)
      at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:342)
      at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:229)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
      at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
      at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:668)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
      at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
      at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
      at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:834)
      at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1417)
      at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
      at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
      at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
      at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
      at java.base/java.lang.Thread.run(Thread.java:834)
  Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
      at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
      at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
      at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
      at com.mysql.jdbc.Util.getInstance(Util.java:408)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
      at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2124)
      at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2058)
      at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5158)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2043)
      at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
      ... 57 more
  </code></pre>
  
  <p>How can I solve this issue?</p>
  
  <p>At the sametime, I am pretty sure I used the <code>where</code> clause here so the statement is like <code>UPDATE Product SET deleteTimestamp = :deleteTimestamp where productid= :id</code> ?</p>