| Release of Lock [message #10796] |
Tue, 17 February 2004 17:42  |
Deepa
Messages: 269
Registered: November 2000
|
Senior Member |
|
|
Hi,
I have a problem w.r.t locks .when I do an update on a particular row, the row is locked.This is correct.
Suddenly when there is a disconnection of network it is no longer connected to the database.Till this stage my row is not commited /rolled back.when I get the network connection after sometime I find the same row is locked.Can you pls. give an explanation why the row is locked and not rolled back the transaction.
Thanks and Regards,
Deepa
|
|
|
|
| Re: Release of Lock [message #10803 is a reply to message #10796] |
Wed, 18 February 2004 00:46  |
jigar
Messages: 74
Registered: July 2002
|
Member |
|
|
|
Oracle waits for the commit or rollback statement from ur session, but as network is disconnected, it is not getting it. So it will keep the rows locked. There is no way to unlock the rows except you restart the server, which will release the lock during the recovery of the transaction
|
|
|
|
| Re: Release of Lock [message #10808 is a reply to message #10803] |
Wed, 18 February 2004 05:09 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Incorrect.
When the transaction dies ,say becos of a network failure, Oracle doesnt hold locks forever. PMON is responsible for rolling back that dead transaction and releasing the locks and this happens without bouncing the Instance. Transaction recovery doesnt need Server restart!
The reason why the lock might be held even after the network disconnection is becos PMON is probably busy rolling back your transaction and it cannot release the lock until all the rows have been rolled back. The time the lock is held is dependent on the size of the transaction being rolled back.
-Thiru
|
|
|
|
| Re: Release of Lock [message #10823 is a reply to message #10808] |
Wed, 18 February 2004 17:40 |
Deepa
Messages: 269
Registered: November 2000
|
Senior Member |
|
|
Hi Thiru,
Thanks for your reply.I have updated only one record.Why PMON is taking such a long time to rollback/unlock the record.Is there any thing like setting the time to unlock the record faster .
Regards,
Deepa
|
|
|
|
| Re: Release of Lock [message #10831 is a reply to message #10823] |
Thu, 19 February 2004 02:06 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Deepa,
Did you confirm that the lock is held still ( v$lock ) ? Do you still see the transaction entry( v$transaction) ?. You can check if it has rolled back by checking v$transaction.used_ublk.
It shouldnt take this long a time to complete the rollback by PMON. You can manually kill the session if its still there and/or as a last resort kill the associated server process(you can look at v$process for it's SPID) at the OS level to terminate it , if it doesnt go away 'naturally'.
Also there were some bugs in some versions that caused locks to be held for a long time,even after the session is gone.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
