Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting WARN: SQL Error: 1205, SQLState: 41000 ERROR: Lock wait timeout exceeded; try restarting transaction. Saving a record in using hibernate

I'm new into java web application development and trying to save a record using hibernate in mySQL database but getting an error when trying to save the record.

POJO Class

package defaultpackage;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;


public class Sirs  implements java.io.Serializable {


 private int sirsid;
 private String sirsDescription;
 private String submitter;

public Sirs() {
}


public Sirs(int sirsid) {
    this.sirsid = sirsid;
}
public Sirs(int sirsid, String sirsDescription, String submitter) {
   this.sirsid = sirsid;
   this.sirsDescription = sirsDescription;
   this.submitter = submitter;
}
@GeneratedValue(strategy=GenerationType.TABLE)
public int getSirsid() {
    return this.sirsid;
}

public void setSirsid(int sirsid) {
    this.sirsid = sirsid;
}
public String getSirsDescription() {
    return this.sirsDescription;
}

public void setSirsDescription(String sirsDescription) {
    this.sirsDescription = sirsDescription;
}
public String getSubmitter() {
    return this.submitter;
}

public void setSubmitter(String submitter) {
    this.submitter = submitter;
}
}

sirs.hbm.xml file

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated 12 Dec, 2014 1:32:06 PM by Hibernate Tools 4.3.1 -->
<hibernate-mapping>
<class name="defaultpackage.Sirs" table="sirs" catalog="sirsdb" optimistic-lock="version">
    <id name="sirsid" type="int">
        <column name="SIRSID" />
        <generator class="assigned" />
    </id>
    <property name="sirsDescription" type="string">
        <column name="`SIRS Description`" length="45" />
    </property>
    <property name="submitter" type="string">
        <column name="Submitter" length="45" />
    </property>
</class>
</hibernate-mapping>

Hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">       
<hibernate-configuration>
<session-factory>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/sirsdb?
zeroDateTimeBehavior=convertToNull</property>             
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password">root</property>
<property name="show_sql">true</property>
  <property name="hibernate.hbm2ddl.auto">update</property>
<mapping resource="defaultpackage/Sirs.hbm.xml"/>
</session-factory>
</hibernate-configuration>

addsirs.jsp code

<%@page import="defaultpackage.Sirs"%>
<!-- A jsp to insert record through hibernate -->
<%@ page import="java.util.*,org.hibernate.*,org.hibernate.cfg.*,
org.hibernate.boot.registry.StandardServiceRegistryBuilder,
org.hibernate.service.ServiceRegistry, org.hibernate.SessionFactory" %>
<%!
int sirsid;String submitter;String sirsDescription; Session session1 = null;
%>
<body>
<%
String num1=request.getParameter("t1");
if(num1 != null)
{
out.println("<h1>Data</h1>");
sirsid=Integer.parseInt(num1);
sirsDescription=request.getParameter("t2");
submitter=request.getParameter("t3");
try
{
Configuration conf = new Configuration().configure("hibernate.cfg.xml");

ServiceRegistry sr = new
StandardServiceRegistryBuilder().applySettings(conf.getProperties()).build();

SessionFactory sf = conf.buildSessionFactory(sr);

Session session1 = sf.openSession();
Sirs e=new Sirs(sirsid,sirsDescription,submitter);
Transaction transaction = session1.beginTransaction();
session1.save(e);
//session1.flush();
transaction.commit();
session1.close();
out.println("<h1>Data Inserted Successfully</h1>");
}
catch(Exception e)
{
System.out.println("e="+e.getMessage());
}
}
%>

<form>
<table width="352" border="1">
<tr>
  <th>SIRS ID</th>
  <td><input name="t1" type="text"></td>
</tr>
<tr>
  <th> Description </th>
  <td><input name="t2" type="text"></td>
</tr>
<tr>
  <th> Submitter </th>
  <td><input name="t3" type="text"></td>
</tr>
<tr>
  <th colspan="2"><input type="submit"value="Submit" >
  </th>
</tr>
</table>
</form>
</body>
</html>

web.xml file

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.1" xmlns="http://xmlns.jcp.org/xml/ns/javaee" 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-   
app_3_1.xsd">
<context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>/WEB-INF/applicationContext.xml</param-value>
</context-param>
<listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<servlet>
    <servlet-name>dispatcher</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
    <servlet-name>dispatcher</servlet-name>
    <url-pattern>*.htm</url-pattern>
</servlet-mapping>
<session-config>
    <session-timeout>
        30
    </session-timeout>
</session-config>
<welcome-file-list>
    <welcome-file>redirect.jsp</welcome-file>
</welcome-file-list>
</web-app>

Glassfish server window

Info:   Hibernate: insert into sirsdb.sirs (`SIRS Description`, Submitter, SIRSID) values (?, ?,      
?)
WARN:   SQL Error: 1205, SQLState: 41000
ERROR:   Lock wait timeout exceeded; try restarting transaction
Info:   HHH000010: On release of batch it still contained JDBC statements
Info:   e=could not execute statement

Please help!!

like image 871
Kedar Vaze Avatar asked Dec 19 '14 10:12

Kedar Vaze


1 Answers

  1. Can you check that you don't have a previous instance of your app still running, or a database browsing tool opened and in the middle of a transaction? Open a terminal to browse & work on the same table could be the root cause. You can also execute show full processlist on the mysql command line. These will normally show you the full sql for the query that is locking.

  2. If this doesn't help, verify that the mysql services were restarted after updating the timeout.

  3. Use hibernate connection pool with max pool size as 10. Here is an example on how to do it.

like image 106
Andy Dufresne Avatar answered Oct 20 '22 23:10

Andy Dufresne