Home » RDBMS Server » Server Administration » Release Row Level Locking (Oracle 12c, windows 2012)
Release Row Level Locking [message #647282] Mon, 25 January 2016 06:39 Go to next message
muktha_22
Messages: 526
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 #647283 is a reply to message #647282] Mon, 25 January 2016 06:49 Go to previous messageGo to next message
Roachcoach
Messages: 1508
Registered: May 2010
Location: UK
Senior Member
Kill the session or wait for pmon to clean it up.
Re: Release Row Level Locking [message #647284 is a reply to message #647283] Mon, 25 January 2016 06:52 Go to previous messageGo to next message
muktha_22
Messages: 526
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 Go to previous messageGo to next message
Roachcoach
Messages: 1508
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 Go to previous messageGo to next message
muktha_22
Messages: 526
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 Go to previous messageGo to next message
Roachcoach
Messages: 1508
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 #647290 is a reply to message #647288] Mon, 25 January 2016 07:29 Go to previous messageGo to next message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi Roachcoach,

Yes, thanks

Muktha
Re: Release Row Level Locking [message #647401 is a reply to message #647290] Wed, 27 January 2016 04:49 Go to previous messageGo to next message
muktha_22
Messages: 526
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 Go to previous messageGo to next message
cookiemonster
Messages: 12988
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.
Re: Release Row Level Locking [message #647421 is a reply to message #647405] Wed, 27 January 2016 06:33 Go to previous message
muktha_22
Messages: 526
Registered: December 2009
Senior Member
Hi Cookiemonster,

Thanks for the update

Regards
Muktha
Previous Topic: Utilization of sessions parameters in RAC HP-UNIX server
Next Topic: AWR reports snapshots is not generating
Goto Forum:
  


Current Time: Tue Jan 16 20:12:41 CST 2018

Total time taken to generate the page: 0.02567 seconds