Deadlocks

When an unexpected issue occurs related to SQL query execution and there was no response from the server for a while, a message is printed in server's log, which reads:

014-01-17 00:29:12,733 WARN  [com.mchange.v2.async.ThreadPoolAsynchronousRunner] com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@5614f781 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!

Although it indicates "DEADLOCK", it does not always mean there is indeed a deadlock in the sense of a database deadlock.

MDM uses a pool of JDBC connections and this pool is monitored by another thread. This monitor ensures the pool meets all the criteria defined in the configuration such as the min pool size, and connection renewal.

The important part of the message above is "APPARENT": this message is printed by the monitor thread. All it says is that it has not been able to maintain a healthy pool for a while. This can be caused by:

• An actual database deadlock: all borrowed connections are waiting for the end of the execution, and the pool cannot be expanded.
• An MDM-to-database connection issue: the pool cannot be expanded because all connections to the database are failing due to wrong credentials or network issues.

For some RDBMS like Oracle, MDM server can sometimes cause transaction deadlocking to happen in Oracle server. To fix deadlocks, you need to investigate which specific FK column(s) are missing their index(es), and then manually create the missing FK index(es) to resolve the issues.

Here is an Oracle PL/SQL script to create the indexes for all the FKs in the user tables in an Oracle database. Note that you need to run this script as the database user, not when connected as system or dba account.

DECLARE
  -- Public variable declarations
  sql_str        VARCHAR2(5000);
  tableName      user_cons_columns.TABLE_NAME%type;
  fkColumnName   user_cons_columns.COLUMN_NAME%type;
  tableSpaceName varchar2(50);
  num NUMBER(10) NOT NULL := 0;
 
  CURSOR table_fk_cur IS
    SELECT ucc.table_name, ucc.column_name
      FROM user_constraints uc, user_cons_columns ucc
     WHERE uc.constraint_name = ucc.constraint_name
       AND uc.constraint_type = 'R'
     ORDER BY ucc.table_name, uc.constraint_name;
 
BEGIN
  DBMS_OUTPUT.ENABLE(200000);
   
  --get tablespace name
  SELECT property_value
    INTO tableSpaceName
    FROM database_properties
   WHERE property_name like 'DEFAULT_PERMANENT_TABLESPACE';
 
 
  OPEN table_fk_cur;
  LOOP
    FETCH table_fk_cur
      INTO tableName, fkColumnName;
       
    EXIT WHEN table_fk_cur%NOTFOUND;
     
    num := num + 1;
    dbms_output.put_line(tableName || '  ' || fkColumnName);
   
    sql_str := 'CREATE INDEX MISSING_FK_IDX_' || num || ' ON ' || tableName || ' ( ' || fkColumnName ||
               ' ) TABLESPACE ' || tableSpaceName;
    dbms_output.put_line(sql_str);
   
    execute immediate sql_str;
     
  END LOOP;
  CLOSE table_fk_cur;
   
  dbms_output.put_line('Totally ' || num || ' of foreign key columns are indexed.'); 
END;