Home » RDBMS Server » Networking and Gateways » select for update - how to kill old locks (11g r2, linux)
select for update - how to kill old locks [message #518356] Wed, 03 August 2011 06:40 Go to next message
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
Re: select for update - how to kill old locks [message #518358 is a reply to message #518356] Wed, 03 August 2011 07:05 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not create such a job, set the sqlnet.ora parameter named SQLNET.EXPIRE_TIME.

Regards
Michel

Re: select for update - how to kill old locks [message #518394 is a reply to message #518358] Wed, 03 August 2011 08:53 Go to previous messageGo to next message
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
Re: select for update - how to kill old locks [message #518395 is a reply to message #518394] Wed, 03 August 2011 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You set it where? On client or on server?

Regards
Michel
Re: select for update - how to kill old locks [message #518396 is a reply to message #518395] Wed, 03 August 2011 09:00 Go to previous messageGo to next message
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
Re: select for update - how to kill old locks [message #518410 is a reply to message #518396] Wed, 03 August 2011 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you restart the instance?
Did you do it with a NEW session?

Regards
Michel
Re: select for update - how to kill old locks [message #518415 is a reply to message #518410] Wed, 03 August 2011 10:37 Go to previous messageGo to next message
dyahav
Messages: 32
Registered: March 2009
Member
Yes. It still not working.
Do you have any idea how to check it?
Thanks
dyahav
Re: select for update - how to kill old locks [message #518417 is a reply to message #518415] Wed, 03 August 2011 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know, it should work.

Regards
Michel
Re: select for update - how to kill old locks [message #518611 is a reply to message #518417] Fri, 05 August 2011 01:33 Go to previous messageGo to next message
babuknb
Messages: 1703
Registered: December 2005
Location: NJ
Senior Member

Reload your listener & try

-Babu
Re: select for update - how to kill old locks [message #518796 is a reply to message #518611] Sun, 07 August 2011 01:31 Go to previous messageGo to next message
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
Re: select for update - how to kill old locks [message #518798 is a reply to message #518796] Sun, 07 August 2011 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: select for update - how to kill old locks [message #518802 is a reply to message #518798] Sun, 07 August 2011 03:40 Go to previous messageGo to next message
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
Re: select for update - how to kill old locks [message #518805 is a reply to message #518802] Sun, 07 August 2011 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If Oracle and TCP/IP did not detect there was a deconnection then the session is still alive.

Regards
Michel
Re: select for update - how to kill old locks [message #518809 is a reply to message #518805] Sun, 07 August 2011 05:37 Go to previous messageGo to next message
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
Re: select for update - how to kill old locks [message #518812 is a reply to message #518809] Sun, 07 August 2011 06:55 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: select for update - how to kill old locks [message #518830 is a reply to message #518824] Sun, 07 August 2011 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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
Re: select for update - how to kill old locks [message #518877 is a reply to message #518873] Mon, 08 August 2011 04:03 Go to previous message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Multiple home listener configuration
Next Topic: alert log error Fatal NI connect error 12170
Goto Forum:
  


Current Time: Sat Apr 19 10:06:11 CDT 2014

Total time taken to generate the page: 0.19459 seconds