Re: Lock on table

On 14 Oct 1998, MarkP28665 wrote:

> From: "Luc Demanche" >>
> When a PC locked a row for update, and this PC crashed. Record get locked and
> not relesed. How can we fix this problem <<
>
> 1) Given time Oracle might recognize that the front-end process is gone, but I
> have seen if fail to do this
> 2) At the Os level kill the Oracle background process; this should hopefully
> get pmon to rollback the transaction

Ooh, that sticky terminology thing again. You probably should not kill any of the background processes, such as DBWR, PMON, SMON and LGWR. Shadow processes, also called Oracle server processes, are more or less fair game. If you must kill these at the Unix O/S level, be sure to kill them with a kill -TERM, rather than a kill -KILL. That allows the server process to handle the signal gracefully, and clean up and signal PMON before going bye-bye. Incidentally, this is pretty much what an "ALTER SYSTEM KILL SESSION..." does.

> 3) If item #1 does not happen and item #2 does not get rid of the problem then
> you pretty much have to bounce (stop and re-start) the instance.

Cycling the instance should never be necessary to solve this problem. "ALTER SYSTEM KILL SESSION..." should really do the trick. This may return "Session marked for kill," but that just means it is busy rolling back, and it will die gracefully when that is done.

> You could try using the alter system kill session command, however, I do not
> recommend its use when the front-end process is missing. I have seen pmon
> write 'unalbe to delete process' messages in the altet log for hours when this
> command was issued and one of the two processes were missing.

Well, "ALTER SYSTEM KILL SESSION..." does not cause PMON to try to delete the client process. It just cancels the current operation in the Oracle server, cleans up, and terminates the server process. The client process will just get an "End of file on communication channel" the next time it tries to talk to its server. If PMON spews the messages you mention, something is wrong with the server process, and you should dig in and investigate the state of the session, using v$session_wait, a process state dump and/or oradebug.

Good luck,

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Wed Oct 14 1998 - 19:24:36 CDT