SQL Server - Alfresco - Snapshot isolation

[compaqnx7300]

Windows 2003 Standard 64 bit, MS SQL 2005, Alfresco 3

During executing transitions for advance workflow tasks (a few concurrent users) sometimes user receive error about aborted snapshot isolation transaction due to update conflict, as shown below. Did I forget about anything in configuration of SQL Server or Alfresco or is it a bug?

09:57:30,453 ERROR [org.alfresco.web.ui.common.Utils] Transaction didn't commit: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   — The error occurred in alfresco/ibatis/#resource.dialect#/content-common-SqlMap.xml.  — The error occurred while applying a parameter map.  — Check the parameter.IdMap.  — Check the statement (update failed).  — Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.alf_content_data' directly or indirectly in database 'one' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement. javax.transaction.RollbackException: Transaction didn't commit: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   — The error occurred in alfresco/ibatis/#resource.dialect#/content-common-SqlMap.xml.  — The error occurred while applying a parameter map.  — Check the parameter.IdMap.  — Check the statement (update failed).  — Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.alf_content_data' directly or indirectly in database 'one' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.   at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:477)   at org.alfresco.web.bean.workflow.ManageTaskDialog.transition(ManageTaskDialog.java:630)   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:597)   at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:132)   at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:61)   at javax.faces.component.UICommand.broadcast(UICommand.java:109)   at javax.faces.component.UIViewRoot._broadcastForPhase(UIViewRoot.java:97)   at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:171)   at org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:32)   at org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:95)   at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:70)   at javax.faces.webapp.FacesServlet.service(FacesServlet.java:139)   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)   at org.alfresco.web.app.servlet.AuthenticationFilter.doFilter(AuthenticationFilter.java:110)   at sun.reflect.GeneratedMethodAccessor483.invoke(Unknown Source)   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)   at java.lang.reflect.Method.invoke(Method.java:597)   at org.alfresco.repo.management.subsystems.ChainingSubsystemProxyFactory$1.invoke(ChainingSubsystemProxyFactory.java:122)   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)   at $Proxy192.doFilter(Unknown Source)   at org.alfresco.repo.web.filter.beans.BeanProxyFilter.doFilter(BeanProxyFilter.java:88)   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)   at org.alfresco.repo.web.filter.beans.NullFilter.doFilter(NullFilter.java:74)   at sun.reflect.GeneratedMethodAccessor483.invoke(Unknown Source)   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)   at java.lang.reflect.Method.invoke(Method.java:597)   at org.alfresco.repo.management.subsystems.ChainingSubsystemProxyFactory$1.invoke(ChainingSubsystemProxyFactory.java:122)   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)   at $Proxy192.doFilter(Unknown Source)   at org.alfresco.repo.web.filter.beans.BeanProxyFilter.doFilter(BeanProxyFilter.java:88)   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)   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:128)   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:286)   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)   at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)   at java.lang.Thread.run(Thread.java:619)Caused by: org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   — The error occurred in alfresco/ibatis/#resource.dialect#/content-common-SqlMap.xml.  — The error occurred while applying a parameter map.  — Check the parameter.IdMap.  — Check the statement (update failed).  — Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.alf_content_data' directly or indirectly in database 'one' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.   at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:107)   at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:276)   at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:197)   at org.springframework.orm.ibatis.SqlMapClientTemplate.delete(SqlMapClientTemplate.java:407)   at org.alfresco.repo.domain.contentdata.ibatis.ContentDataDAOImpl.deleteContentUrlEntity(ContentDataDAOImpl.java:105)   at org.alfresco.repo.domain.contentdata.AbstractContentDataDAOImpl$ContentUrlDeleteTransactionListener.beforeCommit(AbstractContentDataDAOImpl.java:361)   at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.doBeforeCommit(AlfrescoTransactionSupport.java:716)   at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.doBeforeCommit(AlfrescoTransactionSupport.java:696)   at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.beforeCommit(AlfrescoTransactionSupport.java:656)   at org.springframework.transaction.support.TransactionSynchronizationUtils.triggerBeforeCommit(TransactionSynchronizationUtils.java:48)   at org.springframework.transaction.support.AbstractPlatformTransactionManager.triggerBeforeCommit(AbstractPlatformTransactionManager.java:835)   at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:645)   at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:632)   at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:314)   at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:467)   … 48 moreCaused by: com.ibatis.common.jdbc.exception.NestedSQLException:   — The error occurred in alfresco/ibatis/#resource.dialect#/content-common-SqlMap.xml.  — The error occurred while applying a parameter map.  — Check the parameter.IdMap.  — Check the statement (update failed).  — Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.alf_content_data' directly or indirectly in database 'one' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.   at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:107)   at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)   at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.delete(SqlMapExecutorDelegate.java:480)   at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.delete(SqlMapSessionImpl.java:98)   at org.springframework.orm.ibatis.SqlMapClientTemplate$11.doInSqlMapClient(SqlMapClientTemplate.java:409)   at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:194)   … 60 moreCaused by: com.microsoft.sqlserver.jdbc.SQLServerException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.alf_content_data' directly or indirectly in database 'one' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)   at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)   at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)   at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)   at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:320)   at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)   at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:100)   at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)   at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)   … 65 more‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[dward]

Please log this as a bug. This is an optimistic locking error that should normally get handled and retried silently by the repository.