Instance in "End Event" and running state

[devrebeleza]

Hi all.

When we try to complete step for a large number of instances, we get "deadlock errors".

ERROR: ORA-00060: deadlock detected while waiting for resource, backtrace: ORA-06512: at "FLOWS4APEX.FLOW_API_PKG", line 244
ORA-06512: at "FLOWS4APEX.FLOW_BOUNDARY_EVENTS", line 160
ORA-06512: at "FLOWS4APEX.FLOW_BOUNDARY_EVENTS", line 160
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 809
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1273
ORA-06512: at "FLOWS4APEX.FLOW_API_PKG", line 230
ORA-06512: at "FLOWS4APEX.FSOD_UAC_PKG", line 437
ORA-06512: at "FLOWS4APEX.FLOW_API_PKG", line 244
ORA-06512: at "FLOWS4APEX.FLOW_BOUNDARY_EVENTS", line 160
ORA-06512: at "FLOWS4APEX.FLOW_BOUNDARY_EVENTS", line 160
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 809
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1273
ORA-06512: at "FLOWS4APEX.FLOW_API_PKG", line 230
our pkgs->
ORA-06512: at "FLOWS4APEX.FSOD_UAC_PKG", line 411
ORA-06512: at "FSOD.FSOD_INITIATE_UAC_PKG", line 3158
ORA-06512: at "FSOD.FSOD_INITIATE_UAC_PKG", line 3219
ORA-06512: at "FSOD.FSOD_INITIATE_UAC_PKG", line 3219

To avoid this, we pass our process to jobs with restart on failure attribute. Now we have many instances in "Final Event" but still running.

Does this problem occur to you?

Any tips to avoid deadlocks in FLOWS?

Regards,
Renzo

[commi235]

Hi Renzo,

please send us your current Flows, APEX and DB version.

In addition a full stack trace when the error happens will help us analyse this better.
If possible please also include your custom packages used to control the processes.

Regards,
Moritz

[devrebeleza]

Hi Moritz,

First of all, thanks for answering.

I send you everything you asked for.

Flows Version: 22.2
Apex Version: 22.1.7
DB Version: 19c

• log trace:

log_value: FSOD_INITIATE_UAC.MOVE_USER_ACCESS_INSTANCE_PRC. For UAC with id 11467 and user access process with id 182582

log_description: 
ERROR: ORA-00060: deadlock detected while waiting for resource
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "FLOWS4APEX.FLOW_API_PKG", line 244
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE_UTIL", line 540
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 207
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1108
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1353
ORA-06512: at "FLOWS4APEX.FLOW_TASKS", line 185
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1169
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 13, backtrace: ORA-06512: at "FLOWS4APEX.FSOD_UAC_PKG", line 355
ORA-06512: at "FLOWS4APEX.FLOW_API_PKG", line 244
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE_UTIL", line 540
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 207
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1108
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1353
ORA-06512: at "FLOWS4APEX.FLOW_TASKS", line 185
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1169
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1353
ORA-06512: at "FLOWS4APEX.FLOW_GATEWAYS", line 831
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1115
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1353
ORA-06512: at "FLOWS4APEX.FLOW_TASKS", line 185
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1169
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1353
ORA-06512: at "FLOWS4APEX.FLOW_GATEWAYS", line 831
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1115
ORA-06512: at "FLOWS4APEX.FLOW_ENGINE", line 1353
ORA-06512: at "FLOWS4APEX.FLOW_API_PKG", line 230
ORA-06512: at "FLOWS4APEX.FSOD_UAC_PKG", line 289
ORA-06512: at "FLOWS4APEX.FSOD_UAC_PKG", line 324
ORA-06512: at "FSOD_INITIATE_UAC_PKG", line 3115
ORA-06512: at "FSOD_INITIATE_UAC_PKG", line 3115

• Main procedure that calls close all instances

--
PACKAGE BODY "FSOD_INITIATE_UAC_PKG"
--
PROCEDURE MOVE_USER_ACCESS_INSTANCE_PRC (
    p_uac_id             NUMBER,
    p_company_id         NUMBER,
    p_role_group_prcs_id NUMBER
  ) IS
    --
    v_user_access_prcs_id NUMBER;
    v_ua_is_completed     VARCHAR2(1);
    --
  BEGIN 
    --
    FOR ua IN (SELECT user_access_prcs_id, verified
                 FROM fsod_uac_user_roles
                WHERE role_group_prcs_id = p_role_group_prcs_id) LOOP
      --
      v_user_access_prcs_id := ua.user_access_prcs_id;
      --
      SELECT NVL(MAX('Y'), 'N') INTO v_ua_is_completed
        FROM flows4apex.flow_processes
       WHERE prcs_id = v_user_access_prcs_id
         AND UPPER(prcs_status) = 'COMPLETED';
      --
      IF v_ua_is_completed = 'N' THEN 
        --       
        -- sfp-604
          FLOWS4APEX.FSOD_UAC_PKG.MOVE_USER_ACCESS_INSTANCE_PRC(
              p_user_access_prcs_id => ua.user_access_prcs_id, 
              p_verification_code   => ua.verified,
              p_uac_id              => p_uac_id,
              p_company_id          => p_company_id
          );     
        --
      END IF;
      --
    END LOOP;
    --
  EXCEPTION
    WHEN OTHERS THEN
      --
      FWFND.FND_LOG_PKG.FND_LOG_PRC(
        p_program_code      => 'IUAP',
        p_group_id          => p_company_id,
        p_request_id        => p_uac_id,
        p_step_number       => 1,
        p_log_level         => 'ERROR',
        p_log_value         => 'FSOD_INITIATE_UAC.MOVE_USER_ACCESS_INSTANCE_PRC. For UAC with id ' || p_uac_id || ' and user access process with id ' || v_user_access_prcs_id,
        p_log_description   => 'ERROR: ' || SQLERRM || ', backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
      );
      --
      RAISE;
      --
  END MOVE_USER_ACCESS_INSTANCE_PRC;
--

Custom package attached
FSOD_UAC_PKG.zip

Best Regards
Renzo