Re: Object locks - after restart

On Sep 28, 8:55 am, Naren <narendra.sha..._at_gmail.com> wrote:
> Consider following situation:
>
> 1. A application connects to Oracle database using JDBC
> 2. The application starts a JDBC transaction
> 3. One of the query takes very long (more than an hour) to complete
> and as a result the application request times out. The query was badly
> written and the number of records it processes is in millions.
>
> >From application perspective there was some issue in processing the
>
> request. So the user initiated same request again. Because the
> operation involves same records, oracle throws
> DISTRIBUTED_LOCK_TIMEOUT error because the first transaction is still
> running.
>
> The question:
> If I restart the oracle database, will oracle release the locks held
> by the first transaction? Logically I will release because the session
> that was holding the lock was killed when we restarted database. Is
> this logical understanding correct or there is something more to it?
>
> I would appreciate if you can also point me to oracle documentation
> that describes this well.
>
> Thanks,
> Naren

Bouncing the local database will probably result in the remote portion of the initial transaction being terminated and rolled back. There is a remote change that manual intervention to purge an incomplete transaction might be required though this is very doubtful.

However, you could just locate the transaction and kill it thereby avoiding the need to interrupt all other activity via the bounce.

Why does it seem that a select over a db link requires a commit after execution?
 http://www.jlcomp.demon.co.uk/faq/dblink_commit.html

 How do I find distributed queries / transactions (either issued from or connecting to this instance)? http://www.jlcomp.demon.co.uk/faq/find_dist.html

HTH -- Mark D Powell -- Received on Fri Sep 28 2007 - 10:02:05 CDT