| 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
|
|
|
|
|
|
| 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
|
|
|
|
|
|
| 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
|
|
|
|
|
|
|
|
|
|
|
|
| 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
|
|
|
|
|
|
|
|
|
|
|
|
| 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: 68745
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
|
|
|
|
| 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: 68745
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
