Announcement Announcement Module
No announcement yet.
Idle in Transaction Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Idle in Transaction

    I have converted a database heavy application to spring batch. When running on single thread every thing works fine. But when i implement partitioning and multithreading some times every thing works fine and job completes successsfully, but most of the time one or many threads hang and never comes out of infinite loop. It mostly happens when transaction is being committed and transaction performs flush. Following are the debug level logs of spring framework for successful threads and thread that hanged.

    2013-05-27 18:52:55,887 595933 DEBUG [org.springframework.jdbc.core.JdbcTemplate] (taskExecutor-4 Executing prepared SQL query
    2013-05-27 18:52:55,887 595933 DEBUG [org.springframework.jdbc.core.JdbcTemplate] (taskExecutor-4 Executing prepared SQL statement [SELECT VERSION FROM BATCH_JOB_EXECUTION WHERE JOB_EXECUTION_ID=?]
    2013-05-27 18:52:55,901 595947 DEBUG [org.springframework.orm.hibernate3.SessionFactoryU tils] (taskExecutor-4 Closing Hibernate Session
    2013-05-27 18:52:55,902 595948 DEBUG [org.springframework.batch.core.step.AbstractStep] (taskExecutor-4 Step execution complete: StepExecution: id=42, version=176, name=partitionGenerateInvoicesartition3, status=COMPLETED, exitStatus=COMPLETED, readCount=173, filterCount=0, writeCount=173 readSkipCount=0, writeSkipCount=0, processSkipCount=0, commitCount=174, rollbackCount=0

    2013-05-27 18:53:08,971 609017 DEBUG [org.springframework.jdbc.core.JdbcTemplate] (taskExecutor-3 SQL update affected 1 rows
    2013-05-27 18:53:08,971 609017 DEBUG [org.springframework.jdbc.core.JdbcTemplate] (taskExecutor-3 Executing prepared SQL query
    2013-05-27 18:53:08,971 609017 DEBUG [org.springframework.jdbc.core.JdbcTemplate] (taskExecutor-3 Executing prepared SQL statement [SELECT VERSION FROM BATCH_JOB_EXECUTION WHERE JOB_EXECUTION_ID=?]

    I have run a query to on postgresql server to find about the last query and transactions and i came to know that all the stuck threads are 'Idle in Transaction'.
    I have attached a screen shot of query results from table pg_locks and pg_stat_activity.

    I have done all what i could but in vein. I will be thankful if some one could help me.

    I can add more details about error on demand.

    Attached Files

  • #2
    I see you're using Hibernate...can you post your configuration?


    • #3
      Thanks for your helping me out.
      This application is in grails 2.0.3, and we are using grails default HibernateTransactionManager and whole mapping is through annotations. What else can i provide?


      • #4
        Sorry...your batch configuration (the job and any beans related to it).


        • #5
          Following are my spring batch infrastructure and job related beans,

          Spring batch infrastructure beans:

          transactionManager and dataSource used are grails transaction manager and datasource.

          <!-- job repository instance for persitance job details -->
          <bean id="jobRepository" class="org.springframework.batch.core.repository.s upport.JobRepositoryFactoryBean">
          <property name="transactionManager" ref="transactionManager" />
          <property name="dataSource" ref="dataSource" />
          <property name="isolationLevelForCreate" value="ISOLATION_DEFAULT" />

          <!-- job launcher bean to be used to launch a job-->
          <bean id="jobLauncher" class="org.springframework.batch.core.launch.suppo rt.SimpleJobLauncher">
          <property name="jobRepository" ref="jobRepository" />

          <!-- job explorer bean -->
          <bean id="jobExplorer" class="org.springframework.batch.core.explore.supp ort.JobExplorerFactoryBean">
          <property name="dataSource" ref="dataSource"/>
          <property name="lobHandler" ref="lobHandler"/>

          <bean id="lobHandler" class=" tLobHandler"/>

          <bean id="taskExecutor" class="org.springframework.scheduling.concurrent.T hreadPoolTaskExecutor">
          <property name="corePoolSize" value="8" />
          <property name="maxPoolSize" value="10" />

          <!-- custom exception skip policy to skip users throwing specific exceptions -->
          <bean id="skipPolicy" class="....ExceptionSkipPolicy"/>

          Job beans:
          <!-- job bean -->
          <job id="job1" xmlns="">
          <listener ref="listener" />
          <!-- first step of the job -->
          <step id="start" next="stepDecider">
          <partition step="partitionJob" partitioner="partitioner">
          <handler grid-size="6" task-executor="taskExecutor" />

          <decision id="stepDecider" decider="jobFlowDecider">
          <next on="SUCCESS" to="success" />
          <next on="FAILURE" to="failure" />
          <!-- one of the following two steps is executed depending upon exit status of previous step -->
          <step id="success">
          <tasklet ref="succeededTasklet" allow-start-if-complete="true"/>

          <step id="failure">
          <tasklet ref="failedTasklet" allow-start-if-complete="true"/>


          <!-- first step of the job -->
          <batch:step id="partitionJob">
          <batch:tasklet allow-start-if-complete="true">
          <batch:chunk reader="reader" processor="processor" writer="writer" commit-interval="100" skip-policy="skipPolicy">
          <batch:listener ref="skipListener" />

          <!-- reader of the chunk oriented step to read the ids -->
          <bean id="reader"
          class=".....Reader" scope="step">
          <property name="minValue" value="#{stepExecutionContext['minValue']}"/>
          <property name="maxValue" value="#{stepExecutionContext['maxValue']}"/>

          <!-- processor of chunk oriented step -->
          <bean id="processor"
          class="...Processor" parent="jobContextHandler" scope="step">
          <property name="parameter1" value="#{jobParameters['parametr1']}"/>
          <property name="parameter2" value="#{jobParameters['parameter2']}"/>
          <property name="parameter5" value="#{jobParameters['parameter5']}"/>
          <property name="stepExecution" value="#{stepExecution}"/>

          <!-- writer of chunk oriented step, it does nothing -->
          <bean id="processWriter" class="...ProcessWriter" parent="jobContextHandler" scope="step">
          <property name="parameter1" value="#{jobParameters['parameter1']}"/>
          <property name="parameter5" value="#{jobParameters['parameter5']}"/>
          <property name="stepExecution" value="#{stepExecution}"/>

          <!-- spring batch tasklet containing piece of that will run if process succeeds -->
          <bean id="succeededTasklet" class="....SucceededTasklet" scope="step">
          <property name="parameter1" value="#{jobParameters['parameter1']}"/>
          <property name="parameter2" value="#{jobParameters['parameter2']}"/>
          <property name="parameter3" value="#{jobParameters['parameter3']}"/>
          <property name="parameter4" value="#{jobParameters['parameter4']}"/>
          <property name="parameter5" value="#{jobParameters['parameter5']}"/>

          <!-- spring batch tasklet containing code that will run if step fails -->
          <bean id="failedTasklet" class="...FailedTasklet" scope="step">
          <property name="parameter1" value="#{jobParameters['parameter1']}"/>

          <!-- spring batch listener to set custom exit status of step -->
          <bean id="jobFlowDecider" class="....JobFlowDecider" />

          <!-- spring batch listener to do processing on skipped items -->
          <bean id="skipListener" class=".....SkipListener" parent="jobContextHandler" scope="step">
          <property name="stepExecution" value="#{stepExecution}"/>

          <!-- job listener to initialize maps and save values in database at the start and end of job -->
          <bean id="listener" class=".....JobListener"/>

          <!-- custom class to save and retrive values from job execution context -->
          <bean id="jobContextHandler" class="....JobContextHandler" abstract="true"/>

          <!-- partitions the users into groups so they could be executed in parallel -->
          <bean id="partitioner" class=".......Partitioner" scope="step"/>

          We are stuck on the same issue for last couple of weeks. Your help will be much appreciated.