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;