deadlock in load test with db2

[long_187]

Load Test:
  2 concurrent;
  db2 v9.7;
  activiti v5.12
  weblogic 10

deadlock occuerd:

1190995 2013-04-08 16:48:08.176 [ACTIVE] ExecuteThread: '541' for queue: 'weblogic.kernel.Default (self-tuning)' true POST /aps/SubmitApsTask.param [f=SubmitApsTask, ti
d=, ip=197.3.176.106, sid=]
 ERROR c.t.f.f.s.ExceptionHandleFilter - com.tesla.framework.filter.support.ExceptionHandleFilter
org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.ibm.db2.jcc.am.to: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=2, DRIVER=3.58.82
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.deleteTask-Inline
### The error occurred while setting parameters
### SQL: delete from ACT_RU_TASK where ID_ = ? and REV_ = ?
### Cause: com.ibm.db2.jcc.am.to: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=2, DRIVER=3.58.82
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:158) ~[mybatis-3.1.1.jar:3.1.1]
        at org.activiti.engine.impl.db.DbSqlSession$DeletePersistentObjectOperation.execute(DbSqlSession.java:237) ~[activiti-engine-5.12.jar:5.12]
        at org.activiti.engine.impl.db.DbSqlSession.flushDeletes(DbSqlSession.java:668) ~[activiti-engine-5.12.jar:5.12]
        at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:462) ~[activiti-engine-5.12.jar:5.12]
        at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:168) ~[activiti-engine-5.12.jar:5.12]
        at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:115) ~[activiti-engine-5.12.jar:5.12]
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:70) ~[activiti-engine-5.12.jar:5.12]
        at org.activiti.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:42) ~[activiti-spring-5.12.jar:na]
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130) ~[spring-tx-3.2.0.RELEASE.jar:3.2.0.RELEASE]
        at org.activiti.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:40) ~[activiti-spring-5.12.jar:na]
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31) ~[activiti-engine-5.12.jar:5.12]
        at org.activiti.engine.impl.FormServiceImpl.submitTaskFormData(FormServiceImpl.java:70) ~[activiti-engine-5.12.jar:5.12]
        at com.tesla.bpmn.action.SubmitTaskFormDataAction.execute(SubmitTaskFormDataAction.java:25) ~[_wl_cls_gen.jar:na]
        at com.tesla.framework.function.impl.SequenceCallActionFunction.execute(SequenceCallActionFunction.java:22) ~[tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPSHOT]
        at com.tesla.framework.function.support.FunctionFilterChain.doFilter(FunctionFilterChain.java:31) ~[tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPSHOT]
        at com.tesla.framework.function.support.FunctionControllerFilterChain.doFilter(FunctionControllerFilterChain.java:31) [tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPS
HOT]
        at com.tesla.framework.admin.monitor.support.FunctionMonitorFilter.doFilter(FunctionMonitorFilter.java:117) ~[tesla-admin-monitor-0.0.1-20130325.021048-37.jar:0
.0.1-SNAPSHOT]
        at com.tesla.framework.function.support.FunctionControllerFilterChain.doFilter(FunctionControllerFilterChain.java:26) [tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPS
HOT]
        at com.tesla.framework.filter.support.ValidationFilter.doFilter(ValidationFilter.java:38) ~[tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPSHOT]
        at com.tesla.framework.function.support.FunctionControllerFilterChain.doFilter(FunctionControllerFilterChain.java:26) [tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPS
HOT]
        at com.tesla.framework.filter.support.ParameterFilter.doFilter(ParameterFilter.java:59) ~[tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPSHOT]
        at com.tesla.framework.function.support.FunctionControllerFilterChain.doFilter(FunctionControllerFilterChain.java:26) [tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPS
HOT]
        at com.tesla.framework.filter.support.FunctionControlFilter.doFilter(FunctionControlFilter.java:25) ~[tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPSHOT]
        at com.tesla.framework.function.support.FunctionControllerFilterChain.doFilter(FunctionControllerFilterChain.java:26) [tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPS
HOT]
        at com.tesla.framework.filter.support.ExceptionHandleFilter.doFilter(ExceptionHandleFilter.java:35) ~[tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPSHOT]
        at com.tesla.framework.function.support.FunctionControllerFilterChain.doFilter(FunctionControllerFilterChain.java:26) [tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPS
HOT]
        at com.tesla.framework.filter.support.FunctionMDCFilter.doFilter(FunctionMDCFilter.java:20) [tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPSHOT]
        at com.tesla.framework.function.support.FunctionControllerFilterChain.doFilter(FunctionControllerFilterChain.java:26) [tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPS
HOT]
        at com.tesla.framework.function.support.DefaultFunctionController.execute(DefaultFunctionController.java:39) [tesla-core-0.0.1-SNAPSHOT.jar:0.0.1-SNAPSHOT]
        at com.tesla.framework.channel.http.support.AbstractSpringHttpController.handleRequestInternal(AbstractSpringHttpController.java:65) [tesla-channel-http-0.0.1-2
0130323.070636-43.jar:0.0.1-SNAPSHOT]
        at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153) [spring-webmvc-3.2.0.RELEASE.jar:3.2.0.RELEASE]
        at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48) [spring-webmvc-3.2.0.RELEASE.jar:3.2.0.RELE
ASE]
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925) [spring-webmvc-3.2.0.RELEASE.jar:3.2.0.RELEASE]
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856) [spring-webmvc-3.2.0.RELEASE.jar:3.2.0.RELEASE]
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:915) [spring-webmvc-3.2.0.RELEASE.jar:3.2.0.RELEASE]
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:822) [spring-webmvc-3.2.0.RELEASE.jar:3.2.0.RELEASE]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:727) [javax.servlet_1.0.0.0_2-5.jar:2.5]
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:796) [spring-webmvc-3.2.0.RELEASE.jar:3.2.0.RELEASE]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) [javax.servlet_1.0.0.0_2-5.jar:2.5]
        at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227) [weblogic.jar:10.3.5.0]
        at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125) [weblogic.jar:10.3.5.0]
        at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:300) [weblogic.jar:10.3.5.0]
        at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26) [weblogic.jar:10.3.5.0]
        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) [weblogic.jar:10.3.5.0]
        at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) [shiro-web-1.2.1.jar:1.2.1]
        at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365) [shiro-web-1.2.1.jar:1.2.1]
        at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) [shiro-core-1.2.1.jar:1.2.1]
        at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) [shiro-core-1.2.1.jar:1.2.1]
        at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383) [shiro-core-1.2.1.jar:1.2.1]
        at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362) [shiro-web-1.2.1.jar:1.2.1]
        at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) [shiro-web-1.2.1.jar:1.2.1]
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346) [spring-web-3.2.0.RELEASE.jar:3.2.0.RELEASE]
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259) [spring-web-3.2.0.RELEASE.jar:3.2.0.RELEASE]
        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) [weblogic.jar:10.3.5.0]
        at com.tesla.framework.logconfig.servlet.SetLoggingContextFilter.doFilter(SetLoggingContextFilter.java:53) [tesla-logconfig-0.0.1-20130323.070641-27.jar:0.0.1-S
NAPSHOT]
        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) [weblogic.jar:10.3.5.0]
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) [spring-web-3.2.0.RELEASE.jar:3.2.0.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-3.2.0.RELEASE.jar:3.2.0.RELEASE]
        at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56) [weblogic.jar:10.3.5.0]
        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3715) [weblogic.jar:10.3.5.0]
        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3681) [weblogic.jar:10.3.5.0]
        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321) [com.bea.core.weblogic.security.identity_1.1.2.1.jar:1.1.2.1]
        at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120) [com.bea.core.weblogic.security.wls_1.0.0.0_6-1-0-0.jar:6.1.0.0]
        at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2277) [weblogic.jar:10.3.5.0]
        at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2183) [weblogic.jar:10.3.5.0]
        at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1454) [weblogic.jar:10.3.5.0]
        at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209) [com.bea.core.weblogic.workmanager_1.10.0.0.jar:1.10.0.0]
        at weblogic.work.ExecuteThread.run(ExecuteThread.java:178) [com.bea.core.weblogic.workmanager_1.10.0.0.jar:1.10.0.0]
Caused by: com.ibm.db2.jcc.am.to: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=2, DRIVER=3.58.82
        at com.ibm.db2.jcc.am.ed.a(ed.java:663) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.am.ed.a(ed.java:60) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.am.ed.a(ed.java:127) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.am.tm.c(tm.java:2523) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.t4.fb.s(fb.java:940) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.t4.fb.k(fb.java:390) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.t4.fb.a(fb.java:61) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.t4.t.a(t.java:50) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.t4.vb.b(vb.java:218) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.am.um.jc(um.java:2860) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.am.um.b(um.java:3795) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.am.um.cc(um.java:2250) ~[db2jcc-9.0.jar:na]
        at com.ibm.db2.jcc.am.um.execute(um.java:2234) ~[db2jcc-9.0.jar:na]
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[commons-dbcp-1.4.jar:1.4]
        at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172) ~[commons-dbcp-1.4.jar:1.4]
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:108) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75) ~[mybatis-3.1.1.jar:3.1.1]
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145) ~[mybatis-3.1.1.jar:3.1.1]
        … 68 common frames omitted‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[jbarrez]

> It is not strange that it deadlocks because of foreign keys in db2.

Can you elaborate on that? Why would a database not be smart enough to cope with this? Unless the data that is inserted depends on something that is inserted at the same time?

That seems like really way too much foreign keys … foreign keys are there for a reason.
What isolation level are you running your tests on?

[long_187]

Hi jbarrez:

I have find out the reason of it deadlocks because of foreign keys in db2:
The table ACT_RU_IDENTITYLINK  has a foreign key _TASK_ID refference to the table ACT_RU_TASK(ID_). If delete a task from ACT_RU_TASK, db2 will execute TB SCAN(Table Scan)  on ACT_RU_IDENTITYLINK and lock the ACT_RU_IDENTITYLINK table. If two thread do TB SCAN at the same time, the deadlock occur.
Then, I create a index on ACT_RU_IDENTITYLINK(_TASK_ID), db2 will not execute TB SCAN instead of IX SCAN(Index SCAN)  and only lock the matched row.
In other words,  Create index on each foreign keys, can resovle the deadlock.

Indexies to append:
create index ACT_IDX_EVENT_SUBSCR_EXEC_ID on ACT_RU_EVENT_SUBSCR(EXECUTION_ID_);

create index ACT_IDX_EXEC_PARENT_ID on ACT_RU_EXECUTION(PARENT_ID_);
create index ACT_IDX_EXEC_PROC_DEF_ID on ACT_RU_EXECUTION(PROC_DEF_ID_);
create index ACT_IDX_EXEC_PROC_INST_ID on ACT_RU_EXECUTION(PROC_INST_ID_);
create index ACT_IDX_EXEC_SUPER_EXEC on ACT_RU_EXECUTION(SUPER_EXEC_);

create index ACT_IDX_IDENT_LNK_TASK_ID on ACT_RU_IDENTITYLINK(TASK_ID_);
create index ACT_IDX_IDENT_LNK_PROC_INST_ID on ACT_RU_IDENTITYLINK(PROC_INST_ID_);

create index ACT_IDX_JOB_EXCEPTION_STACK_ID on ACT_RU_JOB(EXCEPTION_STACK_ID_);

create index ACT_IDX_TASK_EXECUTION_ID on ACT_RU_TASK(EXECUTION_ID_);
create index ACT_IDX_TASK_PROC_DEF_ID on ACT_RU_TASK(PROC_DEF_ID_);
create index ACT_IDX_TASK_PROC_INST_ID on ACT_RU_TASK(PROC_INST_ID_);

create index ACT_IDX_VARIABLE_BYTEARRAY_ID on ACT_RU_VARIABLE(BYTEARRAY_ID_);
create index ACT_IDX_VARIABLE_EXECUTION_ID on ACT_RU_VARIABLE(EXECUTION_ID_);
create index ACT_IDX_VARIABLE_PROC_INST_ID on ACT_RU_VARIABLE(PROC_INST_ID_);

[jbarrez]

I saw your JIRA issue (thx) and will check/react there.