Re: Recover After Crash

From: Mike Kluev <kluev_at_stalker.-deIete-.gamma.ru>
Date: Thu, 14 Sep 2000 13:38:22 +0400
Message-ID: <B5E6854E.3B79%kluev@stalker.-deIete-.gamma.ru>

In Article 8postb$cvc$13$1_at_news.t-online.com, Carl Rosenberger wrote:

>> This means that transaction should be opened for the whole
>> period when user works with document - it could be hours.

>
> Where is the problem?

Transaction is per session (connect), right? If user will open ten documents we will have to open 10 connections. Connection resources are limited, we can't afford it. More than that, see below.

>> 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.

>
> This depends on the isolation level you are using.
> "Read Committed" is what you should be using.
> In this case only committed data is seen by other people.

So they will see modifications (potentially to other tables) only after user will close document (transaction). This is not appropriate. We need to modify other tables and other people to see our modifications while document is open - all this in *one* connection.

> There is some good information on isolation levels in the Oracle
> documentation. Search for "phantom".

Thanks. I will search.

> If you consider an open transaction a bad idea:
> - Let the user insert a dataset into a timestamp table on the server.
> - Select Sequence.currval to get the pkey of this dataset.
> - Use this pkey to identify the lock on DOCUMENT
> - Write some "High I am still there" information back to the server in
> whatever time interval.
> - Some Check-Code clears Dead-Locks.
> - Store your "HiThere-ID" locally, so you can reuse it, so you don't wait
> upon your own lock upon a crash.

It is almost the same as:
- When user opens document, status is set and program periodically updates the time stamp field of the document entry in the table.

When someone tries to open document it checks not only status, but ensures that time-stamp field is not expired.

I thought about this method, and it seems to work. The problem with it is that it obviously wastes resources (program have to periodically update time-stamp field).

> You can also use Oracles proprietary Connection-ID but I am sorry not to
> have this at hand.

Is it the same as 'AUDSID' identifier of 'v$session' table? I've tried to use it, but got unsatisfactory results, see the other message to this thread. (In short, Oracle considers connection to be alive even hours after crash of workstation).

-- 
Michael Kluev.

PS. Remove "-delete-." part of my e-mail address to reply.

Received on Thu Sep 14 2000 - 04:38:22 CDT