OraFAQ Forum: Server Administration » Release Row Level Locking

Home » RDBMS Server » Server Administration » Release Row Level Locking (Oracle 12c, windows 2012)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Release Row Level Locking [message #647283 is a reply to message #647282]

Mon, 25 January 2016 06:49

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Kill the session or wait for pmon to clean it up.

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

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.

Report message to a moderator

Re: Release Row Level Locking [message #647290 is a reply to message #647288]

Mon, 25 January 2016 07:29

muktha_22
Messages: 527
Registered: December 2009

Senior Member

Hi Roachcoach,

Yes, thanks

Muktha

Report message to a moderator

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:

Code: [Select all] [Show/ hide]

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

Report message to a moderator

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.

Report message to a moderator

Re: Release Row Level Locking [message #647421 is a reply to message #647405]

Wed, 27 January 2016 06:33

muktha_22
Messages: 527
Registered: December 2009

Senior Member

Hi Cookiemonster,

Thanks for the update

Regards
Muktha

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 05 11:39:11 CDT 2025