Re: [Virtuoso-users] debuging Transaction deadlock

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Hi Marcelo,

Transactional and update intensive SPARQL update (SPARUL) operations should be performed with Virtuoso transaction isolation level set to  “Read Committed”  (2), in the Virtuoso INI file and restart, as detailed at;

	http://wikis.openlinksw.com/dataspace/owiki/wiki/VirtuosoWikiWeb/ChangeVirtuosoSDefaultTransactionIsolationLevel

Or is can be set at the application level as detailed at:

	http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#ptunejdbciso

The log-enable parameter should also be set to 2 to disable transaction logging to  memory which can deplete system memory and enables row by row autocommit, as indicated at:

	http://docs.openlinksw.com/virtuoso/sparqlextensions.html#rdfsparul
	http://docs.openlinksw.com/virtuoso/fn_log_enable.html
	http://www.openlinksw.com/dataspace/dav/wiki/Main/VirtTipsAndTricksGuideTransactionLogControl

It would be interested to the the output of the “status(“”):” command run from isql on the server to see the status of the server in terms of memory “buffers" in use and whether there are any "locks/deadlocks” occurring, which also should be handled in the application.  An example for the sample deadlock handler written in Virtuoso PL can be found at:

	http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#ptunesampledeadlockhandler

You should seek to implement the equivalent of in your Java application ...

Best Regards
Hugh Williams
Professional Services
OpenLink Software, Inc.      //              http://www.openlinksw.com/
Weblog   -- http://www.openlinksw.com/blogs/
LinkedIn -- http://www.linkedin.com/company/openlink-software/
Twitter  -- http://twitter.com/OpenLink
Google+  -- http://plus.google.com/100570109519069333827/
Facebook -- http://www.facebook.com/OpenLinkSoftware
Universal Data Access, Integration, and Management Technology Providers

On 13 Dec 2012, at 20:23, Marcelo Aliquintuy wrote:

> Hi.
> I got some Transaction deadlock when make select or insert. I use Transactions and I want to know if I can debug the transaction in virtuoso to find the breaking transaction.
> In ISQL status() i can see if i have any deadlock only.
> 
> attach stacktrace below.
> 
> Best Regards!.
> 
> org.springframework.dao.ConcurrencyFailureException: StatementCallback; SQL [
> SPARQL
> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
> PREFIX cc: <http://www.toeska.cl/ns/contentcompass/ontology/v1.0#>
> 
> SELECT ?co FROM <urn:contentcompass:graph:lo> WHERE {
> 	?co cc:describes <urn:cf:98009548-3d6d-a3c1-5fa9-49733b2a6e6a> .
> 	?co rdf:type cc:Content_Object .
> 	<urn:cf:98009548-3d6d-a3c1-5fa9-49733b2a6e6a> rdf:type cc:Content_Fragment
> }
> 
> LIMIT 1]; SR...: Transaction deadlock, from SQL built-in function.; nested exception is virtuoso.jdbc4.VirtuosoException: SR...: Transaction deadlock, from SQL built-in function.
> 	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110)
> 	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
> 	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
> 	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
> 	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:406)
> 	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
> 	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:463)
> 	at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:490)
> 	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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
> 	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58)
> 	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
> 	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56)
> 	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
> 	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.aop.framework.Cglib2AopProxy$FixedChainStaticTargetInterceptor.intercept(Cglib2AopProxy.java:572)
> 	at org.springframework.jdbc.core.JdbcTemplate$$EnhancerByCGLIB$$e0f52e42.queryForList(<generated>)
> 	at cl.toeska.cc.lo.virtuoso.VirtuosoSQLContentObjectService.checkSimilarContentObject(VirtuosoSQLContentObjectService.java:498)
> 	at cl.toeska.cc.lo.virtuoso.VirtuosoSQLContentObjectService.save(VirtuosoSQLContentObjectService.java:168)
> 	at cl.toeska.cc.lo.virtuoso.VirtuosoSQLContentObjectService.save(VirtuosoSQLContentObjectService.java:54)
> 	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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
> 	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
> 	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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
> 	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58)
> 	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
> 	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56)
> 	at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
> 	at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
> 	at $Proxy146.save(Unknown Source)
> 	at cl.toeska.cc.lo.ingestor.fs_impl.XmlFileIngestor.ingest(XmlFileIngestor.java:59)
> 	at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
> 	at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
> 	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
> 	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
> 	at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
> 	at cl.toeska.cc.lo.ingestor.fs_impl.FileIngestorServiceImpl$2.call(FileIngestorServiceImpl.java:182)
> 	at cl.toeska.cc.lo.ingestor.fs_impl.FileIngestorServiceImpl$2.call(FileIngestorServiceImpl.java:1)
> 	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
> 	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
> 	at java.lang.Thread.run(Thread.java:680)
> Caused by: virtuoso.jdbc4.VirtuosoException: SR...: Transaction deadlock, from SQL built-in function.
> 	at virtuoso.jdbc4.VirtuosoResultSet.process_result(Unknown Source)
> 	at virtuoso.jdbc4.VirtuosoResultSet.<init>(Unknown Source)
> 	at virtuoso.jdbc4.VirtuosoStatement.sendQuery(Unknown Source)
> 	at virtuoso.jdbc4.VirtuosoStatement.executeQuery(Unknown Source)
> 	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
> 	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
> 	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
> 	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:440)
> 	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
> 	... 72 more
> 
> 
> 
> -- 
> Marcelo Aliquintuy
> 87433162
> 
> ------------------------------------------------------------------------------
> LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
> Remotely access PCs and mobile devices and provide instant support
> Improve your efficiency, and focus on delivering more value-add services
> Discover what IT Professionals Know. Rescue delivers
> http://p.sf.net/sfu/logmein_12329d2d_______________________________________________
> Virtuoso-users mailing list
> Vir...@li...
> https://lists.sourceforge.net/lists/listinfo/virtuoso-users