Row Level Locking in the PeopleTools Component Processor
来源:互联网 发布:什么是java工厂模式 编辑:程序博客网 时间:2024/05/17 22:00
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.
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.
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: 3vdfam8g3f7caSELECT EMPLID, EMPL_RCD, ...FROM PS_TL_RPTD_TIMEWHERE EMPLID=:1 AND EMPL_RCD=:2 AND DUR=TO_DATE(:3,'YYYY-MM-DD') AND SEQ_NBR=:4FOR UPDATE OF OPRID
SQL ID: d8b5sy4bcgyuh SELECT EMPLID, EMPL_RCD, ...FROM PS_TL_RPTD_TIMEWHERE EMPLID=:1 AND EMPL_RCD=:2 AND DUR=TO_DATE(:3,'YYYY-MM-DD') AND SEQ_NBR=:4FOR 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.
- Row Level Locking in the PeopleTools Component Processor
- The PeopleSoft Component Buffer and Component Processor
- PeopleTools Tip — Cloning a Component Interface
- 打开CCS5 出现locking is not possible in the directory
- Mongodb: Db-level Locking VS Collection-level Locking
- PeopleTools Tables (Where the MetaData is Stored)
- Locking in JPA (LockModeType)
- Find the Connected Component in the Undirected Graph
- leetcode03. Find the Weak Connected Component in the Directed Graph
- Managing Access Level in the CMC
- Multi-row Editing in the ASP.NET DataGrid...
- How to select the full row in DataGrid
- Question 19: Which of the following are not pre-processor directives in C++?
- Rolling with developments in the Web component programming model
- How to remove the component VOB in Components?
- [QTP] Retrieves the value of the cell in the specified row of the parameter in the run-time Data Tab
- Using RTE enabled fields in BI Publisher with PeopleTools 8.52
- 使用Row-Level值绑定
- 07_Events
- DECLARE_DYNAMIC和IMPLEMENT_DYNAMIC宏
- 在struts中使用checkbox实现批量删除
- 量子编程详解之一: QP-nano代码大餐之状态机函数详细注释
- “Visual C++范例大全”详细目录
- Row Level Locking in the PeopleTools Component Processor
- Makefile学习教程: 写 Makefile
- Failed to push selection: Read-only file system
- RSA公钥加密算法
- Linux相关命令
- 如何在C++中动态建立二维数组--转载
- Pthread:POSIX 多线程程序设计
- 类的声明周期 一
- C语言删除字符串中的指定字符,字符串连接