E-PT: Locking at Database Level on PSLOCK and/or PSVERSION Tables
Last updated on DECEMBER 07, 2017
Applies to:PeopleSoft Enterprise PT PeopleTools - Version 8.49 and later
Information in this document applies to any platform.
What we are trying to do is to allow multiple processes access these tables without locking it up.
The issue you are experiencing is not a bug or a problem. It is working as desired except that any time a select for update is issued, the dbms places an exclusive lock thus preventing other sessions to perform any kind of work.
Two parameters are responsible: INITRANS which is the initial number of concurrent transactions that access one block, and MAXTRANS which is the maximum number of concurrent transactions that may access the same block.
For example, if a table is very large and only a small number of users simultaneously access the table, the chances of multiple concurrent transactions requiring access
to the same data block is low. Therefore, INITRANS can be set low, especially if space is an issue with the database.
The MAXTRANS is already 255 but INITRANS is 1 which now we are setting to 16 thus allowing a higher concurrency.
***Checked for relevance on 07-Dec-2017***
With large number of processes queuing up a significant delay is observed in processing because of locks on PSLOCK and PSVERSION tables.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms