Genesys InfoMart job run failure

[zack31]

Hey all - am having issues with a particular InfoMart job running out of Config Man for GIM_ETL75 on V7.2. Basically, Job_LoadRecent continually fails although all other jobs run without issue. When I look in the GIM logs, I see the following:

11:13:14.211 Std 24301 Job_LoadRecent - error loading from table 'STG_SKILL' with data-source 'dbo@GIM_STAGING@melgim01' to table 'SKILL' with data-source 'dbo@GIM@melgim01'.  Exception: org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:333)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   at com.genesyslab.gim.etl.jobs.JobLoad$LoadGIMTable.doLoadTable(JobLoad.java:1865)
   at com.genesyslab.gim.etl.jobs.JobLoad$LoadGIMTable.run(JobLoad.java:1694)
Caused by: java.sql.BatchUpdateException: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 74) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(Unknown Source)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
   ... 9 more

11:13:15.508 Dbg 35102 Dap=GIM_Staging75DAP.  SQL= 'update dbo.STG_WORKFLOW_STATUS set STATUS='FAILED' where LAST_STATUS_UPDATE_UTC < 1222823295 AND (STATUS='RUNNING' or STATUS='WAITING')'
11:13:16.680 Std 24301 Job_LoadRecent - error loading from table 'STG_CAMPAIGN' with data-source 'dbo@GIM_STAGING@melgim01' to table 'CAMPAIGN' with data-source 'dbo@GIM@melgim01'.  Exception: org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:82)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:140)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:297)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:985)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:333)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   at com.genesyslab.gim.etl.jobs.JobLoad$LoadGIMTable.doLoadTable(JobLoad.java:1865)
   at com.genesyslab.gim.etl.jobs.JobLoad$LoadGIMTable.run(JobLoad.java:1694)
Caused by: java.sql.BatchUpdateException: com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 70) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
   at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(Unknown Source)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)

Does anyone have any ideas what I could do to sort the issue out and allow this job to run?

[oratan]

Hi, zack31
I searched Genesys Solution Search,　However, there was only the information of the release note.
Do you serve as a reference?

----------------------------------------------------------------------
Known Issues and Recommendations
When ETL jobs extract information from Stat Server or Interaction Concentrator sources on Microsoft SQL Server, there exists a possibility that the extract jobs could fail with a deadlock error in SQL Server. This is due to the locking mechanism used internally by SQL Server to isolate different processes accessing the same database resource. If an extract job attempts to read a table at the same time the source system is writing to that same table, it may produce a deadlock which SQL Server resolves by terminating one of the processes.

The extract job log would contain an error similar to the following:
Your transaction process ID was deadlocked on {name} resource with another process and has been chosen as the deadlock victim. Rerun your transaction.

If this happens, the extract job needs to be restarted. No further recovery steps are needed. (ER# 46929761)
Found In:  7.2.000.15    Fixed In: 
----------------------------------------------------------------------

oratan

[tony]

Hi Zack,

You may want to consider changing the timing(s) of when your Job_LoadRecent runs, in relation to the rest of your ETL Jobs, so that it does not overlap with any of the other ETL (Java) processes.

Tony

[kowari]

OK that isnt a deadlock, but it DOES mean that some other process is locking the tables, and in my rather hefty experience with breaking infomart, its generally not an infomar job that is the issue.

Check what other things are acessing the infomart GIM db, because it isnt clear which end of you query couldn't get the lock.  The assumption is that it is the STG_ table, but it might be the other end as well.

So, 3 things,
1) do as tony suggested, and perhaps spread out your jobs a little more (loadrecent SHOULD be able to run concurrently with extracts, but you know, its genesys infomart and it is a temperamental bugger).

2) Make sure that anyone accessing the GIM db for reporting during the day is kept OUT while you are chunking data via a "load" process

3) get to the latest version of infomart 7.5 as soon as you can.  I cannot say how much more stable 7.5 is as compared to 7.2  Just stupid amountes better.

[Garridan]

Give please. If you would be a real seeker after truth, it is necessary that at least once in your life you doubt, as far as possible, all things. Help me! Can not find sites on the: Another airway why caused, article diagnosis irritability is third-service, albuterol.. I found only this - infant albuterol inhaler. Albuterol, with the household of side domain symptoms, you can have always generally a larger route, but only an attack that occurs quicker and does longer. Albuterol, these are soluble and associated for life breath. Best regards :confused:, Garridan from Libya.

[lacroixsy]

You must update InfoMart. I had the same problem