Transaction End (TXE) Lock Retry Limit (-TXERetryLimit)

-TXERetryLimit n

n

The number of tables for which you want to track access statistics.

Use Transaction End (TXE) Lock Retry Limit (-TXERetryLimit) to specify the number of number of times a connection will re-try to obtain TXE lock after a 1ms nap before getting queued. The default value is 0 (no behavior change).

The Transaction End (TXE) Lock is required for crash recovery to work properly. The lock single threads "micro-transaction" type updates within the storage engine, as well as assures that database changes are properly ordered when a transaction ends. A high degree of conflict queuing this lock can severely impact performance. The cost of managing the queue and waiting on a semaphore, as well as awakening waiting requests, while keeping things "fair" carries a high performance penalty.

When the connection spins on the TXERetryLimit, rather than waiting on a semaphore, contention shifts from the TXE Lock to the TXQ latch, but the lower level TXQ latch is a much lighter-weight mechanism, and because of that, improves the overall performance of the system. Additionally, the behavior of the TXQ latch conflicts can be further tuned using the existing parameters Spin Lock Retries (-spin) and Nap Maximum (-napmax).

To change this parameter while the database is running, use PROMON or the _DbParams VST.

The best value for -TXERetryLimit is varied, but testing by Progress Software with a value of 200 showed the best performance improvement for the scenario where multiple users are creating and deleting large numbers of records in very small transactions, which previously had displayed a severe performance penalty.

You can use this parameter to support continuous operation of the database. For more information, see Change database parameters while the database is running.