ORA-00054

My appologies for a long message, but we could sure use some help to resolve a nagging problem.

We are using an Oracle-7 data base on an RS6000 running AIX to support a warehouse management system. The clients are on PS2s running OS/2. There are 37 processes distributed on 22 PS2s which provide user interface and interface to conveyors, scanners, etc. Each of these processes communicates with Oracle via a DLL which we wrote using Pro*C. The system has been running in the present configuration for almost 5 months.

Because we are operating in a real time environment, every call to Oracle, which would result in an UPDATE, includes a SELECT ... FOR UPDATE NOWAIT. In this way we are able to manage delays caused by locking at the 'C' code level. In general an alarm is issued and the query is retried repeatedly. Incidently, each call through our DLL is ended with either a COMMIT or ROLLBACK as appropriate.

For the past couple of months we have been experiencing an intermittant condition in which one (or more) threads will continually retry for extended periods. In other words Oracle is returning an error code ORA00054 in response to the SELECT ... FOR UPDATE NOWAIT. The rest of the system is working normally.

The mystery is that we cannot detect any persistant lock when we view the V$LOCK table. In those instances where we can determine the row and table which our process is failing to lock, we have been able to use SQL*DBA and mimic the SELECT ... FOR UPDATE NOWAIT. When we do, we acquire the lock. Then we issue a ROLLBACK command. On its next attempt our process will proceed normally.

In some instances, the problem occurs when we call a stored function. In this case it is usually impossible to figure out the table/row which appears locked. In these cases we have to either wait out the delay, which may be minutes or hours, or bring down our whole system and restart it. The latter is obviously an undiserable solution in a busy warehouse.

Any suggestions or hints will be gratefully appreciated.

-- 
apayne_at_digital.net
Alan Payne                
2937 Fountainhead Blvd.   
Melbourne, FL 32935-8730
(407) 259-0145
ctg: (407) 725-1300 vm 2558
ctg: fax (407)951-4925