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

On Sep 28, 8:55 am, Naren <narendra.sha...@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 --