Java 获得警告：SQL 错误：1205，SQLState：41000 错误：超出锁定等待超时；尝试重新启动事务。使用休眠保存记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27564411/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting WARN: SQL Error: 1205, SQLState: 41000 ERROR: Lock wait timeout exceeded; try restarting transaction. Saving a record in using hibernate
提问by Kedar Vaze
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.
我是 Java Web 应用程序开发的新手，并尝试在 mySQL 数据库中使用 hibernate 保存记录，但在尝试保存记录时出错。
POJO Class
POJO类
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
sirs.hbm.xml 文件
<?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
休眠文件.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
addirs.jsp 代码
<%@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
web.xml 文件
<?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
Glassfish 服务器窗口
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!!
请帮忙！！
采纳答案by Andy Dufresne
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 processliston the mysql command line. These will normally show you the full sql for the query that is locking.If this doesn't help, verify that the mysql services were restarted after updating the timeout.
Use hibernate connection pool with max pool size as 10. Here is an exampleon how to do it.
