用户登录界面后首页查询待办死锁

mayuhui

2014-04-18 08:56:36 [http-8080-210] [com.justep.system.action.Engine] ERROR: invokeActions error！
com.justep.model.exception.ModelException: java.lang.reflect.InvocationTargetException
        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)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Thread.java:662)
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.GeneratedMethodAccessor3216.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at com.justep.system.action.Engine.invokeActions(Native Method)
        ... 21 more
Caused by: com.justep.model.exception.ModelException: 执行ksql:select t, t.sName, t.sCURL, t.sProcess, t.sActivity, t.sCreateTime, t.sExecutorFName, t.sExecutorNames, t.sEURL, t.sCreatorFName, t.sShortcut, t.sHints, t.sCreatorFID, t.sExecutorFID, t.sKindID  from SA_Task t  where (t.sKindID='tkTask' or t.sKindID='tkExecutor' or t.sKindID='tkNotice' or t.sKindID IS NULL)  and (t.sStatusID='tesReady' or t.sStatusID='tesExecuting')  and (t.sTypeID IS NULL or t.sTypeID <> 'WORKREMIND') and (('/6F9EB2163A1B43E0ACC80F65D6827620.ogn/86E769D3AEB14D49BF962AA5AF938957.dpt/2EECB2E146EE4B488C3AD3A235525C13.dpt/AD98708E618A46E8BB04294C55A4075A.pos/3E8980D75B3649FA9EEC883EC367029F@AD98708E618A46E8BB04294C55A4075A.psm' like concat(t.sExecutorFID, '%')) and (t.sExecutorFID like '/%')) and ((t.sProcess = '/OA/Custom_Service/process/changeApply/changeApplyProcess') or (t.sProcess = '/OA/Soft_Modify/process/softModifyOpinion/softModifyOpinionProcess') or (t.sProcess = '/OA/Soft_Modify/process/softModifyTask/softModifyTaskProcess') or (t.sProcess = '/OA/Custom_Service/process/remoteService/remoteServiceProcess') or (t.sProcess = '/OA/Project_Manage/process/fieldService/fieldServiceProcess') or (t.sProcess = '/OA/Custom_Service/process/problemRegister/problemRegisterProcess')) order by  t.sCreateTime desc  limit 0,8 时出错！
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.KSQL.select(Unknown Source)
        at Custom_Service.queryCustomerMyTaks(Custom_Service.java:2195)
        ... 25 more
Caused by: com.justep.model.exception.ModelException: 执行sql：SELECT  t.sID, t.SNAME AS sName, t.SCURL AS sCURL, t.SPROCESS AS sProcess, t.SACTIVITY AS sActivity, t.SCREATETIME AS sCreateTime, t.SEXECUTORFNAME AS sExecutorFName, t.SEXECUTORNAMES AS sExecutorNames, t.SEURL AS sEURL, t.SCREATORFNAME AS sCreatorFName, t.SSHORTCUT AS sShortcut, t.SHINTS AS sHints, t.SCREATORFID AS sCreatorFID, t.SEXECUTORFID AS sExecutorFID, t.SKINDID AS sKindID FROM SA_Task t  WHERE  ( (t.SKINDID = ?) OR (t.SKINDID = ?) OR (t.SKINDID = ?) OR (t.SKINDID IS NULL) ) AND ( (t.SSTATUSID = ?) OR (t.SSTATUSID = ?) ) AND ( (t.STYPEID IS NULL) OR (t.STYPEID <> ?) ) AND ( (? LIKE t.SEXECUTORFID + ?) AND (t.SEXECUTORFID LIKE ?) ) AND ( (t.SPROCESS = ?) OR (t.SPROCESS = ?) OR (t.SPROCESS = ?) OR (t.SPROCESS = ?) OR (t.SPROCESS = ?) OR (t.SPROCESS = ?) )   ORDER BY t.SCREATETIME DESC, binds:[tkTask, tkExecutor, tkNotice, tesReady, tesExecuting, WORKREMIND, /6F9EB2163A1B43E0ACC80F65D6827620.ogn/86E769D3AEB14D49BF962AA5AF938957.dpt/2EECB2E146EE4B488C3AD3A235525C13.dpt/AD98708E618A46E8BB04294C55A4075A.pos/3E8980D75B3649FA9EEC883EC367029F@AD98708E618A46E8BB04294C55A4075A.psm, %, /%, /OA/Custom_Service/process/changeApply/changeApplyProcess, /OA/Soft_Modify/process/softModifyOpinion/softModifyOpinionProcess, /OA/Soft_Modify/process/softModifyTask/softModifyTaskProcess, /OA/Custom_Service/process/remoteService/remoteServiceProcess, /OA/Project_Manage/process/fieldService/fieldServiceProcess, /OA/Custom_Service/process/problemRegister/problemRegisterProcess]出错！
        at com.justep.system.ksql.KSQLUtils.execSqlSelect(Unknown Source)
        ... 30 more
Caused by: java.sql.SQLException: 事务(进程 ID 87)与另一个进程被死锁在 锁 资源上，并且已被选作死锁牺牲品。请重新运行该事务。
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
        at net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(TdsCore.java:792)
        at net.sourceforge.jtds.jdbc.JtdsResultSet.<init>(JtdsResultSet.java:146)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:424)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:693)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        ... 31 more

jishuang

Custom_Service.queryCustomerMyTaks(Custom_Service.java:2195)
怎么写的把，是不是操作的SA_task，
跟SELECT  t.sID, t.SNAME AS sName, t.SCURL AS sCURL, t.SPROCESS AS sProcess, t.SACTIVITY AS sActivity, t.SCREATETIME AS sCreateTime, t.SEXECUTORFNAME AS sExecutorFName, t.SEXECUTORNAMES AS sExecutorNames, t.SEURL AS sEURL, t.SCREATORFNAME AS sCreatorFName, t.SSHORTCUT AS sShortcut, t.SHINTS AS sHints, t.SCREATORFID AS sCreatorFID, t.SEXECUTORFID AS sExecutorFID, t.SKINDID AS sKindID FROM SA_Task t  WHERE  ( (t.SKINDID = ?) OR (t.SKINDID = ?) OR (t.SKINDID = ?) OR (t.SKINDID IS NULL) ) AND ( (t.SSTATUSID = ?) OR (t.SSTATUSID = ?) ) AND ( (t.STYPEID IS NULL) OR (t.STYPEID <> ?) ) AND ( (? LIKE t.SEXECUTORFID + ?) AND (t.SEXECUTORFID LIKE ?) ) AND ( (t.SPROCESS = ?) OR (t.SPROCESS = ?) OR (t.SPROCESS = ?) OR (t.SPROCESS = ?) OR (t.SPROCESS = ?) OR (t.SPROCESS = ?) )   ORDER BY t.SCREATETIME DESC, binds:[tkTask, tkExecutor, tkNotice, tesReady, tesExecuting, WORKREMIND, /6F9EB2163A1B43E0ACC80F65D6827620.ogn/86E769D3AEB14D49BF962AA5AF938957.dpt/2EECB2E146EE4B488C3AD3A235525C13.dpt/AD98708E618A46E8BB04294C55A4075A.pos/3E8980D75B3649FA9EEC883EC367029F@AD98708E618A46E8BB04294C55A4075A.psm, %, /%, /OA/Custom_Service/process/changeApply/changeApplyProcess, /OA/Soft_Modify/process/softModifyOpinion/softModifyOpinionProcess, /OA/Soft_Modify/process/softModifyTask/softModifyTaskProcess, /OA/Custom_Service/process/remoteService/remoteServiceProcess, /OA/Project_Manage/process/fieldService/fieldServiceProcess, /OA/Custom_Service/process/problemRegister/problemRegisterProcess]

操作的同一条数据所以被锁了，这个数据库的问题，可以看看数据库中有没有办法查看那条数据被锁了

mayuhui

jishuang 发表于 2014-4-18 17:24
Custom_Service.queryCustomerMyTaks(Custom_Service.java:2195)
怎么写的把，是不是操作的SA_task，
跟SEL ...

这是首页实现待办查询的语句，我们系统现在存在一个用户可以同时登录多次的情况，会不会是这种情况造成的？

zhaixin

参考 http://wenku.baidu.com/link?url= ... j9AtZVvGSVDai3KtDMO

mayuhui

jishuang 发表于 2014-4-18 17:24
Custom_Service.queryCustomerMyTaks(Custom_Service.java:2195)
怎么写的把，是不是操作的SA_task，
跟SEL ...

Custom_Service.queryCustomerMyTasks的实现代码：
        public static Document queryCustomerMyTaks(){
                String condition = TaskUtils.getExecutorCondition("t", ContextHelper.getPerson().getPersonMembers(), true);
               
                String query = "select t, t.sName, t.sCURL, t.sProcess, t.sActivity, t.sCreateTime, t.sExecutorFName, t.sExecutorNames, t.sEURL, t.sCreatorFName, t.sShortcut, t.sHints, t.sCreatorFID, t.sExecutorFID, t.sKindID " +
                                " from SA_Task t " +
                                " where (t.sKindID='tkTask' or t.sKindID='tkExecutor' or t.sKindID='tkNotice' or t.sKindID IS NULL) " +
                                " and (t.sStatusID='tesReady' or t.sStatusID='tesExecuting') " +
                                " and (t.sTypeID IS NULL or t.sTypeID <> 'WORKREMIND')" +
                                " and " + condition +
                                " and ((t.sProcess = '/OA/Custom_Service/process/changeApply/changeApplyProcess') or (t.sProcess = '/OA/Soft_Modify/process/softModifyOpinion/softModifyOpinionProcess') or (t.sProcess = '/OA/Soft_Modify/process/softModifyTask/softModifyTaskProcess') or (t.sProcess = '/OA/Custom_Service/process/remoteService/remoteServiceProcess') or (t.sProcess = '/OA/Project_Manage/process/fieldService/fieldServiceProcess') or (t.sProcess = '/OA/Custom_Service/process/problemRegister/problemRegisterProcess'))"+
                                " order by  t.sCreateTime desc " +
                                " limit 0,8 ";
                return toDOM(KSQL.select(query, null, DATA_MODEL_system, null));
        }


我们现在的做法是将客服部分的待办单独设计了一个自定义待办窗口显示，X5自带的待办过滤了客服待办的内容

zhaixin

先从数据库哪里查一下是哪个语句产生了死锁

zhaixin

上网搜搜 select 产生死锁
例如 http://www.csharpwin.com/csharpspace/11505r288.shtml

zhaixin

1、换成平台的待办任务是否产生死锁
2、死锁后，查看哪个资源被锁，发上来

jishuang

kcgee 发表于 2014-4-22 17:07
很多同事反映在读取待办任务及审批流转时系统很慢，在数据库上跟踪时显示如下图所示，问题可能出在系统表上 ...

SA_Task中有多少条数据？X5用的什么版本？

jishuang

kcgee 发表于 2014-4-22 17:34
150万条左右，X5.2.3;
另外能否发一下平台读取待办任务时的SQL脚本啊

可以按状态、按时间排一序，把已经完成的不用查数据的任务记录导出到一个备份表中，减少SA_Task中的记录数

可以把runtime\BusinessServer\WEB-INF\justep.log.properties中的log4j.logger.com.justep.system.ksql=DEBUG,Console这条注释放开，重启服务，控制台就能输出执行的sql语句