| Table LOCK in PeopleSoft Program [message #624261] |
Fri, 19 September 2014 13:42  |
gaganmadhu
Messages: 11
Registered: April 2008
|
Junior Member |
|
|
Team,
We need your inputs on how table locking works in Oracle.
We have requirement in PeopleSoft, that when a perticular process uses a table for updating, the same process can be run by another person and hence the same table can be used by other process at same time. We should instruct program to check if any locks are present on table (before UPDATE statement). IF yes, dont process perticular table and go to next table for processing. This way, we can avoid deadlock.
We tried with LOCK statement with EXCLUSIVE mode, but even one program goes to success (after all commits), second program which ran at same time is still in processing.
Can you please suggest ?
Thanks,
Madhu.
|
|
|
|
| Re: Table LOCK in PeopleSoft Program [message #624262 is a reply to message #624261] |
Fri, 19 September 2014 13:50  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Well, you could probably do this with SELECT...FOR UPDATE SKIP LOCKED; but I am 100% certain that your whole approach wrong. In the Oracle environment, you should never need to lock an entire table. Never! Are your programmers perhaps more familiar with a non-Oracle environment?
|
|
|
|
| Re: Table LOCK in PeopleSoft Program [message #624263 is a reply to message #624262] |
Fri, 19 September 2014 13:57 |
gaganmadhu
Messages: 11
Registered: April 2008
|
Junior Member |
|
|
Thanks John.
Let me explain scenario. Assume we have to update two coloumns in a table, column A and B. These details are stored in separate table as to what fields we need update. Example
Table Name Column
Job A
Job B
Personal A1
we have to loop throgh each row in above table and update column data to some value. If a rogram runs by person X , job table wll be locked for updating column A. Assume "commit" will be issued immediately after update statement. If at same time, program is also run by another person Y, it is going into deadlock mode (even after first program goes success, including all commits)
[Updated on: Fri, 19 September 2014 14:16] Report message to a moderator |
|
|
|
| Re: Table LOCK in PeopleSoft Program [message #624265 is a reply to message #624263] |
Fri, 19 September 2014 14:22 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Deadlock can't happen if you update the rows in the same order.
Quote:(even after first program goes success, including all commits)
This can even less happen as at this moment the first has no more locks and so can't block any other session.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
