Re: Recover After Crash

TJI If you user is connected for hours, there is an open transaction anyway. The cursor script won't do any additional damage.

If you are looking for an automatic way to clear locks, then consider either a dbms_job or a system startup trigger.

The startup triggers are available in 8i, they fire when the database is opened. At that point you could do a generic unlock.

The dbms_jobs approach would work in the later 7s and 8.x. Schedule a job to run automagically at some time you know there are no users, like 4am, to do the generic unlock. If your users are 7 by 24, then a comparison of one of the address fields in v$lock against the session addr field in v$session should be able to identify an orphan lock.

I also did not see your OS. THere may be complications because the user has an orphan session in Oracle-- orphan because Oracle and the OS think the user is there, but the user is really gone, because of network or workstation problems.

In article <kluev-1309002334420001_at_node9.stalker.gamma.ru>,   kluev_at_stalker.gamma.-deIete-.ru (Michael Kluev) wrote:
> In article <8poia8$6lk$17$1_at_news.t-online.com>, "Carl Rosenberger"
> <carl_at_db4o.com> wrote:
>
> > [Locking under Oracle crashsafe]
> > > update DOCUMENT set STATUS=1 where DOC_ID=xxx
> > > - to lock xxx document
> >
> > A possible solution (pseudocode):
> >
> > Statement = "select DOC_ID from DOCUMENT where DOC_ID = xxx for
 update
> > nowait"
> > Declare Cursor
> > Prepare Cursor from Statement using Transaction
> > Open Cursor
> > Fetch Cursor
> > Close Cursor
> >
> > The record should be locked until the Transaction commits,
 rollbacks or
> > disconnects.
>
> This means that transaction should be opened for the whole
> period when user works with document - it could be hours.
> I guess, this is not "good thing" to have transaction opened
> for so long. If I will modify other tables/rows of this table
> (while having transaction is opened), will my modifications
> be seen by elsewhom? I think not - this is not appropriate.
>
> --
> Michael Kluev.
>
> PS. Remove "-delete-." part from my address to reply.
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.


Sent via Deja.com http://www.deja.com/
Before you buy.