Re: frontend application sometimes gets locked / backend is Oracle 8i

In article <7ljduso9ajbjfkb6k0fe1sragnmrnr00b0_at_4ax.com>,   Bernd Rosenau <brosenau_at_gss-online.de> wrote:
> Hello,
>
> our app is programmed in VB 6.0. It connects to Oracle 8i by ADO.
>
> It sometimes happens that the frontend software gets into a "locked"
> state. This happens to all frontend clients (approx. 20)
>
> It makes me think there is somewhere a lock on database tables
> leading to this problem.
>
> On which tables can i look into to see what happens ?
>
> How can i avoid such a situation ?
> In some parts, quite long transactions are used, can they
> lead to such a deadlock situation ?
>
> Many thanks in advance for your help,
>
> Regards, Bernd
>

When the front-end appears to hang you can check your instance for locked sessions. The quickest way is probably just to query v$session for all rows where 'lockwait is not null'. If you get no rows returned then database locking is not the problem. If you get rows back you can use the rowid columns in v$session to see if several users want the exact same row. If the problem is not the same row then you can look at v$lock for resource level locking problems.

The catlock script creates several views to look at locks such as dba_locks, dba_dml_locks and dba_ddl_locks. There is a wait lock graph script that Oracle provides normally in $ORACLE_HOME/rdbms/admin in utllockt.sql.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.