2 Replies Latest reply on Dec 5, 2019 10:17 AM by deivarayan1

    Database connections locked in JBoss when trying to delete several million records

    deivarayan1
    deivarayan1 Nov 22, 2019 10:34 AM

      Hi,

      I am using Jboss 7.1.6 with hibernate 5.1.17. I have a scheduler that deletes my older records every day. The problem is the transaction trying to time out after few minutes but i think the cancel transaction gets interrupted (I am not sure why) and when i check the active connections, the connection does not seems to be destroyed as well and starts creating a leak. This problem of deleting older records exists in our system for more than 2 years but the problem (connection not getting destroyed) seems to appear after we migrated jboss from 6.4 to 7.1.6.

      standlalone-full-ha.xml config:

      <recovery-environment socket-binding="txn-recovery-environment" status-socket-binding="txn-status-manager"/> 
      <coordinator-environment default-timeout="300"/>
      Error:  2019-10-27 07:50:36,043 INFO  [com.system.main.service.SchedulerBean] (EJB default - 2) Deleting older data... 
      2019-10-27 07:52:44,294 WARN  [com.arjuna.ats.arjuna] (Transaction Reaper) ARJUNA012117: TransactionReaper::check timeout for TX 0:ffff048068f6:-688a4bd4:5db53d6d:26 in state  RUN 
      2019-10-27 07:52:44,297 WARN  [com.arjuna.ats.arjuna] (Transaction Reaper Worker 0) ARJUNA012095: Abort of action id 0:ffff048068f6:-688a4bd4:5db53d6d:26 invoked while multiple threads active within it. 
      2019-11-21 04:50:00,503 WARN  [com.arjuna.ats.arjuna] (Transaction Reaper) ARJUNA012117: TransactionReaper::check timeout for TX 0:ffff048068f6:-688a4bd4:5db53d6d:c5d0dd in state  CANCEL 
      2019-11-21 04:50:01,005 WARN  [com.arjuna.ats.arjuna] (Transaction Reaper) ARJUNA012117: TransactionReaper::check timeout for TX 0:ffff048068f6:-688a4bd4:5db53d6d:c5d0dd in state  CANCEL_INTERRUPTED 
      2019-11-21 04:50:01,006 WARN  [com.arjuna.ats.arjuna] (Transaction Reaper) ARJUNA012120: TransactionReaper::check worker Thread[Transaction Reaper Worker 4,5,main] not responding to interrupt when cancelling TX 
      0:ffff048068f6:-688a4bd4:5db53d6d:c5d0dd -- worker marked as zombie and TX scheduled for mark-as-rollback

      Scheduler code:

      @Singleton public class SchedulerBean {  
      @Schedule(hour = "4", minute = "45") 
      public void deleteOlderData() { service.deleteOlderData(); } }  

      @Stateless       public class ServiceBean implements Service { 
      @PersistenceContext 
      private EntityManager entityManager; 

      @Override       public int deleteOlderData() {  
       int numberDeleted = -1; 
      try { 
      Query query = entityManager.createNamedQuery("Data.deleteOlder"); 
      numberDeleted       = query.executeUpdate(); 
      } catch (Exception e) { 
      log      .error("Error while trying to delete older data", e); 
      context      .setRollbackOnly(); 
      } 
      return numberDeleted; } 
      }

      I checked the connection/transaction is still active from Jboss CLI as well as in the database.

      [root@server client]$ java -jar jboss-cli-client.jar --connect 
      [standalone@localhost:9990 /] /subsystem=datasources/data-source=db/statistics=pool/:read-resource(recursive=false,proxies=false,include-runtime=true,include-defaults=true) 
      { "outcome" => "success", "result" => { 
      "ActiveCount" => 1, "AvailableCount" => 19, "AverageBlockingTime" => 51L, "AverageCreationTime" => 560L, "AverageGetTime" => 17L, 
      "AveragePoolTime" => 3342L, "AverageUsageTime" => 2076L, "BlockingFailureCount" => 0, "CreatedCount" => 105, "DestroyedCount" => 104, "IdleCount" => 0, **"InUseCount" => 1,** }

      This inUseCount never gets destroyed unless we kill the transaction in the database. We are trying to make our sql query better but could someone please provide some insights how to have the connection destroyed ? Thanks