| Release Row Level Locking [message #647282] |
Mon, 25 January 2016 06:39  |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi,
Having a doubt, kindly clarify.
1) When a user performing ROW LEVEL LOCKING for UPDATE statement for a particular record.
2) And before committing it his session got interupted or closed the application directly.
3) Now how we can unlock that ROW LEVEL LOCKING.
Regards
Muktha
|
|
|
|
|
|
| Re: Release Row Level Locking [message #647284 is a reply to message #647283] |
Mon, 25 January 2016 06:52  |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi Roachcoach,
Thanks for the update,
1) What could be the wait time by default?
2) Is there any chance the particular table owner can perform to unlock?
3) Will it make a Dead Lock?
Regards
Muktha
|
|
|
|
| Re: Release Row Level Locking [message #647285 is a reply to message #647284] |
Mon, 25 January 2016 06:58 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
I can't remember the cycles of pmon off the top of my head. I've never found myself waiting for long.
Table owner cannot do anything without the privs required to kill a session.
No, that is impossible. Deadlock doesn't mean what you think it does if you;re asking that, read up on them.
My stance is if it is urgent, kill the session, if it is not, the DB will deal with it for you. You tend to only hear about the urgent ones. What might hurt you is if that dead session had a lot of uncommitted stuff to roll back, but there is nothing you can do then but wait it out.
[Updated on: Mon, 25 January 2016 06:59] Report message to a moderator |
|
|
|
| Re: Release Row Level Locking [message #647286 is a reply to message #647285] |
Mon, 25 January 2016 07:04 |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi Roachcoach,
Thanks a lot for your help.
1) It is not urgent.
2) A apps developer required to make a option on the front end to deal such scenario, if it occur, without killing the session.
3) As our company is based on Banking sector, we cant wait the PMON to release the lock (as it may take more time)
Thanks
Muktha
|
|
|
|
| Re: Release Row Level Locking [message #647288 is a reply to message #647286] |
Mon, 25 January 2016 07:08 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
That doesn't make sense, your original post said if the session disconnects or app terminates. How is a disconnected/termination app supposed to talk to the database?
Then you need to design a process to handle this stuff. I've never seen it be a problem in my life before though. Pmon has always gotten there before the users get to us. Remember it is a row level lock, you'll only notice if someone needs the same row, which tends to be rare unless the app retries but as I say I've never had a problem with this.
Still, you could write something but you'd have to be careful.
|
|
|
|
|
|
| Re: Release Row Level Locking [message #647401 is a reply to message #647290] |
Wed, 27 January 2016 04:49 |
muktha_22
Messages: 527
Registered: December 2009
|
Senior Member |
|
|
Hi Roachcoach,
I have a some doubt,
If some one is closing the apps windows directly, and he is doing delete or update then it get automatically Rollback.
Why same cant be happened for ROW LOCK FOR UPDATE statement.
Developers are using the below code:
SELECT TRANSFERSLIPNO, A.FAXPAGECOUNT PAGECOUNT, A.IMG_NAME, TRUNC(A.ENTRY_TIME), DIS_DUP_SRNO
INTO P_TRANSFERSLIPNO , P_PAGECOUNT , P_IMG_NAME, P_ENTRY_DATE , P_DIS_DUP_SRNO
FROM TABLE1 A
WHERE TRANSFERSLIPNO = cur_rec.TRANSFERSLIPNO
AND ROW_LOCK = 2
FOR UPDATE SKIP LOCKED;
Regards
Muktha
|
|
|
|
| Re: Release Row Level Locking [message #647405 is a reply to message #647401] |
Wed, 27 January 2016 05:24 |
cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
There is absolutely no reason why select for update would be treated differently by the DB to update or delete.
Any automatic rollback has to release any locks held by the session.
If there is a difference in behavior it's the front end that's causing it - most likely by leaving the oracle session inactive if it doesn't think there's an update or delete that needs to rolled back.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
