执行KSQL.EXECUTE报错Lock wait timeout exceeded; try restarting transaction

xuni2

执行KSQL.EXECUTE，页面卡了好久，几十秒后，后台报错Lock wait timeout exceeded; try restarting transaction，将SQL拿到后台执行是正常的

<actions><action content-type="application/json" accept="application/json, text/javascript, */*"><![CDATA[{"process":"/CLOTHING_DRP_GUOYI/PurchaseManage/process/PurchaseInStorage/purchaseInStorageProcess","activity":"mainActivity","actionFlag":"__action_0__","executor":"CA0E26923CFC4892BF6EF264E29329A7","executeContext":"","action":"saveCL_PurchaseInStorageHdAction","parameters":{"table":{"@type":"table","userdata":{"relationAlias":"version,yID,yDocCode,yDocDate,yDocStatus,yDocType,yRefCode,fPurcContCode,fCompanyID,fCompanyCode,fCompanyName,fSupplierID,fSupplierCode,fSupplierName,fStID,fStCode,fStName,yDocSource,fTotalBaseMoney,fTotalBaseDigit,yMemo,yEnterName,yEnterDate,yModifyName,yModifyDate,yPostName,yPostDate,yDocStatus2","relationTypes":"Integer,String,String,Date,Integer,String,String,String,String,String,String,String,String,String,String,String,String,String,Decimal,Integer,Text,String,DateTime,String,DateTime,String,DateTime,String","idColumnType":"String","sys.rowid":"CL_PurchaseInStorageHd","idColumnDefine":"CL_PurchaseInStorageHd","idColumnName":"CL_PurchaseInStorageHd","sys.count":1,"relations":"CL_PurchaseInStorageHd.version,CL_PurchaseInStorageHd.yID,CL_PurchaseInStorageHd.yDocCode,CL_PurchaseInStorageHd.yDocDate,CL_PurchaseInStorageHd.yDocStatus,CL_PurchaseInStorageHd.yDocType,CL_PurchaseInStorageHd.yRefCode,CL_PurchaseInStorageHd.fPurcContCode,CL_PurchaseInStorageHd.fCompanyID,CL_PurchaseInStorageHd.fCompanyCode,CL_PurchaseInStorageHd.fCompanyName,CL_PurchaseInStorageHd.fSupplierID,CL_PurchaseInStorageHd.fSupplierCode,CL_PurchaseInStorageHd.fSupplierName,CL_PurchaseInStorageHd.fStID,CL_PurchaseInStorageHd.fStCode,CL_PurchaseInStorageHd.fStName,CL_PurchaseInStorageHd.yDocSource,CL_PurchaseInStorageHd.fTotalBaseMoney,CL_PurchaseInStorageHd.fTotalBaseDigit,CL_PurchaseInStorageHd.yMemo,CL_PurchaseInStorageHd.yEnterName,CL_PurchaseInStorageHd.yEnterDate,CL_PurchaseInStorageHd.yModifyName,CL_PurchaseInStorageHd.yModifyDate,CL_PurchaseInStorageHd.yPostName,CL_PurchaseInStorageHd.yPostDate,EXPRESS","model":"/CLOTHING_DRP_GUOYI/PurchaseManage/data","updateMode":"whereVersion","concept":"CL_PurchaseInStorageHd","conceptAlias":"CL_PurchaseInStorageHd"},"rows":[{"userdata":{"id":{"value":"6417DB3E9B554EF29F0EED65202736FF","originalValue":"6417DB3E9B554EF29F0EED65202736FF","changed":"0"},"recordState":"edit"},"version":{"value":0,"originalValue":0,"changed":"0"},"yID":{"value":"6417DB3E9B554EF29F0EED65202736FF","originalValue":"6417DB3E9B554EF29F0EED65202736FF","changed":"1"},"yDocCode":{"value":"CGRK1405030001","originalValue":"CGRK1405030001","changed":"1"},"yDocDate":{"value":"2014-05-03","originalValue":"2014-05-03","changed":"1"},"yDocStatus":{"value":-1,"originalValue":0,"changed":"1"},"yDocType":{"value":"","originalValue":"","changed":"1"},"yRefCode":{"value":"CGHTGUOYI1404300001","originalValue":"CGHTGUOYI1404300001","changed":"1"},"fPurcContCode":{"value":"","originalValue":"","changed":"1"},"fCompanyID":{"value":"078137ADF4CD431C80C6F4E60314C418","originalValue":"078137ADF4CD431C80C6F4E60314C418","changed":"1"},"fCompanyCode":{"value":"GUOYI","originalValue":"GUOYI","changed":"1"},"fCompanyName":{"value":"国意服饰","originalValue":"国意服饰","changed":"1"},"fSupplierID":{"value":"B96812E33561420CAE8D5F23A8A3E12F","originalValue":"B96812E33561420CAE8D5F23A8A3E12F","changed":"1"},"fSupplierCode":{"value":"GYS001","originalValue":"GYS001","changed":"1"},"fSupplierName":{"value":"供应商001","originalValue":"供应商001","changed":"1"},"fStID":{"value":"870B090DBD4747CBB5D76EEE5259AF9F","originalValue":"870B090DBD4747CBB5D76EEE5259AF9F","changed":"1"},"fStCode":{"value":"GUOYI001","originalValue":"GUOYI001","changed":"1"},"fStName":{"value":"总仓","originalValue":"总仓","changed":"1"},"yDocSource":{"value":"","originalValue":"","changed":"1"},"fTotalBaseMoney":{"value":"0.00","originalValue":"0.00","changed":"1"},"fTotalBaseDigit":{"value":0,"originalValue":0,"changed":"1"},"yMemo":{"value":"","originalValue":"","changed":"1"},"yEnterName":{"value":"国意服饰000","originalValue":"国意服饰000","changed":"1"},"yEnterDate":{"value":"2014-05-03T15:46:02.000Z","originalValue":"2014-05-03T15:46:02.000Z","changed":"1"},"yModifyName":{"value":"国意服饰000","originalValue":"国意服饰000","changed":"1"},"yModifyDate":{"value":"2014-05-03T15:46:14.000Z","originalValue":"2014-05-03T15:46:14.000Z","changed":"1"},"yPostName":{"value":"","originalValue":"","changed":"1"},"yPostDate":{"value":"","originalValue":"","changed":"1"},"yDocStatus2":{"value":"未确认","originalValue":"未确认","changed":"1"}}]}},"translateParameter":null}]]></action></actions>
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:592)
        at com.justep.system.action.Engine.invokeActions(Native Method)
        at com.justep.system.action.Engine.invokeActions(Unknown Source)
        at com.justep.business.server.BusinessServer.doAction(Unknown Source)
        at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
        at com.justep.business.server.BusinessServer.excute(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: INSERT INTO BA_StorageDetailAcct a ( a.gProcessCode, a.gProcessName, a.gDirection, a.yDocCode, a.yDocDate, a.yRowID, a.yPeriodID,a.fCompanyID,a.fMatID,a.fStID,a.yBatchCode, a.fDigit)(SELECT '/CLOTHING_DRP_GUOYI/PurchaseManage/process/PurchaseInStorage/purchaseInStorageProcess','采购入库','+', x.yDocCode, x.yDocDate, x.yRowID, substring(DateToString(x.yDocDate),0,7),x.fCompanyID,x.fMatID,x.fStID,x.yBatchCode,x.fDigit FROM (SELECT CL_PurchaseInStorageHd.yDocCode,CL_PurchaseInStorageHd.yDocDate,CL_PurchaseInStorageHd.fCompanyID,CL_PurchaseInStorageHd.fStID,CL_PurchaseInStorageItem.yRowID,CL_PurchaseInStorageItem.fMatID,CL_PurchaseInStorageItem.yBatchCode,CL_PurchaseInStorageItem.fDigit FROM CL_PurchaseInStorageItem CL_PurchaseInStorageItem JOIN CL_PurchaseInStorageHd CL_PurchaseInStorageHd ON CL_PurchaseInStorageItem.yID = CL_PurchaseInStorageHd.yID WHERE CL_PurchaseInStorageItem.yID = '6417DB3E9B554EF29F0EED65202736FF') x )
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlInsert(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlExecuteUpdate(Unknown Source)
        at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
        at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
        at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
        at com.wujx.business.acct.BusiAcctUtils.exeDetailBusiAcct(BusiAcctUtils.java:241)
        at com.wujx.business.acct.BusiAcctUtils.exeBusiAcct(BusiAcctUtils.java:127)
        at com.wujx.business.acct.BusiAcctUtils.exeBusiAcctInterface(BusiAcctUtils.java:71)
        at PurchaseInStorageProcess.purchaseInStorageProcessAfterSaveCL_PurchaseInStorageHdAction(PurchaseInStorageProcess.java:42)
        ... 26 more
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150039; 提示: 执行sql: INSERT INTO BA_STORAGEDETAILACCT(BA_STORAGEDETAILACCT.GPROCESSCODE, BA_STORAGEDETAILACCT.GPROCESSNAME, BA_STORAGEDETAILACCT.GDIRECTION, BA_STORAGEDETAILACCT.YDOCCODE, BA_STORAGEDETAILACCT.YDOCDATE, BA_STORAGEDETAILACCT.YROWID, BA_STORAGEDETAILACCT.YPERIODID, BA_STORAGEDETAILACCT.FCOMPANYID, BA_STORAGEDETAILACCT.FMATID, BA_STORAGEDETAILACCT.FSTID, BA_STORAGEDETAILACCT.YBATCHCODE, BA_STORAGEDETAILACCT.FDIGIT)(SELECT  ?, ?, ?, x.yDocCode AS yDocCode, x.yDocDate AS yDocDate, x.yRowID AS yRowID, SUBSTRING(DATE_FORMAT(x.yDocDate, '%Y-%m-%d'), ?, ?), x.fCompanyID AS fCompanyID, x.fMatID AS fMatID, x.fStID AS fStID, x.yBatchCode AS yBatchCode, x.fDigit AS fDigit FROM (SELECT  CL_PurchaseInStorageHd.YDOCCODE AS CL_PurchaseInStorageHd_yDocCode, CL_PurchaseInStorageHd.YDOCDATE AS CL_PurchaseInStorageHd_yDocDate, CL_PurchaseInStorageHd.FCOMPANYID AS CL_PurchaseInStorageHd_fCompanyID, CL_PurchaseInStorageHd.FSTID AS CL_PurchaseInStorageHd_fStID, CL_PurchaseInStorageItem.YROWID AS CL_PurchaseInStorageItem_yRowID, CL_PurchaseInStorageItem.FMATID AS CL_PurchaseInStorageItem_fMatID, CL_PurchaseInStorageItem.YBATCHCODE AS CL_PurchaseInStorageItem_yBatchCode, CL_PurchaseInStorageItem.FDIGIT AS CL_PurchaseInStorageItem_fDigit FROM CL_PURCHASEINSTORAGEITEM CL_PurchaseInStorageItem JOIN CL_PURCHASEINSTORAGEHD CL_PurchaseInStorageHd ON CL_PurchaseInStorageItem.YID = CL_PurchaseInStorageHd.YID  WHERE CL_PurchaseInStorageItem.YID = ?) x), binds: [/CLOTHING_DRP_GUOYI/PurchaseManage/process/PurchaseInStorage/purchaseInStorageProcess, 采购入库, +, 0, 7, 6417DB3E9B554EF29F0EED65202736FF]出错
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.ksql.KSQLUtils.execSqlUpdate(Unknown Source)
        ... 36 more
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)

fpj

Lock wait timeout exceeded; try restarting transaction
是MYSQL 数据库 锁等待超时的问题；

1、锁等待超时。是当前事务在等待其它事务释放锁资源造成的。可以找出锁资源竞争的表和语句，优化你的SQL，创建索引等，如果还是不行，可以适当减少并发线程数。
2、你的事务在等待给某个表加锁时超时了，估计是表正被另的进程锁住一直没有释放。可以用 SHOW INNODB STATUS/G; 看一下锁的情况。

解决的办法
innodb_lock_wait_timeout 锁定等待时间改大
my.ini文件：
#innodb_lock_wait_timeout = 50修改为innodb_lock_wait_timeout = 500

yinlun

studio里的数据库连接能测试成功吗？

xuni2

yinlun 发表于 2014-5-3 20:27
studio里的数据库连接能测试成功吗？

呃。。。在studio怎么测试呢。。不是只有在标准动作里面才能测试？

xuni2

fpj 发表于 2014-5-3 16:36
Lock wait timeout exceeded; try restarting transaction
是MYSQL 数据库 锁等待超时的问题；

第一次调用BA_StorageDetailAcct表哦。如果只是(select,,,,)这部分，我单独测试了，不会锁掉

yinlun

xuni2 发表于 2014-5-3 20:53
呃。。。在studio怎么测试呢。。不是只有在标准动作里面才能测试？

打开，窗口--->首选项

undar

首先在studio中可以测试数据源的连接是否成功，我看你这个数据源连接成功了，都执行到sql处了。
其次就是锁的问题，在insert into select 时候会有锁的问题，你看下下面的连接：
INSERT INTO … SELECT 的锁http://www.zhaokunyao.com/archives/4326

xuni2

yinlun 发表于 2014-5-4 08:21
打开，窗口--->首选项

哦，你说这个啊，测试连接肯定是可以的。

xuni2

undar 发表于 2014-5-4 08:41
首先在studio中可以测试数据源的连接是否成功，我看你这个数据源连接成功了，都执行到sql处了。
其次就是锁 ...

这。。。各种抽象。。。是可以了解了解原理，只是我用的这个语法是KSQL的语法，API介绍的。。。。难道没考虑这些么？？？0 0！！！

jishuang

KSQL会自己转换为不同类型的数据库sql进行执行的，

sql: INSERT INTO BA_STORAGEDETAILACCT(BA_STORAGEDETAILACCT.GPROCESSCODE, BA_STORAGEDETAILACCT.GPROCESSNAME, BA_STORAGEDETAILACCT.GDIRECTION, BA_STORAGEDETAILACCT.YDOCCODE, BA_STORAGEDETAILACCT.YDOCDATE, BA_STORAGEDETAILACCT.YROWID, BA_STORAGEDETAILACCT.YPERIODID, BA_STORAGEDETAILACCT.FCOMPANYID, BA_STORAGEDETAILACCT.FMATID, BA_STORAGEDETAILACCT.FSTID, BA_STORAGEDETAILACCT.YBATCHCODE, BA_STORAGEDETAILACCT.FDIGIT)(SELECT  ?, ?, ?, x.yDocCode AS yDocCode, x.yDocDate AS yDocDate, x.yRowID AS yRowID, SUBSTRING(DATE_FORMAT(x.yDocDate, '%Y-%m-%d'), ?, ?), x.fCompanyID AS fCompanyID, x.fMatID AS fMatID, x.fStID AS fStID, x.yBatchCode AS yBatchCode, x.fDigit AS fDigit FROM (SELECT  CL_PurchaseInStorageHd.YDOCCODE AS CL_PurchaseInStorageHd_yDocCode, CL_PurchaseInStorageHd.YDOCDATE AS CL_PurchaseInStorageHd_yDocDate, CL_PurchaseInStorageHd.FCOMPANYID AS CL_PurchaseInStorageHd_fCompanyID, CL_PurchaseInStorageHd.FSTID AS CL_PurchaseInStorageHd_fStID, CL_PurchaseInStorageItem.YROWID AS CL_PurchaseInStorageItem_yRowID, CL_PurchaseInStorageItem.FMATID AS CL_PurchaseInStorageItem_fMatID, CL_PurchaseInStorageItem.YBATCHCODE AS CL_PurchaseInStorageItem_yBatchCode, CL_PurchaseInStorageItem.FDIGIT AS CL_PurchaseInStorageItem_fDigit FROM CL_PURCHASEINSTORAGEITEM CL_PurchaseInStorageItem JOIN CL_PURCHASEINSTORAGEHD CL_PurchaseInStorageHd ON CL_PurchaseInStorageItem.YID = CL_PurchaseInStorageHd.YID  WHERE CL_PurchaseInStorageItem.YID = ?) x), binds: [/CLOTHING_DRP_GUOYI/PurchaseManage/process/PurchaseInStorage/purchaseInStorageProcess, 采购入库, +, 0, 7, 6417DB3E9B554EF29F0EED65202736FF]出错
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.ksql.KSQLUtils.execSqlUpdate(Unknown Source)
        ... 36 more
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)


这个错误是sql数据库报出来的