Re: How to release acquired locks

In article <43spc3$pk2_at_hpcc48.corp.hp.com>, Yuk Hon Johnny Chan <jychan_at_corp.hp.com> wrote:
>Paul Berger (pberger_at_nic.wat.hookup.net) wrote:
>: In article <43kcjv$s8s_at_homer.alpha.net>, Saad Ahmad <sahmad_at_mfa.com> wrote:
>: >ctripath_at_ucs.att.com (ctripath_at_ucs.att.com) wrote:
>: >> Hi,
>: >> Is there any simple way of releasing the row locks assosiated with a table
>: >> without shutting down and then restarting the database. Is there any DBA
>: >> utility provided by oracle or SQL statements that can achieve it with the
>: >> database online.
 

>: >> We have encountered this problem as some jobs running to update the
>: >> database have to abnormally be terminated, and they hold the locks, which
>: >> prevents further action on these tables.
>: >
>: >See the v$session view; that would still have entries for those jobs.
>: >Use the ALTER SYSTEM KILL command to get rid of them.
>: >
>: >* Saad Ahmad *
>: >* McHugh Freeman & Associates, Inc. *
>: >* E-Mail: sahmad_at_mfa.com *
>
>
>: Doesn't Oracle back out deadlocks? I guess I should be re-phrasing this
>: to say "I wish Oracle7 would back out one session's transactions when a
>: deadlock is detected." Better still, a session or global deadlock detection
>: init.ora parameter that when set to a fixed expiry time backs out the
>: locked session's transactions, rolling them back to some (default) set point.
 

>: We've started to run into this too.
>
>the abnornally terminated connections resources and locks should be
>released normally but may not be due to a variety of reasons. the most
>common being that the front-end is killed while the back-end is doing
>some very time consuming SQL (like maybe update many rows in a large
>table) or PL/SQL block. the backend does not detect that the front-end
>is gone, hence it diligently runs that SQL until it is finished, at which
>time it'll try to tell the front-end that it's done, find that it's
>no longer there, and mark itself as dead and clean itself up.
>
>one common method to avoid this is to kill the backend either by issuing
>the kill command on the back-end process (assuming it is dedicated server)
>or using the alter system kill command (preferred).
>
>one last note, this is not a deadlock situation...there's another thread
>that talks extensively about blocking locks vs dead locks, so i won't
>rehash it here.
>
>jc

Actually I am talking about deadlocks. When two sessions try to update the same row in a table one session is blocked indefinitely. This is a deadlock condition not a blocked condition as you allude to!

PB Received on Fri Sep 22 1995 - 00:00:00 CEST