Exception in thread "main" org.hibernate.exception.LockAcquisitionException: could not execute statement

Question

I am trying to update a record in table using Hibernate, but the record is not updating and on next execution it takes much time and throws the following error: Exception in thread "main" org.hibernate.exception.LockAcquisitionException: could not execute statement

Employee:

    import javax.persistence.CascadeType;
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.OneToOne;
    import javax.persistence.Table;

    import org.hibernate.annotations.*;

    @Entity
    @Table(name = "Emp_Table")
    public class Employee {
        @Id
        @GeneratedValue
        private int id;
        private String name;
        private double salary;

        @OneToOne(mappedBy = "emp")
        @Cascade(value = org.hibernate.annotations.CascadeType.ALL)
        private Address addr;

        /* Getter-Setter methods */
    }

Address:

    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.OneToOne;
    import javax.persistence.PrimaryKeyJoinColumn;
    import javax.persistence.Table;

    import org.hibernate.annotations.GenericGenerator;
    import org.hibernate.annotations.Parameter;

    @Entity
    @Table(name = "Addr_Table")
    public class Address {

        @Id
        @Column(name = "eid")
        @GeneratedValue(generator = "gen")
        @GenericGenerator(name = "gen", strategy = "foreign", parameters = {@Parameter(name = "property", value = "emp")})
        private int id;
        private String state;
        private String country;

        @OneToOne
        @PrimaryKeyJoinColumn
        private Employee emp;

        /* Getter-Setter methods */
    }

HQLExample contains the CRUD operation.

package com.Hibernate.Main;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import com.Hibernate.Model.Employee;
import com.Hibernate.Util.HibernateUtil;

public class HQLExample {

    @SuppressWarnings("unchecked")
    public static void main(String[] args) {

        SessionFactory factory = new Configuration().configure().buildSessionFactory();
        Session session = factory.openSession();

        Transaction tx = session.beginTransaction();
        Query query = session.createQuery("from Employee");
        List<Employee> emp = query.list();
        for (Employee empList : emp) {
            System.out.println("\nID:\t" + empList.getId() + "\nName:\t" + empList.getName() + "\nSalary:\t"
                    + empList.getSalary());
        }

        Query query1 = session.createQuery("from Employee where id = :id");
        query1.setInteger("id", 2);
        Employee empList = (Employee) query1.uniqueResult();
        System.out.println(
                "\nID:\t" + empList.getId() + "\nName:\t" + empList.getName() + "\nSalary:\t" + empList.getSalary());


        Query query2 = session.createQuery("from Employee");
        query2.setFirstResult(1);
        query2.setFetchSize(1);
        emp = query2.list();
        for (Employee empList1 : emp) {
            System.out.println("\nID:\t" + empList1.getId() + "\nName:\t" + empList1.getName() + "\nSalary:\t"
                    + empList1.getSalary());
        }

        query = session.createQuery("update Employee set name = :name where id = :id");
        query.setParameter("name", "Gaurav");
        query.setInteger("id", 2);
        int result = query.executeUpdate();
        System.out.println("\n\nEmployee updated successfully: "+result);



        query = session.createQuery("delete from Employee where id: eid");
        query.setInteger("eid", 4);
        result = query.executeUpdate();
        System.out.println("Employee deleted successfully "+ result);*/
    }
}

I am able to retrieve the records but during updating the record it either does not update the record or takes a long time and throws error: Exception in thread "main" org.hibernate.exception.LockAcquisitionException: could not execute statement.

Using the command show processlist and I had tried to kill the process there where in sleep mode. After killing those process I am able to execute the update statement but it is not reflected into the database. When tried to execute the code again will go to long run and fails.

hibernate.cfg.xml:

<?xml version="1.0" encoding="utf-8"?>
 <!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">root.123</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/Hibernate</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.current_session_context_class">thread</property>
        <property name="hibernate.hbm2ddl.auto">update</property>
        <property name="hibernate.show_sql">true</property>
        <mapping class="com.Hibernate.Model.Address"></mapping>
        <mapping class="com.Hibernate.Model.Employee"></mapping>
    </session-factory>
</hibernate-configuration>

Answer 1

You have to commit the transaction to save the modification to database. Also you have to close the session and session factory. If you are not closing the resources means the code will hold the lock on the record in database.

Also I observed syntax error in your delete query. Here are the changes you can do to make the code work.

query = session.createQuery("delete from Employee where id = :eid");
query.setInteger("eid", 4);
result = query.executeUpdate();
System.out.println("Employee deleted successfully " + result);

tx.commit(); // commit the transaction
session.close(); // close the session
factory.close(); // close the factory