Blocked sessions are the silent killers of enterprise applications. No one in IT notices there's a problem until the business is seriously affected.
The ProTop portal can tell you when its happening
By default, the latest version of ProTop is configured to send blocked session alerts to the ProTop portal. This can be your first clue that something is amiss in your application code:
If you hover your cursor over the alerts beginning with zBlk... you see additional information that can help zero in on an culprit you may already be hunting:
This pop-up information is brought to you by the enhancers in your alert configuration.
Here are the default alert definitions that produced the output above:
If you do not see these lines in your localized etc/alert.*.cfg file, update your version of ProTop and copy the lines above from the latest default etc/alert.cfg file into your etc/alert.*.cfg file. The ProTop agents will automatically read the changes and start monitoring for blocked sessions.
If you are using the commercial version of ProTop you can also configure the portal to forward these alerts to a list of recipients as an email or a page (and soon via any Webhook enabled application, Slack etc).
Use ProTop Real-Time to dive deeper
When you see these alerts in the portal, or via email or page, the first thing to do is open ProTop Real-Time (RT) for the site and resource listed, and use the command-key "b" to show Blocked Sessions:
This is one of the most useful screens in ProTop as it can provide the user with detailed information on who is blocking whom, on what resource they are blocked, and if there is a chain of processes all blocked by the same user. You can also easily detect deadly embraces (deadlocks) using this panel as well.
Client statement cache tells all
To get at the culprit code, you need to enable client statement cache (CSC) for the Blocker-Usr# listed in the Blocked Sessions panel, in this case "7", outlined in red above. You enable it by telling ProTop RT the user number you are interested in. See the Set Usr# (#) feature of ProTop RT for more information.
Assuming the blocking session is actively talking to the database, it will pick up the request to send its CSC and after a screen refresh or two, you will see the Blocker-StatementCache in the Blocked Sessions panel (outlined in green above). In this case we see the blocker is on line 17 of s2k_demo/blocker.p. If you have updated your alert configuration, these details will also be sent to the portal with the blocked session alerts, ready to be examined at your earliest opportunity.
If the CSC data is not being populated, use $DLC/bin/proGetStack on the Device:PID (underlined in yellow above) to generate a protrace file. The protrace file will include the full ABL call stack, including program names and line numbers. Note that the protrace is created in the process' working directory, not the -T directory. To get a protrace file you must be logged on to the same server that the client is connecting from and you must have root or Administrator privileges (for CSC data you only need access to the database with DBA privileges).
Examine your code
You should now have enough information about your blocked and blocking sessions to launch an investigation into the code. You have the table name (underlined in purple above) and a call stack or at least one procedure name and line number implicated in these blocked sessions. Now its time to have a chat with your development team and share the details!
Blocked sessions need not be a silent killer
Your users should not be frustrated waiting for data in your database to free up in order to do their jobs. ProTop can tell you when blocked and blocking sessions occur in your database application. It can even guide you right to the problem table and the specific line of code causing the problem! Got ProTop?
