Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 mysql事务里锁死
发布于 3180天前 作者 milixiang 2070 次浏览 复制 上一个帖子 下一个帖子
标签: 无

感觉没做啥特别的事情啊 就根据非主键字段删除几条记录 然后插入几条记录 很容易就出现这个 Lock wait timeout exceeded; try restarting transaction
可能是因为什么原因呢？

 For example:> "DELETE FROM lend_depart_viewer WHERE depart_id=195"
22:03:11 [DEBUG] com.mchange.v2.c3p0.impl.NewPooledConnection {NewPooledConnection.java:430} - com.mchange.v2.c3p0.impl.NewPooledConnection@a473fd0 handling a throwable.
java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
	at com.mysql.jdbc.Connection.execSQL(Connection.java:3255)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1293)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:867)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute(NewProxyPreparedStatement.java:989)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runPreparedStatement(NutDaoExecutor.java:266)
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:81)
	at org.nutz.dao.impl.DaoSupport$DaoExec.invoke(DaoSupport.java:264)
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:31)
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:219)
	at org.nutz.dao.impl.NutDao.clear(NutDao.java:606)
	at com.twl.app.lend.ctrl.LendCtrlImpl$8.run(LendCtrlImpl.java:848)
	at org.nutz.trans.Trans.exec(Trans.java:174)
	at org.nutz.trans.Trans.exec(Trans.java:132)
	at org.nutz.trans.Trans.exec(Trans.java:195)
	at com.twl.app.lend.ctrl.LendCtrlImpl.updateLendViewDepart(LendCtrlImpl.java:840)
	at com.twl.app.lend.ctrl.LendCtrlImpl$$FastClassByCGLIB$$cb33b7fd.invoke(<generated>)
	at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
	at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:700)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)
	at com.twl.app.lend.ctrl.LendCtrlImpl$$EnhancerByCGLIB$$b4fe529c.updateLendViewDepart(<generated>)
	at com.twl.app.lend.ajax.LendAjax.updateLendViewDepart(LendAjax.java:149)
	at com.twl.app.lend.ajax.LendAjax$$FastClassByCGLIB$$abe323dd.invoke(<generated>)
	at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
	at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:700)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)
	at com.twl.app.lend.ajax.LendAjax$$EnhancerByCGLIB$$14b281a3.updateLendViewDepart(<generated>)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.directwebremoting.impl.ExecuteAjaxFilter.doFilter(ExecuteAjaxFilter.java:34)
	at org.directwebremoting.impl.DefaultRemoter$1.doFilter(DefaultRemoter.java:428)
	at org.directwebremoting.impl.DefaultRemoter.execute(DefaultRemoter.java:431)
	at org.directwebremoting.impl.DefaultRemoter.execute(DefaultRemoter.java:283)
	at org.directwebremoting.servlet.PlainCallHandler.handle(PlainCallHandler.java:52)
	at org.directwebremoting.servlet.UrlProcessor.handle(UrlProcessor.java:101)
	at org.directwebremoting.servlet.DwrServlet.doPost(DwrServlet.java:146)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:96)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1002)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:744)
4 回复 
return Trans.exec(new Molecule<Integer>() {
			public void run() {
				
				String[] idArr = ids.split(",");
				
				List<MLendDepartViewer> departViewers = new ArrayList<MLendDepartViewer>();
				
				if(type == Constant.INFORMATION_RECEIVER_TYPE_DEPART){
					
					dao.clear(MLendDepartViewer.class, Cnd.where("departId", "=", id));

					for (String string : idArr) {
						if(StringUtils.isNotEmpty(string)){
							MLendDepartViewer departViewer = new MLendDepartViewer();
							departViewer.setAddTime(new Date());
							departViewer.setDepartId(id);
							departViewer.setUserId(Integer.parseInt(string));
							departViewers.add(departViewer);
						}
					}
				}else if(type == Constant.INFORMATION_RECEIVER_TYPE_USER){
					
					dao.clear(MLendDepartViewer.class,Cnd.where("userId","=",id));

					for (String string : idArr) {
						if(StringUtils.isNotEmpty(string)){
							MLendDepartViewer departViewer = new MLendDepartViewer();
							departViewer.setAddTime(new Date());
							departViewer.setDepartId(Integer.parseInt(string));
							departViewer.setUserId(id);
							departViewers.add(departViewer);
						}
					}
				}
				dao.insert(departViewers);
				setObj(1);
			}
		});

@Table("lend_depart_viewer")
public class MLendDepartViewer {
	@Id
	private Integer id;
	@Column("depart_id")
	private Integer departId;
	@Column("user_id")
	private Integer userId;
	@Column("add_time")
	private Date addTime;

两个clear操作分别锁了一些记录，然后插入时就挂了

修改成在事务外准备好数据，事务内仅一条clear和一条fastInsert

@wendal Ok 谢了

添加回复
请先登陆
回到顶部