Row Level Locking in the PeopleTools Component Processor

My apologies in advance, but this entry is a bit of a rant.  It is one of those things in PeopleSoft about which you do nothing, but it still is interesting to know because it reveals something of how it works under the covers.

In the PeopleSoft for the DBA, I commented on how the columns updated in the UPDATE statements issued by the Component Processor at save time contain only the fields that have been updated in the component.  You get different update statements depending upon what you update in the component, but the alternative is to update all the columns, possibly to the same value, and that would generate additional unnecessary redo.

PeopleSoft uses 'optimistic locking'.  That is to say that it hopes that the data in the database underlying the component hasn't changed between the operator querying data into the component and saving any changes.  At save time, the data is requeried so it can be compared with the results returned by the first query, but this time the row of data is locked by adding the FOR UPDATE clause.  The lock is released by the commit at the end of the save time processing.  You have the overhead of requerying the data, but it is likely to be in the buffer cache after the first query.  However, this avoids the risks of holding a database lock while the user is in the component. 

This is a common approach in many applications.  It is not just reasonable, but essential for scalability.  The Component Processor never holds a lock while waiting for the user to respond.  This is also essential for the Tuxedo application server.  Each server process is stateless and each service request is atomic.  Each server process maintains a persistent database connection.  A database transaction is never left uncommitted at the end of a service request.  There is no guarantee that the next request from the same user will be handled by the same application server process, and it might have to handle requests from other users in the intervening period.

However, PeopleSoft also dynamically generates the column list in the FOR UPDATE clause.  Here are two examples from the timesheet component in Time & Labor.  I have removed most of the select clause for readability.

SQL_ID: 3vdfam8g3f7ca
SELECT EMPLID, EMPL_RCD, ...
FROM PS_TL_RPTD_TIME
WHERE EMPLID=:1 AND EMPL_RCD=:2 AND DUR=TO_DATE(:3,'YYYY-MM-DD') AND SEQ_NBR=:4
FOR UPDATE OF OPRID

SQL ID: d8b5sy4bcgyuh 
SELECT EMPLID, EMPL_RCD, ...
FROM PS_TL_RPTD_TIME
WHERE EMPLID=:1 AND EMPL_RCD=:2 AND DUR=TO_DATE(:3,'YYYY-MM-DD') AND SEQ_NBR=:4
FOR UPDATE OF PUNCH_END, OPRID, RT_SOURCE, OPRID_LAST_UPDT, DTTM_MODIFIED, TL_QUANTITY

This is a totally unnecessary complexity.  Oracle (and other databases) employ row level locking.  They do not lock individual pieces of data. The row would be just as locked with one column in the FOR UPDATE clause as with many or any other.  PeopleSoft could simply have put the first column from the select clause into the FOR UPDATE clause.  Instead, we have different SQL statements with different SQL_IDs, and everything that goes with that.

However, there is nothing you can do about this, just know about it. It shows the dynamic nature of the SQL generated by the Component Processor, and reveals how it must be tracking which fields have been updated by the user and the PeopleCode in a component.