topic SQL Server - Alfresco - Snapshot isolation in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/sql-server-alfresco-snapshot-isolation/m-p/237255#M190385 <HTML><HEAD></HEAD><BODY><SPAN>Windows 2003 Standard 64 bit, MS SQL 2005, Alfresco 3</SPAN><BR /><BR /><SPAN>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?</SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />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:&nbsp;&nbsp; <BR />— The error occurred in alfresco/ibatis/#resource.dialect#/content-common-SqlMap.xml.&nbsp; <BR />— The error occurred while applying a parameter map.&nbsp; <BR />— Check the parameter.IdMap.&nbsp; <BR />— Check the statement (update failed).&nbsp; <BR />— 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. <BR />javax.transaction.RollbackException: Transaction didn't commit: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:&nbsp;&nbsp; <BR />— The error occurred in alfresco/ibatis/#resource.dialect#/content-common-SqlMap.xml.&nbsp; <BR />— The error occurred while applying a parameter map.&nbsp; <BR />— Check the parameter.IdMap.&nbsp; <BR />— Check the statement (update failed).&nbsp; <BR />— 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.<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:477)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.web.bean.workflow.ManageTaskDialog.transition(ManageTaskDialog.java:630)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)<BR />&nbsp;&nbsp;&nbsp;at java.lang.reflect.Method.invoke(Method.java:597)<BR />&nbsp;&nbsp;&nbsp;at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:132)<BR />&nbsp;&nbsp;&nbsp;at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:61)<BR />&nbsp;&nbsp;&nbsp;at javax.faces.component.UICommand.broadcast(UICommand.java:109)<BR />&nbsp;&nbsp;&nbsp;at javax.faces.component.UIViewRoot._broadcastForPhase(UIViewRoot.java:97)<BR />&nbsp;&nbsp;&nbsp;at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:171)<BR />&nbsp;&nbsp;&nbsp;at org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:32)<BR />&nbsp;&nbsp;&nbsp;at org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:95)<BR />&nbsp;&nbsp;&nbsp;at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:70)<BR />&nbsp;&nbsp;&nbsp;at javax.faces.webapp.FacesServlet.service(FacesServlet.java:139)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.web.app.servlet.AuthenticationFilter.doFilter(AuthenticationFilter.java:110)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.GeneratedMethodAccessor483.invoke(Unknown Source)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)<BR />&nbsp;&nbsp;&nbsp;at java.lang.reflect.Method.invoke(Method.java:597)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.repo.management.subsystems.ChainingSubsystemProxyFactory$1.invoke(ChainingSubsystemProxyFactory.java:122)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)<BR />&nbsp;&nbsp;&nbsp;at $Proxy192.doFilter(Unknown Source)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.repo.web.filter.beans.BeanProxyFilter.doFilter(BeanProxyFilter.java:88)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.repo.web.filter.beans.NullFilter.doFilter(NullFilter.java:74)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.GeneratedMethodAccessor483.invoke(Unknown Source)<BR />&nbsp;&nbsp;&nbsp;at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)<BR />&nbsp;&nbsp;&nbsp;at java.lang.reflect.Method.invoke(Method.java:597)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.repo.management.subsystems.ChainingSubsystemProxyFactory$1.invoke(ChainingSubsystemProxyFactory.java:122)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)<BR />&nbsp;&nbsp;&nbsp;at $Proxy192.doFilter(Unknown Source)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.repo.web.filter.beans.BeanProxyFilter.doFilter(BeanProxyFilter.java:88)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)<BR />&nbsp;&nbsp;&nbsp;at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)<BR />&nbsp;&nbsp;&nbsp;at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)<BR />&nbsp;&nbsp;&nbsp;at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)<BR />&nbsp;&nbsp;&nbsp;at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)<BR />&nbsp;&nbsp;&nbsp;at java.lang.Thread.run(Thread.java:619)<BR />Caused by: org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation; bad SQL grammar []; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:&nbsp;&nbsp; <BR />— The error occurred in alfresco/ibatis/#resource.dialect#/content-common-SqlMap.xml.&nbsp; <BR />— The error occurred while applying a parameter map.&nbsp; <BR />— Check the parameter.IdMap.&nbsp; <BR />— Check the statement (update failed).&nbsp; <BR />— 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.<BR />&nbsp;&nbsp;&nbsp;at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:107)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:276)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:197)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.orm.ibatis.SqlMapClientTemplate.delete(SqlMapClientTemplate.java:407)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.repo.domain.contentdata.ibatis.ContentDataDAOImpl.deleteContentUrlEntity(ContentDataDAOImpl.java:105)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.repo.domain.contentdata.AbstractContentDataDAOImpl$ContentUrlDeleteTransactionListener.beforeCommit(AbstractContentDataDAOImpl.java:361)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.doBeforeCommit(AlfrescoTransactionSupport.java:716)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.doBeforeCommit(AlfrescoTransactionSupport.java:696)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.repo.transaction.AlfrescoTransactionSupport$TransactionSynchronizationImpl.beforeCommit(AlfrescoTransactionSupport.java:656)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.transaction.support.TransactionSynchronizationUtils.triggerBeforeCommit(TransactionSynchronizationUtils.java:48)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.transaction.support.AbstractPlatformTransactionManager.triggerBeforeCommit(AbstractPlatformTransactionManager.java:835)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:645)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:632)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:314)<BR />&nbsp;&nbsp;&nbsp;at org.alfresco.util.transaction.SpringAwareUserTransaction.commit(SpringAwareUserTransaction.java:467)<BR />&nbsp;&nbsp;&nbsp;… 48 more<BR />Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:&nbsp;&nbsp; <BR />— The error occurred in alfresco/ibatis/#resource.dialect#/content-common-SqlMap.xml.&nbsp; <BR />— The error occurred while applying a parameter map.&nbsp; <BR />— Check the parameter.IdMap.&nbsp; <BR />— Check the statement (update failed).&nbsp; <BR />— 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.<BR />&nbsp;&nbsp;&nbsp;at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:107)<BR />&nbsp;&nbsp;&nbsp;at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)<BR />&nbsp;&nbsp;&nbsp;at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.delete(SqlMapExecutorDelegate.java:480)<BR />&nbsp;&nbsp;&nbsp;at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.delete(SqlMapSessionImpl.java:98)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.orm.ibatis.SqlMapClientTemplate$11.doInSqlMapClient(SqlMapClientTemplate.java:409)<BR />&nbsp;&nbsp;&nbsp;at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:194)<BR />&nbsp;&nbsp;&nbsp;… 60 more<BR />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.<BR />&nbsp;&nbsp;&nbsp;at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)<BR />&nbsp;&nbsp;&nbsp;at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)<BR />&nbsp;&nbsp;&nbsp;at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)<BR />&nbsp;&nbsp;&nbsp;at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)<BR />&nbsp;&nbsp;&nbsp;at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)<BR />&nbsp;&nbsp;&nbsp;at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)<BR />&nbsp;&nbsp;&nbsp;at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)<BR />&nbsp;&nbsp;&nbsp;at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)<BR />&nbsp;&nbsp;&nbsp;at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:320)<BR />&nbsp;&nbsp;&nbsp;at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)<BR />&nbsp;&nbsp;&nbsp;at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:100)<BR />&nbsp;&nbsp;&nbsp;at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)<BR />&nbsp;&nbsp;&nbsp;at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)<BR />&nbsp;&nbsp;&nbsp;… 65 more<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE></BODY></HTML> Thu, 19 Nov 2009 09:32:01 GMT compaqnx7300 2009-11-19T09:32:01Z SQL Server - Alfresco - Snapshot isolation https://connect.hyland.com/t5/alfresco-archive/sql-server-alfresco-snapshot-isolation/m-p/237255#M190385 Windows 2003 Standard 64 bit, MS SQL 2005, Alfresco 3During 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 Thu, 19 Nov 2009 09:32:01 GMT https://connect.hyland.com/t5/alfresco-archive/sql-server-alfresco-snapshot-isolation/m-p/237255#M190385 compaqnx7300 2009-11-19T09:32:01Z Re: SQL Server - Alfresco - Snapshot isolation https://connect.hyland.com/t5/alfresco-archive/sql-server-alfresco-snapshot-isolation/m-p/237256#M190386 <HTML><HEAD></HEAD><BODY><SPAN>Please log this as a bug. This is an optimistic locking error that should normally get handled and retried silently by the repository.</SPAN></BODY></HTML> Thu, 19 Nov 2009 12:33:52 GMT https://connect.hyland.com/t5/alfresco-archive/sql-server-alfresco-snapshot-isolation/m-p/237256#M190386 dward 2009-11-19T12:33:52Z