How to monitor for users with the largest number of locks in a OpenEdge database?

Getting lock table overflow errors which take down the database and need to understand who was holding the most locks and by which program(s) prior to the outage.

Lock table overflow, increase -L on server (915)

How to understand whether a lock table overflow was just a product of the application or if it can be avoided with better coding?

This article provides a program that can be run in the background which reads the lock table to report who has the most locks at any given time.

The attached program assumes only one database is connected.

The program reads the _Connect, _Lock and _Trans records into temp-tables, then prepends details about the users that are connected to the database, incluiding the number of locks to an output file named like "lockReport<database>_<date>.log", so the latest is always at the top of the file.  A new output file is created every day.  

Below is a sample of output.

12/04/2024 15:17:00.844-05:00 ConnectId:9 ConnectUser:8 (Administrator) Device:W10x64 Pid:3748 Connect Time:Wed Dec  4 14:50:41 2024 Connect Type:REMC Locks:34 
	Stack:
		173 updateLines muchActivity.p
		106 updateOrders muchActivity.p
		50 updateCustomersAndOrders muchActivity.p
		9 muchActivity.p
12/04/2024 15:17:00.844-05:00 ConnectId:6 ConnectUser:5 (Administrator) Device:W10x64 Pid:5328 Connect Time:Wed Dec  4 14:50:41 2024 Connect Type:REMC Locks:1762 
	Stack:
		128 updateOrders muchActivity.p
		50 updateCustomersAndOrders muchActivity.p
		9 muchActivity.p
12/04/2024 15:17:00.844-05:00 ConnectId:8 ConnectUser:7 (Administrator) Device:W10x64 Pid:11424 Connect Time:Wed Dec  4 14:20:41 2024 Connect Type:REMC Locks:10882 
	Stack:
		173 updateLines muchActivity.p
		106 updateOrders muchActivity.p
		50 updateCustomersAndOrders muchActivity.p
		9 muchActivity.p
================================================================================================

To use this program, simply run it on a recurring schedule using some scheduling software like cron or task scheduler while connected to a database.  Then check the log if there is a lock table overflow.

Note that this requires the Client Request Statement Caching feature to be enabled.  For more information about enabling Client Request Statement Caching follow the steps in the article below.

What is Client Database-Request Statement Caching?

References to Other Documentation:

Progress Article(s):
How to debug "Increase -L" error (915), record locking, transactions?