AnsweredAssumed Answered

SQL Server - Alfresco - Snapshot isolation

Question asked by compaqnx7300 on Nov 19, 2009
Latest reply on Nov 19, 2009 by dward
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 more
Caused 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 more
Caused 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

Outcomes