Re: Thread Time Out (ORA-2049)

On Thu, 06 Jan 2005 17:32:28 GMT, "KK via OracleMonster.com" <forum_at_OracleMonster.com> wrote:

>We are facing a Time Out issue that has been puzzling us for the last couple weeks now. Here is the situation -
>
>A process (X) reads a row from table A and if the row is found, inserts a row in another Table B.
> If the row is not found, the process throws an exception. In case of exception, the process (X) rolls back the transaction.

Which Transaction? No changes have been made in the case of row not found, according to your description.

>Another process (Y) comes in immediately after the process (X) and reads the same row as process (X) and then inserts another row in the Table B.
> But, here is the puzzling thing. When the process (X) cannot read the row in table A and throws exception, the process (Y) times out while reading the row in table A.

Which indicates that the row is locked. 2 things have probably already gone wrong here ;
1) Process (x) has locked the row in table A. Not necessary. It's a simple read.
2)After having unnecessarily locked the row, process (x) does not release the lock, though you state that (X) rolls back. Ensure that it does release the lock, e.g. issu a "rollback" explicitly in your code.

>There is a a window of about 500 milliseconds after process (X) finishes where any process that comes in and tries to read the same row from table A, times out.
>
>We are using entity beans to access the database and Websphere's wsOptimisticUpdate to read/write to the tables. wsOptimisticUpdate is closely equivalent to a 'committed read'.
>
>We also checked the 'distributed_lock_timeout' parameter in Oracle and it is set to the default - 60 seconds.
>
>We have explored many options, but haven't been able to figure out what is going on. Any help would be really appreciated.
>
>--
>Message posted via http://www.oraclemonster.com

• Kenneth Koenraadt