Over the years, Absolute Performance has worked with a number of clients to optimize their complex, business-critical HandySoft BizFlow® implementations. During these engagements, we’ve noticed three common early indicators of problems that can lead to poor performance or outages:
- Log Analysis
- Blocked Processes
- Queue Analysis
In our last article, we discussed the importance of persistent automated log analysis in complex BizFlow environments. We will now turn our attention to another significant BizFlow monitoring and management challenge – blocked processes.
What are Blocked Processes?
A blocked process is an issue within the database where one query (BizFlow process) is holding locks that prevent other BizFlow processes from executing. In many cases, these situations will persist until the application is restarted or the problem is remediated in another manner.
In the complex environments of BizFlow, blocked processes quickly become a major issue. A single blocked process can rapidly escalate, degrading end-user performance. If it escalates too far, the blocked processes can eventually lead to an outage that causes the business to grind to a halt.
Blocked Processes in BizFlow: A Vicious Cycle
As a Business Process Management (BPM) solution, BizFlow is often responsible for orchestrating complicated work flows for thousands of business process instances each day. When a process doesn’t run to completion, the typical response of a business user or batch application is to attempt to do the same work over again or proceed to the next work item in the list. Doing the same work over again means attempting to access the locked record. Proceeding to the next work item may require access to physically adjacent records that may call for the same lock already being held.
If the problem is a true deadlock situation, the database engine will detect the problem and eventually kill one of the processes. However, sometimes these are not true deadlock situations. Rather, they are lock contention issues that temporarily behave like deadlocks. In this case, the database will never kill off the blocker. Once processes start stacking up against this lock, the problem compounds itself as the repeated attempts to process the work eat up available database connections. If unresolved, BizFlow performance slows and eventually crashes.
Blocked Processes in SQL Server vs. Oracle
Our experience with real-world BizFlow environments has shown that blocked processes are a more severe issue in environments using Microsoft SQL Server rather than in those relying on Oracle. Why? Each database engine takes a different approach to managing lock escalation, with the SQL Server method being less capable of handling the type of locking issues that we commonly see in BizFlow. While similar problems exist in Oracle, they do not typically escalate to a level that causes an outage.
So, if your BizFlow environment relies on a SQL Server database, it is even more critical that you keep a close watch for blocked processes to avoid unplanned downtime.
Tracking Down Elusive Blocked Processes
When overlooked, blocked processes can lead to longer and more frequent outages – not to mention growing frustration among business users. However, to deal with blocked processes, you have to be able to see them.
A blocked process in BizFlow doesn’t always manifest itself as a deadlock situation, in which case it will be missed by most standard monitoring techniques. Plus, it is often difficult for IT professionals to make the correlation between a BizFlow outage and subtle happenings in the database.
One clear indicator of a blocked process situation can be found by looking for long-held blocking locks keyed off of the DBA_BLOCKERS view in Oracle or the SYSPROCESSES table in SQL Server.
If the blocker is killed by the database engine or manual intervention, the processes stacked up behind the lock will process through and the process instance whose query was killed off will go into one of several stuck flow conditions, including:
- Processes with no running activity
- Running activities with no work items
- Work items that were checked out earlier than today
One approach to identifying the root cause of a blocked process situation is to use log profiling techniques similar to those discussed in our article BizFlow Hot Button #1: Log Analysis. However, identifying process instances and their behavior by hand in a log can be as tedious, time-consuming, and inaccurate as manually tracking error entries.
Another alternative for tracking down root cause is to run the queries for detecting stuck work flows before and after you kill the blocker.
Mitigating Stuck Work Flows
Once you have identified a blocked process, you must clean it up using a two-step approach. The first step is to kill the process in the database that is holding the lock. This action will usually create an orphan or stuck work item inside of BizFlow.
The next step is to resolve the stuck work item. The options for fixing the stuck work item vary based on your company’s customized work flow. For some processes, the remediation may be as simple as someone on the IT team restarting the activity to get the work item to process forward. However, restarting an activity is not the only solution. More complex problems, such as those that involve dependencies on other systems or custom code written against BizFlow tables, may require the intervention of a business power user for resolution.
