How we solved a (ORA-02049 Timeout: Distributed Transaction Waiting for Lock) on our Apps Customized module

We have a customized Point of Sale module that is integrated with our Apps standard CRM and financial modules; we faced a serious issue on this customized module that is when users are trying to sale through this module they receive an ORA-02049 Timeout: Distributed Transaction Waiting for Lock, which require them to keep trying until they make the sale. This error used to show on daily basis on the peak hours only but we could not tell what the cause of it, simple search of the error on metalink return note 1018919.102 that advices that we should increases the distributed_lock_timeout value in the INIT.ORA file the default value was 60 seconds so we increased it to 300 seconds even though we don’t have any distributed transactions on the system all the transactions were local. We restart the issue and the problem became worse because now the end users have to wait for 5 minutes (300 seconds) before they receive the error message (ORA-02049) and because of that we had to set the value back to 60 seconds.

After that we tried to trace the error using different event trace levels but with no luck we were not able to determine what is causing the error.

We thought that it’s a database bug and oracle advised us to upgrade the database from 9.2.0.5 to 9.2.0.7 we did that but still the issue is there.

After a month of investigation/tracing and snapshot of when the problem is happing we managed to find out what was causing the problem. It was a bitmap index that was built on the table we were trying to insert data on.

When an end user was trying to sale without committing his transaction for some reason and at the same time another end user tries to sale he will receive the error message and a lock on the table happened and the error pops-up.

We solved the issue by dropping the bitmap index and creating a normal b-tree index even though the column has only three distinct values.