How to change the Commit Lock Skip Limit for the TXE in promon?

How to modify the Commit Lock Skip Limit for the TXE?
Can the TXE locking algorithm be altered?
Can a different Commit Lock Skip Limit be set when the database is started?

In Progress 9.1E04, OpenEdge 10.0B03 and later, the TXE algorithm was changed in an effort to ensure that TXE Commit Locks will not be starved.

• The new algorithm provides for 10,000 TXE Share and TXE Update Lock Requests to be processed prior to TXE Commit Lock Requests by default.
• The TXE latch algorithm can be manually tuned via PROMON depending on the contention being experienced.
• Since OpenEdge 10.1B, a database startup parameter -TXESkipLimit was introduced to set the TXE commit lock skip limit (maximum number of SHARE/UPDATE locks that can skip the queued COMMIT locks)
• The previous TXE algorithm can be reverted by setting the TXE Commit Lock Skip Limit to (zero) 0 to favour queued COMMIT locks.
• The goal is to have this new algorithm auto or self tuned in the future.

Prior to the TXE algorithm change, typical Symptoms of the TXE algorithm not scaling based on types and volume of transactions being processed were:

• Transaction processing taking a very long time to complete. 
• Reports that normally would take a couple of minutes appear to be hanging.
• Processes continuing to get CPU but are not completing their jobs.
• User processes appear to be hanging.
• Users pressing Control+C to backout their transactions in an effort to restart jobs to see if the problem is specific to their session
• Users backing out transactions causing even worse performance for other users in the database.
• Powerful UNIX machines do not appear to be working very hard when the performance problem occurs.
• Backing out transactions cause the performance problem to become worse.
• Very high TXE Commit Waits in promon TXE Lock Activity Screen view
• Increasing -spin or setting -napmax 80 in the database startup parameters reduced latch timeouts and thereby reduced the total performance problem period.

The TXE Lock Activity Screen can be used to determine if and where the TXE contention issue queue or contention is being manifested at a given time.

A new row was added to this view when the TXE algorithm was changed, to provide more information about TXE bottlenecks called "Upgrades" locks. This counts the total requests for upgrade from SHARE to UPDATE, the rate of these (per second), and the percentage of the UPGRADE versus SHARE requests. 

Example:  Output from the TXE Lock Activity Screen indicating that tuning of the TXE Commit Lock Skip Limit to 0 may be warranted.

Activity: TXE Lock Activity

                       --- Requests ----   --------- Waits ---------
                       Total    /Sec       Total    /Sec        Pct

Record Create            546      54           5       0       0.92
Record Modify           2605     260          19       1       0.73
Record Delete            477      47         371      37      77.78 <==
Key Add                 3275     327           3       0       0.09
Key Delete              2291     229         543      54      23.70 <==
Sequence Update           24       2           0       0       0.00
Other                    273      27           9       0       3.30

Share Locks             7941     794          25       2       0.00
Update Locks            1494     149         925      92      61.91 <==
Commit Locks             446      44         457      45     102.47 <==
Excl Locks                 0       0           0       0       0.00
Total                   9881     988        1407     140      14.24

Upgrades:        Request:        383   Rate:          38     Pct:      4.82
Current  Share:            1 Update:           1  Commit:        0    Excl:     0
Current   Wait:           15   Wait:          41    Wait:       34    Wait:     0
                                 
The "Current" lines represent the different facets of the TXE latch that can be acquired:

The line beneath these facets of the TXE latches represent where there are waits for that specific facet:

When high wait percentages on Record Deletes or Key Deletes and also long queues for Update and Commit Locks, are being experienced then restoring the prior TXE locking algorithm to favour of COMMIT locks by setting the TXE Commit Lock Skip Limit to 0 could be beneficial.

While more common with Record Deletes and Key Deletes, the behavior may also exhibit with Record Creates and Key Adds.
                                 
We don't usually recommend customers to revert or change its value, but new options to the PROMON,  "Adjust TXE Options", menu called "TXE commit lock skip limit" were added to tune the new TXE algorithm and alter the TXE locking behaviour online should the need arise:
 

The default value is set to 10000.  

• Enter 3 to adjust the TXE Commit Lock Skip limit, the following prompt requests the new setting:
• Enter TXE commit lock skip limit (0 to 100000):
• Enter the new value and press return to set the maximum SHARE/UPDATE locks that can skip queued COMMIT locks.

NOTE: Once a change is made, it occurs immediately.  However, depending upon the queue or contention which had manifested itself, it may take a while to see the benefits of the queue or contention which had manifested itself, diminished. This is because the new locking algorithm once in place still needs to process requests already in queue.  An exact time as to when the Database Engine will get through the contention cannot be identified.

The TXE Commit Lock Skip Limit could be changed under the following circumstances:

1. Set it to 0 (zero) will restore to the old TXE locking algorithm.
2. If very long COMMIT or UPDATE locks queued (from the promon "Activity: TXE Lock Activity" window), and also system performance slows down, and CPU utilization spikes then this value should be set to 0.
3. The new algorithm gives Record Delete Operations lower priority. To increase the priority when say large delete operations are occurring overnight, the value should be decreased or even set to 0.
4. If increasing or decreasing the maximum SHARE/UPDATE locks that can skip the queued COMMIT locks changes need to be made in large scale order increments.  For example, by changing from 10000 to 10001 should not make any difference if this is indeed this TXE latch contention causing the performant issue

The -TXESkipLimit governs the flow of transactions between priority to shares and updates versus priority to commits.

Depending on the application design, user concurrency, and work contention, it may be necessary to adjust this "flow control" to moderate performance.

With knowledge of usage patterns of a site (application & users) it may be beneficial adjusting -TXESkipLimit before a known task that has higher dependency on commits to avoid / reduce the duration of a TXE Storm.

Disconnecting session which are involved in active transactions may increase the duration of the overall incident because the act to undo the active transaction may  also require the TXE commit latch one or more times.