What Causes a Lock Table Overflow?
A Lock Table overflow is caused by record locking and scoping issues related to the size of transactions. When Lock table overflow, increase -L on server (915) occurs the application code is locking more records than the current Lock Table (-L) Database startup parameter currently allows. When the number of Lock Table entries is not explicitly set as a database startup parameter the default number of locks is 8192 which is rarely sufficient for multi-user production environments.
Exceeding -L is not a usual situation, it indicates that the Application logic needs review. The most likely reasons that many records are being locked is either that:
- The transaction scope is very large.
- The records are being held SHARE-LOCK'ed after the transaction because the record scope has not ended.
- An increase in the number of database users
When the lock table is exceeded, the built in overhead in shared memory (-Mxs) is used to increase the size of the lock table. When the excess shared memory is used up, this results in a fatal error and the database shuts down: Out of free shared memory. Use -Mxs to increase. (6495).
In earlier Progress versions, the Lock Table size could continually creep up unnoticed.
In later OpenEdge versions, the lock table overflow message is raised every time the initial lock table allocation is exceeded. This provides more advanced warning that something needs to be fixed (whether it be the initial lock table size and/or the cause of the -L being exceeded) and diagnosed sooner before eventually excess shared memory is exceeded.
Resolving multi-user record locking and scoping issues (915)
Increase -L
As a database grows, logically the amount of data within tables will grow. Even the best queries are expected to use a greater and greater number of locks as the number of records grows over time, unless the query is satisfied by a unique record. If the application transaction scope is designed correctly and is not locking records needlessly, then the Size of Lock Table (-L) needs to be increased. Since OpenEdge 10.1C it can be increased online, refer to Article What is proutil <dbname> -C increaseto?
Increasing the -L database startup parameter will alleviate the problem initially. It will however result in more records remaining locked within the transaction, thereby creating a greater likelihood of other users having to wait before they can access locked records.
Increasing -Mxs to allow the Lock Table size to extend can be considered in addition to increasing the initial Lock Table size, but excess shared memory should not be relied on as it is used for overflow in other shared memory structures as well. Each lock table entry size is 72 bytes for both 64 and 32 bit.
Review Application Transaction Scope
Running the procedure in single-user mode bypasses record-locking. If the process involves a very large transaction through necessity, consider running the procedure as a single-user batch process, or as a shared-memory batch process at a time when other users are not actively updating records.
Making transactions smaller within the procedure locks fewer records and avoids having to increase the lock table (-L) startup parameter disproportionately.
One method of narrowing the scope of the transaction is to put a DO TRANSACTION: block directly around the statement that updates the record. However, when the scope of the record being updated is higher than the scope of the transaction, a SHARE-LOCK is still held for each record until the end of the record scope. The RELEASE statement explicitly releases a SHARE-LOCK’ed record after the statement which updates the database has occurred.
What is a record's scope?
The record's scope is the block in which the record was first referenced in the procedure or calling procedure. The COMPILE with the LISTING option can be used to determine a record's scope. Any reference to a record, before the first block in the program, scopes that record to the procedure block. The procedure block does not end until program ends. Therefore, record locks being held until the end of the record scope are held for the duration of the program unless these locks are explicitly released the with the RELEASE statement.
What is transaction scope?
The transaction scope is the outermost block with transaction properties that updates the database. Statements that update the database include:
UPDATE, CREATE, ASSIGN and SET.
Blocks with transaction properties include:
FOR EACH, REPEAT, PROCEDURE, DO TRANSACTION, a block that specifies EXCLUSIVE-LOCK and any block that has the TRANSACTION keyword.
FIND with an EXCLUSIVE-LOCK will also start a transaction, as the Progress compiler interprets the EXCLUSIVE-LOCK as an intent to update the database.
Online Procedures and Utilities:
1. Current Record Locks
Identifying the user holding the most locks for an extended period assists in eventually finding the transaction code needing review. Record Locks being held can be found through:
PROMON > Option 4. Record Lock Table
2. Long running Transactions
Identifying Transactions that run for extended periods of time assists in eventually isolating the transaction code needing review. Long running Transactions can be found through the VST _Trans and the _DbStatus VST reports on the Total Current and most used Locks. Refer to Articles:
3. Active transactions in the procedure
Identify Active transactions:
- Only one Active transaction is ever allowed per user session at a time, the next transaction is started in the procedure is a sub-transaction.
- When a transaction is started in one procedure and it has not ended when a sub-procedure is called, new transactions in the sub-procedure will also be sub transactions.
- The TRANSACTION function can be used to determine if there is already an active transaction in the procedure.
DO:
MESSAGE "transaction active ? " TRANSACTION.
END.
DO TRANSACTION:
MESSAGE "transaction active ? " TRANSACTION.
END.
4. COMPILE LISTING
When the procedure is locking more records than expected a COMPILE LISTING provides information on where records and transactions within the program are scoped.
COMPILE <procedure> LISTING <listfile>
5. Use 4GLTrans and 4GLTrace logging
On the Client side, in OpenEdge 10 and later, use 4GLTrans and 4GLTrace log entry types with -clientlog to report where transactions begin and end.
Example, a Client session started with:
prowin32.exe -p progs\speedtest.p -db db\sports2000 -errorstack -clientlog mylog.lg -logginglevel 2 -logentrytypes DB.Connects,4GLMessages,4GLTrans,4GLTrace
Creates a mylog.lg file with entries of the form:
15:02:18.048] P-012876 T-011196 2 4GL 4GLTRACE Run slow [Main Block - progs\speedtest.p @ 25]
15:02:18.048] P-012876 T-011196 3 4GL 4GLTRANS BEGIN TRANS 5 [SLOW progs\speedtest.p @ 13]
15:02:19.085] P-012876 T-011196 3 4GL 4GLTRANS END TRANS 5 [SLOW progs\speedtest.p @ 17]
For further details refer to Article How to determine transaction scope at runtime
6. Periodically review and evaluate database startup parameters
Schedule database startup parameters reviews, particularly when a large amount of data or large increase in database users has occurred. Either of these two situations will increase the number of locks that must be supported by the database during concurrent use.
7. Utility for monitoring users' lock count for forensic value
The below link brings you to an article with a utility that can be run proactively to monitor the locking table and periodically output various user information including the number of locks held by each user connection. This also provides stack information from the processes in order to identify which code is running. This should help to understand (after the fact) who was holding the largest number of locks, in order to understand where to start looking for potentially problematic code, or to confirm that the -L setting is presently too small.
How to monitor for users with the largest number of locks in a OpenEdge database?
