Object locks - after restart

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Naren - 28 Sep 2007 13:55 GMT

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

Reply to this Message

Mark D Powell - 28 Sep 2007 16:02 GMT

> Consider following situation:
>
[quoted text clipped - 22 lines]
> 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 --

Reply to this Message