sp_tables causes lock - is called after a failed delete

• francesco.giusto.sb

  Old Hand

  Points: 358

  More actions

  May 26, 2017 at 9:56 am

  #330629

  Hi everyone,

  in our company we have this issue that is plaguing us from a month.
  Looks like that the sp_tables stored_procedures is suddenly called and blocks every other transaction. 

  We could replicate the issue everytime a particular DELETE fails because of a foreign key constraint.
  This provokes an error like this and soon after an sp_tables start:

  org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.springframework.orm.hibernate4.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:163)
        at org.springframework.orm.hibernate4.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:684)
        at org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:566)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:757)
        at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:726)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:496)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
        at it.sabacom.service.tariffazione.TariffazioneService$$EnhancerBySpringCGLIB$$2c426a1b.eliminaContrattoRegola(<generated>)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:418)
        at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)
        at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1400)
        at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:1005)
        at flex.messaging.endpoints.AbstractEndpoint$$FastClassBySpringCGLIB$$1a3ef066.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.flex.core.MessageInterceptionAdvice.invoke(MessageInterceptionAdvice.java:66)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:125)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$FixedChainStaticTargetInterceptor.intercept(CglibAopProxy.java:603)
        at flex.messaging.endpoints.SecureAMFEndpoint$$EnhancerBySpringCGLIB$$34bc9a5a.serviceMessage(<generated>)
        at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:103)
        at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)
        at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:44)
        at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)
        at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:166)
        at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:291)
        at flex.messaging.endpoints.SecureAMFEndpoint$$EnhancerBySpringCGLIB$$34bc9a5a.service(<generated>)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:190)
        at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:62)
        at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3400)
        at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3630)
        at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:114)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:463)
        at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:349)
        at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
        at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56)
        at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1222)
        at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:425)
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177)
        at org.springframework.orm.hibernate4.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:558)
        ... 88 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: L'istruzione DELETE è in conflitto con il vincolo REFERENCE "e2_mov_prestazione_voci$FK_e2_mov_prestazione_voci_5". Il conflitto si è verificato nella tabella "dbo.e2_mov_prestazione_voci", column 'fk_e2_tar_contr_valore_voci' del database "xxx".
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:232)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1672)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:460)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:405)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7535)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2438)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:208)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:183)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:348)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)

  We never ever explicitly call an sp_tables in our code, so we are kinda puzzled by this.
  We could better design our code in order to avoid this kind of errors, but it doesn't always happen for every failed delete, I can't understand this.

  So, why an sp_tables sould be called after a failed insert? And why should it lock all other transactions focing us to manually issuye a KILL command?

  The db is a Sql Server 2012, the transactions are executed through Hibernate.

  Thanks

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  May 26, 2017 at 12:05 pm

  #1944372

  francesco.giusto.sb - Friday, May 26, 2017 9:56 AM

  
  We never ever explicitly call an sp_tables in our code, so we are kinda puzzled by this.
  We could better design our code in order to avoid this kind of errors, but it doesn't always happen for every failed delete, I can't understand this.

  So, why an sp_tables sould be called after a failed insert? And why should it lock all other transactions focing us to manually issuye a KILL command?

  The db is a Sql Server 2012, the transactions are executed through Hibernate.

  Thanks

  Hibernate often calls sp_tables have no idea why. Just a simple call to sp_tables is highly unlikely to be the root cause of the issues. You'd see a lot of reports about this if it were to be the issue. So something along the lines of the application, hibernate but something in your environment. 
  You would need to monitor the blocking/locking to get a better idea of what is going on. There are many resources for doing this. You could download WhoIsActive and find the lead blockers with this.
  You can capture the blocking using the blocked process report with extended events. There is a great article by Erin Stellato on sqlskills that will walk you through setting this up:
  Capture Blocking Information with Extended Events and the Blocked Process Report

  You can also just query some of the DMVs to get the information you need. Here is an example from the sys.dm_trans_lock documentation:
  SELECT 
   t1.resource_type,
   t1.resource_database_id,
   t1.resource_associated_entity_id,
   t1.request_mode,
   t1.request_session_id,
   t2.blocking_session_id
  FROM sys.dm_tran_locks as t1
  INNER JOIN sys.dm_os_waiting_tasks as t2
   ON t1.lock_owner_address = t2.resource_address;

  Sue