OraFAQ Forum: Networking and Gateways » select for update - how to kill old locks

Home » RDBMS Server » Networking and Gateways » select for update - how to kill old locks (11g r2, linux)

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

select for update - how to kill old locks [message #518356]

Wed, 03 August 2011 06:40

dyahav
Messages: 32
Registered: March 2009

Member

Hi,

My code executes a Select For Update before updating a table.
In some cases the network is disconnected and it causes the lock to hang. Then, I must kill the session in order to realese this lock.
I want to do it automatically. I would like to create a job that kills session that has a lock due to Select For Update that is not alive.
Does anybody know how to do that?
Thanks
dyahav

Report message to a moderator

Re: select for update - how to kill old locks [message #518358 is a reply to message #518356]

Wed, 03 August 2011 07:05

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Do not create such a job, set the sqlnet.ora parameter named SQLNET.EXPIRE_TIME.

Regards
Michel

Report message to a moderator

Re: select for update - how to kill old locks [message #518394 is a reply to message #518358]

Wed, 03 August 2011 08:53

dyahav
Messages: 32
Registered: March 2009

Member

Hi Michel,

Thanks for the quick response.

I set the SQLNET.EXPIRE_TIME to be 5 min'.

Then, I connected to the database using sqlplus and wrote
"select * from myTab for udpdate"

Then I took the network cable off and saw that the lock is still there. I waited more than 5 min' but the lock wasn't released.

Do you have any direction how to make it work?
Thanks
dyahav

Report message to a moderator

Re: select for update - how to kill old locks [message #518395 is a reply to message #518394]

Wed, 03 August 2011 08:54

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You set it where? On client or on server?

Regards
Michel

Report message to a moderator

Re: select for update - how to kill old locks [message #518396 is a reply to message #518395]

Wed, 03 August 2011 09:00

dyahav
Messages: 32
Registered: March 2009

Member

Hi,

I set it on server: (network/admin/sqlnet.ora)

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

#SQLNET.AUTHENTICATION_SERVICES = (NONE,NTS)

SQLNET.EXPIRE_TIME=5
...
...

Thanks
dyahav

Report message to a moderator

Re: select for update - how to kill old locks [message #518410 is a reply to message #518396]

Wed, 03 August 2011 10:12

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Did you restart the instance?
Did you do it with a NEW session?

Regards
Michel

Report message to a moderator

Re: select for update - how to kill old locks [message #518415 is a reply to message #518410]

Wed, 03 August 2011 10:37

dyahav
Messages: 32
Registered: March 2009

Member

Yes. It still not working.
Do you have any idea how to check it?
Thanks
dyahav

Report message to a moderator

Re: select for update - how to kill old locks [message #518417 is a reply to message #518415]

Wed, 03 August 2011 11:07

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't know, it should work.

Regards
Michel

Report message to a moderator

Re: select for update - how to kill old locks [message #518611 is a reply to message #518417]

Fri, 05 August 2011 01:33

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

babu.knb@gmail.com

Reload your listener & try

-Babu

Report message to a moderator

Re: select for update - how to kill old locks [message #518796 is a reply to message #518611]

Sun, 07 August 2011 01:31

dyahav
Messages: 32
Registered: March 2009

Member

Hi,

It still occurs.

I will describe the scenario:

1. connect sqlplus
2. execute: select * from myTab for update;
3. Take the network cable out.
4. Verify that a lock exists (it exists).
5. Connect the network cable.
6. Wait 5 minutes (SQLNET.EXPIRE_TIME=5)
7. The lock still exists.

Only kill session releases the lock...

Do you have any suggestion how to solve it?
Thanks
dyahav

Report message to a moderator

Re: select for update - how to kill old locks [message #518798 is a reply to message #518796]

Sun, 07 August 2011 02:34

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you immediatly reconnect the cable then it might not be detected (and the session might still be alive).
Take the cable out for more than 5 minutes then you can see.

Regards
Michel

Report message to a moderator

Re: select for update - how to kill old locks [message #518802 is a reply to message #518798]

Sun, 07 August 2011 03:40

dyahav
Messages: 32
Registered: March 2009

Member

Hi,
Thanks, I will check it.
But what if scenarios, as I described in the previous post, occur?

Should I set the parameter to be very small?

Thanks a lot,
dyahav

Report message to a moderator

Re: select for update - how to kill old locks [message #518805 is a reply to message #518802]

Sun, 07 August 2011 04:10

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If Oracle and TCP/IP did not detect there was a deconnection then the session is still alive.

Regards
Michel

Report message to a moderator

Re: select for update - how to kill old locks [message #518809 is a reply to message #518805]

Sun, 07 August 2011 05:37

dyahav
Messages: 32
Registered: March 2009

Member

Hi,
As I understand there is no solution except a job that kills old locks?
Do you know how to discover such locks?
Thanks
dyahav

Report message to a moderator

Re: select for update - how to kill old locks [message #518812 is a reply to message #518809]

Sun, 07 August 2011 06:55

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Locks are part of database applications.
Locks can last long it depends on what the transaction is doing, only application knows when they have to be released.
You still didn't check that the dead connection detection does not work. For me it works.
What "such" refers to?

Regards
Michel

[Updated on: Sun, 07 August 2011 06:57]

Report message to a moderator

Re: select for update - how to kill old locks [message #518824 is a reply to message #518812]

Sun, 07 August 2011 16:28

dyahav
Messages: 32
Registered: March 2009

Member

Hi,

The scenario described in the pervious post shows a case that occurs during a normal work with SQLPLUS.
A small network interuption causes to a lock hang that only a dba intervention can solve it by killing the session.

In my production environment I have a lot of network interuptions that cause to lock hangs.

I understand that using the sqlnet expire_time parameter may solve the problem but it can't help in case of short disconnection.

Still, I didn't find a way to do it automatically.
Thanks
dyahav

Report message to a moderator

Re: select for update - how to kill old locks [message #518830 is a reply to message #518824]

Sun, 07 August 2011 23:59

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A lock does not hang please take care of the way you expose your problem.
How can you determine if it is a long transaction or a session that has been disconnected? Note that a SElECT FIR UPDATE can be very long to execute.
If Oracle does not detect there is a deconnexion, there is nothing you can do at this side.
By the way, if Oracle does not detect it this means:
1/ the session does not currently do anything (which does not mean it can't hold locks)
2/ or the session is doing something and Oracle can talk with the client

You didn't tell us what happen at CLIENT side, maybe this will give you a way to workaround the problem.
But I think you should first think to fix your network instead of trying to workaround at database end.

Regards
Michel

[Updated on: Mon, 08 August 2011 00:00]

Report message to a moderator

Re: select for update - how to kill old locks [message #518873 is a reply to message #518830]

Mon, 08 August 2011 03:45

dyahav
Messages: 32
Registered: March 2009

Member

Hi Michel,

After the reconnection the SQLPLUS is stuck and I can't execute any command.
Actually I can't use the session that locks the rows (after running the SELECT FOR UPDATE).

I thought about checking the v$lock table and looking for a session that locks and its sql statement has been executed before x seconds. Does it make sense?

The network in the production is not stable....
Thanks again
dyahav

Report message to a moderator

Re: select for update - how to kill old locks [message #518877 is a reply to message #518873]

Mon, 08 August 2011 04:03

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

After the reconnection the SQLPLUS is stuck and I can't execute any command.

So you close the previous connection?

Quote:

Actually I can't use the session that locks the rows (after running the SELECT FOR UPDATE).

Question

I don't understand what you have and what you get.
Please read OraFAQ Forum Guide and show us.

Quote:

The network in the production is not stable...

For me this should the FIRST and MAIN concern. Forget Oracle and concentrate on network, all the power you have on hands, immediatly. It is not acceptable (for a client) that the network would not be stable.

Regards
Michel

Report message to a moderator

Previous Topic:	Multiple home listener configuration
Next Topic:	alert log error Fatal NI connect error 12170

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 11 03:59:27 CDT 2025