0 Replies Latest reply on Jun 27, 2006 6:39 PM by 666705

    Deadlocks in Worklist with SQL Server

    666705
      I'm looking for information about setting the proper locking configurations for Worklist tables in the SQL Server to minimize deadlock occurrences.
      The server instance console generate the follow exception:

      [java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 130) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.]]

      I retry the transaction using the follow code at Controller.jpf, but the error continue, how i can minimize the problem programatically?

      protected Forward process(ProcessForm form)
      {
      controls.RetryManager retryMng = new controls.RetryManager();

      UserTransaction tx = getUserTransaction();
      try{
      if(!beginTransaction(tx))
      return new Forward( "fail" );
           // process method call many java controls and worklist task
      businessService.process(form.isRaiseError());
      commitTransaction(tx);
      return new Forward( "success" );
      }catch(Exception e){
      rollbackTransaction(tx);
      if(form.getRetries()>0){
           // Retry when SQLException errorCode==1205 || state.equals("40001")
                // Rerun the transaction
      if(retryMng.checkRetry(e)) {
      form.setRetries(form.getRetries()-1);
      return new Forward("retry",form);
      }
      else {
      return new Forward("fail");
      }
      }
      }
           return new Forward( "fail" );
      }

      Full StackTrace:

      479813 <Jun 27, 2006 10:31:53 AM CDT> <Error> <EJB> <BEA-010026> <Exception occurred during commit of transaction Name=[EJB com.bea.wlw.runtime.core.bean.SyncDispatcherBean.invoke(com.bea.wlw.runtime.core.request.Request)],Xid=BEA1-10612213489C2E529A8C(24371849),Status=Rolled back. [Reason=weblogic.utils.NestedRuntimeException: Error writing from beforeCompletion - with nested exception:
      [java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 130) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.]],numRepliesOwedMe=0,numRepliesOwedOthers=0,seconds since begin=13,seconds left=60,XAServerResourceInfo[weblogic.jdbc.wrapper.JTSXAResourceImpl]=(ServerResourceInfo[weblogic.jdbc.wrapper.JTSXAResourceImpl]=(state=rolledback,assigned=cgServer),xar=weblogic.jdbc.wrapper.JTSXAResourceImpl@17396cf,re-Registered = false),XAServerResourceInfo[Juridico Connection Pool]=(ServerResourceInfo[Juridico Connection Pool]=(state=rolledback,assigned=cgServer),xar=Juridico Connection Pool,re-Registered = false),SCInfo[d_cambios+cgServer]=(state=rolledback),properties=({weblogic.transaction.name=[EJB com.bea.wlw.runtime.core.bean.SyncDispatcherBean.invoke(com.bea.wlw.runtime.core.request.Request)], weblogic.jdbc=t3://172.16.20.136:7001}),local properties=({modifiedListeners=[weblogic.ejb20.internal.TxManager$TxListener@1524590, weblogic.ejb20.internal.TxManager$TxListener@19ff424, weblogic.ejb20.internal.TxManager$TxListener@1509a95, weblogic.ejb20.internal.TxManager$TxListener@1791191], weblogic.jdbc.jta.Juridico Connection Pool=weblogic.jdbc.wrapper.TxInfo@1c4e884}),OwnerTransactionManager=ServerTM[ServerCoordinatorDescriptor=(CoordinatorURL=cgServer+172.16.20.136:7001+d_cambios+t3+, XAResources={JMS_FileStore, JMS_cgJMSStore, Usuario Connection Pool, weblogic.jdbc.wrapper.JTSXAResourceImpl, Juridico Connection Pool},NonXAResources={})],CoordinatorURL=cgServer+172.16.20.136:7001+d_cambios+t3+): java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 130) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
           at weblogic.jdbc.base.BaseExceptions.createException(Ljava/lang/String;Ljava/lang/String;I)Ljava/sql/SQLException;(Unknown Source)
           at weblogic.jdbc.base.BaseExceptions.getException(Ljava/sql/SQLException;II[Ljava/lang/String;Ljava/lang/String;I)Ljava/sql/SQLException;(Unknown Source)
           at weblogic.jdbc.sqlserver.tds.TDSRequest.processErrorToken()V(Unknown Source)
           at weblogic.jdbc.sqlserver.tds.TDSRequest.processReplyToken(BLweblogic/jdbc/base/BaseWarnings;)Z(Unknown Source)
           at weblogic.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(BLweblogic/jdbc/base/BaseWarnings;)Z(Unknown Source)
           at weblogic.jdbc.sqlserver.tds.TDSRequest.processReply(ILweblogic/jdbc/base/BaseWarnings;)V(Unknown Source)
           at weblogic.jdbc.sqlserver.SQLServerImplStatement.getNextResultType()I(Unknown Source)
           at weblogic.jdbc.base.BaseStatement.commonTransitionToState(I)V(Unknown Source)
           at weblogic.jdbc.base.BaseStatement.postImplExecute(Z)V(Unknown Source)
           at weblogic.jdbc.base.BasePreparedStatement.postImplExecute(Z)V(Unknown Source)
           at weblogic.jdbc.base.BaseStatement.commonExecute()V(Unknown Source)
           at weblogic.jdbc.base.BaseStatement.executeUpdateInternal()I(Unknown Source)
           at weblogic.jdbc.base.BasePreparedStatement.executeUpdate()I(Unknown Source)
           at weblogic.jdbcspy.SpyPreparedStatement.executeUpdate()I(Unknown Source)
           at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate()I(PreparedStatement.java:147)
           at weblogic.ejb20.manager.BaseEntityManager.executeInsertStmt(Ljava/util/List;Ljavax/transaction/Transaction;Ljava/util/Set;Z)V(BaseEntityManager.java:589)
           at weblogic.ejb20.manager.BaseEntityManager.executeDBOperations(Ljava/util/List;Ljavax/transaction/Transaction;Ljava/util/Set;ZI)V(BaseEntityManager.java:475)
           at weblogic.ejb20.internal.TxManager$TxListener.executeDBOperations(Ljava/util/Set;ZI)V(TxManager.java:585)
           at weblogic.ejb20.internal.TxManager.executeDBOperations(Ljavax/transaction/Transaction;Ljava/util/Set;ZI)V(TxManager.java:151)
           at weblogic.ejb20.manager.BaseEntityManager.executeDBOperations(Ljava/util/Set;ZI)V(BaseEntityManager.java:457)
           at weblogic.ejb20.manager.BaseEntityManager.executeInsertStmt(Ljava/util/List;Ljavax/transaction/Transaction;Ljava/util/Set;Z)V(BaseEntityManager.java:501)
           at weblogic.ejb20.manager.BaseEntityManager.executeDBOperations(Ljava/util/List;Ljavax/transaction/Transaction;Ljava/util/Set;ZI)V(BaseEntityManager.java:475)
           at weblogic.ejb20.internal.TxManager$TxListener.executeDBOperations(Ljava/util/Set;ZI)V(TxManager.java:585)
           at weblogic.ejb20.internal.TxManager$TxListener.executeDBOperationsDriver(Z)V(TxManager.java:560)
           at weblogic.ejb20.internal.TxManager$TxListener.beforeCompletion()V(TxManager.java:748)
           at weblogic.transaction.internal.ServerSCInfo.callBeforeCompletions(Lweblogic/transaction/internal/TransactionImpl;)V(ServerSCInfo.java:1026)
           at weblogic.transaction.internal.ServerSCInfo.startPrePrepareAndChain(Lweblogic/transaction/internal/ServerTransactionImpl;I)V(ServerSCInfo.java:116)
           at weblogic.transaction.internal.ServerTransactionImpl.localPrePrepareAndChain()V(ServerTransactionImpl.java:1202)
           at weblogic.transaction.internal.ServerTransactionImpl.globalPrePrepare()V(ServerTransactionImpl.java:1974)
           at weblogic.transaction.internal.ServerTransactionImpl.internalCommit()V(ServerTransactionImpl.java:257)
           at weblogic.transaction.internal.ServerTransactionImpl.commit()V(ServerTransactionImpl.java:228)
           at weblogic.ejb20.internal.BaseEJBObject.postInvoke(Lweblogic/ejb20/internal/InvocationWrapper;Ljava/lang/Throwable;)V(BaseEJBObject.java:320)
      --------------- nested within: ------------------
      weblogic.utils.NestedRuntimeException: Error writing from beforeCompletion - with nested exception:
      [java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 130) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.]
           at weblogic.ejb20.internal.TxManager$TxListener.beforeCompletion()V(TxManager.java:760)
           at weblogic.transaction.internal.ServerSCInfo.callBeforeCompletions(Lweblogic/transaction/internal/TransactionImpl;)V(ServerSCInfo.java:1026)
           at weblogic.transaction.internal.ServerSCInfo.startPrePrepareAndChain(Lweblogic/transaction/internal/ServerTransactionImpl;I)V(ServerSCInfo.java:116)
           at weblogic.transaction.internal.ServerTransactionImpl.localPrePrepareAndChain()V(ServerTransactionImpl.java:1202)
           at weblogic.transaction.internal.ServerTransactionImpl.globalPrePrepare()V(ServerTransactionImpl.java:1974)
           at weblogic.transaction.internal.ServerTransactionImpl.internalCommit()V(ServerTransactionImpl.java:257)
           at weblogic.transaction.internal.ServerTransactionImpl.commit()V(ServerTransactionImpl.java:228)
           at weblogic.ejb20.internal.BaseEJBObject.postInvoke(Lweblogic/ejb20/internal/InvocationWrapper;Ljava/lang/Throwable;)V(BaseEJBObject.java:320)
           at weblogic.ejb20.internal.StatelessEJBObject.postInvoke(Lweblogic/ejb20/internal/InvocationWrapper;Ljava/lang/Throwable;)V(StatelessEJBObject.java:168)
           at com.bea.wlw.runtime.core.bean.SyncDispatcher_k1mrl8_EOImpl.invoke(Lcom/bea/wlw/runtime/core/request/Request;)Lcom/bea/wlw/runtime/core/request/Response;(SyncDispatcher_k1mrl8_EOImpl.java:56)
           at com.bea.wlw.runtime.core.dispatcher.Dispatcher.remoteDispatch(Lcom/bea/wlw/runtime/core/dispatcher/DispFile;Lcom/bea/wlw/runtime/core/request/Request;)Lcom/bea/wlw/runtime/core/request/Response;(Dispatcher.java:161)
           at com.bea.wlw.runtime.core.dispatcher.ServiceHandleImpl.invoke(Lcom/bea/wlw/runtime/core/request/Request;)Ljava/lang/Object;(ServiceHandleImpl.java:436)
           at com.bea.wlw.runtime.core.dispatcher.WlwProxyImpl._invoke(Lcom/bea/wlw/runtime/core/request/ExecRequest;)Ljava/lang/Object;(WlwProxyImpl.java:326)
           at com.bea.wlw.runtime.core.dispatcher.WlwProxyImpl.invoke(Ljava/lang/Object;Ljava/lang/reflect/Method;[Ljava/lang/Object;)Ljava/lang/Object;(WlwProxyImpl.java:315)
           at $Proxy18.completarTarea(Ljava/lang/String;)V(Unknown Source)
           at JuicioFiscal.RegistroComplementarioJF.RegistroComplementarioJFController.begin(LAdmonTareas/TareaForm;)Lcom/bea/wlw/netui/pageflow/Forward;(RegistroComplementarioJFController.jpf:700)
           at jrockit.reflect.NativeMethodInvoker.invoke0(Ljava/lang/Object;ILjava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;(Unknown Source)
           at jrockit.reflect.NativeMethodInvoker.invoke(Ljava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;(Unknown Source)
           at jrockit.reflect.VirtualNativeMethodInvoker.invoke(Ljava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;(Unknown Source)
           at java.lang.reflect.Method.invoke(Ljava/lang/Object;[Ljava/lang/Object;I)Ljava/lang/Object;(Unknown Source)
           at com.bea.wlw.netui.pageflow.FlowController.invokeActionMethod(Ljava/lang/reflect/Method;Lorg/apache/struts/action/ActionForm;Ljavax/servlet/http/HttpServletRequest;Lorg/apache/struts/action/ActionMapping;)Lorg/apache/struts/action/ActionForward;(FlowController.java:1498)
           at com.bea.wlw.netui.pageflow.FlowController.getActionMethodForward(Ljava/lang/String;Lorg/apache/struts/action/ActionForm;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;Lorg/apache/struts/action/ActionMapping;)Lorg/apache/struts/action/ActionForward;(FlowController.java:1433)
           at com.bea.wlw.netui.pageflow.FlowController.internalExecute(Lorg/apache/struts/action/ActionMapping;Lorg/apache/struts/action/ActionForm;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)Lorg/apache/struts/action/ActionForward;(FlowController.java:764)
           at com.bea.wlw.netui.pageflow.PageFlowController.internalExecute(Lorg/apache/struts/action/ActionMapping;Lorg/apache/struts/action/ActionForm;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)Lorg/apache/struts/action/ActionForward;(PageFlowController.java:211)
           at com.bea.wlw.netui.pageflow.FlowController.execute(Lorg/apache/struts/action/ActionMapping;Lorg/apache/struts/action/ActionForm;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)Lorg/apache/struts/action/ActionForward;(FlowController.java:594)
           at org.apache.struts.action.RequestProcessor.processActionPerform(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;Lorg/apache/struts/action/Action;Lorg/apache/struts/action/ActionForm;Lorg/apache/struts/action/ActionMapping;)Lorg/apache/struts/action/ActionForward;(RequestProcessor.java:484)
           at org.apache.struts.action.RequestProcessor.process(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(RequestProcessor.java:274)
           at com.bea.wlw.netui.pageflow.PageFlowRequestProcessor.process(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(PageFlowRequestProcessor.java:650)
           at com.bea.wlw.netui.pageflow.AutoRegisterActionServlet.process(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(AutoRegisterActionServlet.java:527)
           at com.bea.wlw.netui.pageflow.PageFlowActionServlet.process(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(PageFlowActionServlet.java:152)
           at org.apache.struts.action.ActionServlet.doPost(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(ActionServlet.java:525)
           at javax.servlet.http.HttpServlet.service(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(HttpServlet.java:760)
      --------------- nested within: ------------------
      weblogic.transaction.RollbackException: Unexpected exception in beforeCompletion: sync=weblogic.ejb20.internal.TxManager$TxListener@17b710b
      Error writing from beforeCompletion - with nested exception:
      [weblogic.utils.NestedRuntimeException: Error writing from beforeCompletion - with nested exception:
      [java.sql.SQLException: [BEA][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 131) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.]]
           at weblogic.transaction.internal.TransactionImpl.throwRollbackException()V(TransactionImpl.java:1684)
           at weblogic.transaction.internal.ServerTransactionImpl.internalCommit()V(ServerTransactionImpl.java:311)
           at weblogic.transaction.internal.ServerTransactionImpl.commit()V(ServerTransactionImpl.java:228)
           at weblogic.ejb20.internal.BaseEJBObject.postInvoke(Lweblogic/ejb20/internal/InvocationWrapper;Ljava/lang/Throwable;)V(BaseEJBObject.java:320)
           at weblogic.ejb20.internal.StatelessEJBObject.postInvoke(Lweblogic/ejb20/internal/InvocationWrapper;Ljava/lang/Throwable;)V(StatelessEJBObject.java:168)
           at com.bea.wlw.runtime.core.bean.SyncDispatcher_k1mrl8_EOImpl.invoke(Lcom/bea/wlw/runtime/core/request/Request;)Lcom/bea/wlw/runtime/core/request/Response;(SyncDispatcher_k1mrl8_EOImpl.java:56)
           at com.bea.wlw.runtime.core.dispatcher.Dispatcher.remoteDispatch(Lcom/bea/wlw/runtime/core/dispatcher/DispFile;Lcom/bea/wlw/runtime/core/request/Request;)Lcom/bea/wlw/runtime/core/request/Response;(Dispatcher.java:161)
           at com.bea.wlw.runtime.core.dispatcher.ServiceHandleImpl.invoke(Lcom/bea/wlw/runtime/core/request/Request;)Ljava/lang/Object;(ServiceHandleImpl.java:436)
           at com.bea.wlw.runtime.core.dispatcher.WlwProxyImpl._invoke(Lcom/bea/wlw/runtime/core/request/ExecRequest;)Ljava/lang/Object;(WlwProxyImpl.java:326)
           at com.bea.wlw.runtime.core.dispatcher.WlwProxyImpl.invoke(Ljava/lang/Object;Ljava/lang/reflect/Method;[Ljava/lang/Object;)Ljava/lang/Object;(WlwProxyImpl.java:315)
           at $Proxy18.completarTarea(Ljava/lang/String;)V(Unknown Source)
           at JuicioFiscal.RegistroComplementarioJF.RegistroComplementarioJFController.begin(LAdmonTareas/TareaForm;)Lcom/bea/wlw/netui/pageflow/Forward;(RegistroComplementarioJFController.jpf:700)
           at jrockit.reflect.NativeMethodInvoker.invoke0(Ljava/lang/Object;ILjava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;(Unknown Source)
           at jrockit.reflect.NativeMethodInvoker.invoke(Ljava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;(Unknown Source)
           at jrockit.reflect.VirtualNativeMethodInvoker.invoke(Ljava/lang/Object;[Ljava/lang/Object;)Ljava/lang/Object;(Unknown Source)
           at java.lang.reflect.Method.invoke(Ljava/lang/Object;[Ljava/lang/Object;I)Ljava/lang/Object;(Unknown Source)
           at com.bea.wlw.netui.pageflow.FlowController.invokeActionMethod(Ljava/lang/reflect/Method;Lorg/apache/struts/action/ActionForm;Ljavax/servlet/http/HttpServletRequest;Lorg/apache/struts/action/ActionMapping;)Lorg/apache/struts/action/ActionForward;(FlowController.java:1498)
           at com.bea.wlw.netui.pageflow.FlowController.getActionMethodForward(Ljava/lang/String;Lorg/apache/struts/action/ActionForm;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;Lorg/apache/struts/action/ActionMapping;)Lorg/apache/struts/action/ActionForward;(FlowController.java:1433)
           at com.bea.wlw.netui.pageflow.FlowController.internalExecute(Lorg/apache/struts/action/ActionMapping;Lorg/apache/struts/action/ActionForm;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)Lorg/apache/struts/action/ActionForward;(FlowController.java:764)
           at com.bea.wlw.netui.pageflow.PageFlowController.internalExecute(Lorg/apache/struts/action/ActionMapping;Lorg/apache/struts/action/ActionForm;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)Lorg/apache/struts/action/ActionForward;(PageFlowController.java:211)
           at com.bea.wlw.netui.pageflow.FlowController.execute(Lorg/apache/struts/action/ActionMapping;Lorg/apache/struts/action/ActionForm;Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)Lorg/apache/struts/action/ActionForward;(FlowController.java:594)
           at org.apache.struts.action.RequestProcessor.processActionPerform(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;Lorg/apache/struts/action/Action;Lorg/apache/struts/action/ActionForm;Lorg/apache/struts/action/ActionMapping;)Lorg/apache/struts/action/ActionForward;(RequestProcessor.java:484)
           at org.apache.struts.action.RequestProcessor.process(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(RequestProcessor.java:274)
           at com.bea.wlw.netui.pageflow.PageFlowRequestProcessor.process(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(PageFlowRequestProcessor.java:650)
           at com.bea.wlw.netui.pageflow.AutoRegisterActionServlet.process(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(AutoRegisterActionServlet.java:527)
           at com.bea.wlw.netui.pageflow.PageFlowActionServlet.process(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(PageFlowActionServlet.java:152)
           at org.apache.struts.action.ActionServlet.doPost(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(ActionServlet.java:525)
           at javax.servlet.http.HttpServlet.service(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponse;)V(HttpServlet.java:760)
           at javax.servlet.http.HttpServlet.service(Ljavax/servlet/ServletRequest;Ljavax/servlet/ServletResponse;)V(HttpServlet.java:853)
           at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run()Ljava/lang/Object;(ServletStubImpl.java:1072)
           at weblogic.servlet.internal.ServletStubImpl.invokeServlet(Ljavax/servlet/ServletRequest;Ljavax/servlet/ServletResponse;Lweblogic/servlet/internal/FilterChainImpl;)V(ServletStubImpl.java:465)
           at weblogic.servlet.internal.ServletStubImpl.invokeServlet(Ljavax/servlet/ServletRequest;Ljavax/servlet/ServletResponse;)V(ServletStubImpl.java:348)