Skip to main content

From Customer:   At least once a day we have a session ID that is blocking other sessions to run and slows down the system performance in Production.  Currently our DBA team is monitoring for blocking sessions longer than 45 minutes.  These seem to be sessions that go to sleep.  We need an understanding what is running in the FSM software with the user of metrix running this SQL:   When the blocking occurs it does affect all users.  Our short term approach is to delete the locked session.

'<'?query -- (@task_step_id decimal(6,0),@task_id decimal(7,0),@step_id decimal(4,0),@sequence decimal(3,0),@step_name nvarchar(28),@completed nvarchar(1),@step_type nvarchar(7),@required nvarchar(1),@completed_as_of datetime,@completed_by nvarchar(10),@mobile nvarchar(1),@user_def1 nvarchar(7),@user_def4 nvarchar(1),@user_def11 nvarchar(6),@user_def12 nvarchar(6),@created_by nvarchar(10),@created_dttm datetime,@task_unit_id decimal(5,0),@signature_required nvarchar(1))DELETE FROM task_steps WHERE task_step_id=@task_step_id AND task_id=@task_id AND step_id=@step_id AND sequence=@sequence AND step_name=@step_name AND completed=@completed AND step_type=@step_type AND required=@required AND completed_as_of=@completed_as_of AND completed_by=@completed_by AND mobile=@mobile AND user_def1=@user_def1 AND user_def4=@user_def4 AND user_def11=@user_def11 AND user_def12=@user_def12 AND created_by=@created_by AND created_dttm=@created_dttm AND task_unit_id=@task_unit_id AND signature_required=@signature_required --?'>'  (Note: 2 examples of the query have been provided but they are not identical..)

Customer just implemented Task Steps with PM Request Generation on FSM6U16.  They were not using Task Steps before this.  They verified all user_def fields in use are flagged Force Select = Y.  Work Assignment BR for PMs assign to place.person_primary relationship.  They say they are not using Team Tasks - though I have not verified existence of CUST_APP_PARAM named DISABLE_TEAM_TASKS.

How can we identify the function that is issuing the query that causes the database blocking sessions so we can stop this from happening every day?  (G2331041)

Response from R&D to Question Task:

I reviewed the FSM code and did not find any code which specifically deletes task_steps records.

I believe the most likely source is the cascading delete relationship on the task table which deleted related task_steps records when a task record is deleted.  If this is the case, then the deletion of a task record would be the source.  This is the only baseline cascading relationship where task_steps records are deleted.

I doubt they would have any custom relationships like this for task_steps but they could check for those using the following query:

select * from cust_relation_def where related_tab_name = 'TASK_STEPS'

The customer could potentially audit the TASK_STEPS delete operations.  This likely wouldn t show them the cause of the hanging delete statement directly but would allow them to see who was deleting task_steps records and when.  They could then ask those users what they were doing at the time to get an idea of where the deletes originate.

Reply


Terms & ConditionsCookie settings