Troubleshooting Automation Engine not available due to DB blockage
Article ID: 206348
Updated On:
Products
Issue/Introduction
Users complain that they are unable to login to AWI.
One or multiple WPs start using a log of CPU and a full restart of the AE is necessary to restore the service.
No particular errors in the AE logs, in order to figure out what was the problem, we need to look in the WP log files for the following string that indicates queries during more than 1s to execute: U00003524
When doing so, we could find several that took more than 37 minutes to complete and it was always the same query (can be see with the next message U00003525)
20220131/064353.959 - U00003524 UCUDB: ===> Time critical DB call! OPC: 'SLCB' time: '2259:658.436.952'
20220131/102742.160 - U00003524 UCUDB: ===> Time critical DB call! OPC: 'SLCB' time: '1240:171.235.804'
20220131/102742.160 - U00003524 UCUDB: ===> Time critical DB call! OPC: 'SLCB' time: '1240:171.235.804'
The query can be seen below without the bindpars:
20220131/102742.160 - U00003525 UCUDB: ===> 'SELECT RT.* FROM RH, RT WHERE RH_Client = ? AND RH_Type = ? AND RH_AH_Idnr = ? AND RH_AH_Idnr = RT_AH_Idnr AND RH_Type = RT_Type AND RT_MsgNr = ? ORDER BY RT_Lnr DESC'
Environment
Release : 12.x
Component : AUTOMATION ENGINE
Cause
Automation Engine was not able to connect to DB due to blockage
In the example above, this was the cause of the problems as RH and RT tables are one of the most critical ones, since the DB was a SQL Server and by default lock-escalation is not turned off on the schema a row lock will be escalated to table lock causing this issue.
Resolution
For MS SQL Server:
Disable lock escalation in ALL the tables of the AE schema via ALTER TABLE name_of_the_table SET(LOCK_ESCALATION=DISABLE)
See https://msdn.microsoft.com/en-us/library/ms190273.aspx for details.
Not DB dependent:
Additionally, we also suggest you to modify in UC_SYSTEM_SETTINGS in Client 0, variable SERVER_OPTIONS set the position 11 to 1:
SERVER_OPTIONS = NNNNNNNNNN1NNNNN
That way you will see in the WP* log the associated bindpar related to the query taking more than 1s so that you DBA can check why this occurs replaying the query in a sql editor such as ms sql studio.
Additional Information
Automation Engine was not able to connect to DB due to blockage.
Kindly check with your DBA for improving DB's performance by running regular maintenance.
Feedback
