Inadvertent contention on Company Device Names causes database deadlocks on notification processing

Details

An issue introduced in 4.1 patch 014 caused unnecessary updates to a Company's Device Names from the node. The system was always treating the data as having been modified, resulting in unnecessary updates to the related database table. This unnecessary database activity may result in database deadlocks and system delays (especially in systems that process high volumes of events).

This issue has been resolved in 4.1 patch 18. If you have applied 4.1 patch 18 (or later), your system will not be affected by this issue. 

Technical Context

The database stores empty strings as NULL but the Java code returns an empty String for the same field resulting in the system marking the field as modified causing an unnecessary database update.

The resulting updates may lead to deadlocks in the database as the node is processing the event. These errors can be found in the node logs (/logs/AlarmPoint*.txt). For example, a stacktrace similar to the following is logged for an Oracle database:

Exception stack trace: org.springframework.dao.CannotAcquireLockException: Could not execute JDBC batch update; nested exception is org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update

Caused by: org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update

  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:87)

  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

  at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:249)

  at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)

  at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)

  at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2312)

  at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2257)

  at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2557)

  at org.hibernate.action.EntityUpdateAction.execute(EntityUpdateAction.java:92)

  at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)

  at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)

  at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)

  at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)

  at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)

  at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)

  at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:388)

  at org.springframework.orm.hibernate3.HibernateInterceptor.invoke(HibernateInterceptor.java:105)

  at com.invoqsystems.foundation.aspect.SavedFilterHibernateInterceptor.invoke(SavedFilterHibernateInterceptor.java:1)

  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

  at com.invoqsystems.foundation.aspect.MessageQueueingInterceptor.invoke(MessageQueueingInterceptor.java:20)

  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

  at com.invoqsystems.foundation.aspect.MessageConsumptionInterceptor.invoke(MessageConsumptionInterceptor.java:5)

  at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)

  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:209)

  at $Proxy60.call(Unknown Source)

  at com.invoqsystems.foundation.component.communication.jms.sink.MethodCallMessageSink.process(MethodCallMessageSink.java:50)

  at com.invoqsystems.foundation.component.communication.jms.worker.MessageProcessingWorker.process(MessageProcessingWorker.java:26)

  at com.invoqsystems.foundation.component.communication.jms.worker.AbstractWorker.iterate(AbstractWorker.java:24)

  at com.invoqsystems.foundation.component.communication.jms.worker.AbstractWorker.runUntilStop(AbstractWorker.java:46)

  at com.invoqsystems.foundation.component.communication.jms.worker.AbstractWorker.run(AbstractWorker.java:64)

  at java.lang.Thread.run(Unknown Source)

Caused by: java.sql.BatchUpdateException: ORA-00060: deadlock detected while waiting for resource 

  at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10296)

  at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:216)

  at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)

  at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)

  at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:242)

  ... 28 more

Solution

To resolve this issue:

  1. Locate the OrgDeviceName.hbm.xml file in the following locations:
    • <xMHOME>/node/assets/resources/hibernate
    • <xMHOME>/webserver/webapps/cocoon/WEB-INF/classes/resources/hibernate
    • <xMHOME>/webserver/webapps/mobilegateway/WEB-INF/classes/resources/hibernate
    • <xMHOME>/webserver/webapps/axis2/WEB-INF/classes/resources/hibernate
  2. In each file, locate the following property:
<property  
  name="domains"  
  column="DOMAINS"  
  type="string"  
  not-null="false"  
  length="4000"  
/>  
  1. In each file, modify the type attribute in each instance to read as follows:
type="com.invoqsystems.apex.dao.hibernate.types.NullProtectedStringUserType"  
  1. Save and close each file.
  2. Restart the node and web server.

xMatters Reference

XFO-4027, SUP-7138, SUP-7018, DTN-3240, JDN-4055

Originally created by Cameron Stewart

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk