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

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

    Deivarayan Azhagappan Newbie

      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