Hello Experts,

We are constantly getting the oracle deadlock error while pushing data to oracle from wM. Could you please help me if anything can be done from webMethods to resolve this issue.

2011-05-26 06:12:21 UTC [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ADA.1.316] Cannot execute the SQL statement “{? = call TRANS_LOG.HANDLE_TRANS_LOG (?, ?, ?, ?, ?, ?, ?, ?, ?) }”. "
(72000/1001) ORA-01001: invalid cursor
ORA-06512: at “XXXXX.TRANS_LOG”, line 272
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 1
"
ORA-01001: invalid cursor
ORA-06512: at “XXXXX.TRANS_LOG”, line 272
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 1
2011-05-26 06:12:21 UTC [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service XXXX.XXXX.db.transLog:fcnHandleTransLog.
[ADA.1.316] Cannot execute the SQL statement “{? = call TRANS_LOG.HANDLE_TRANS_LOG (?, ?, ?, ?, ?, ?, ?, ?, ?) }”. "
(72000/1001) ORA-01001: invalid cursor
ORA-06512: at “XXXXX.TRANS_LOG”, line 272
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 1
"
ORA-01001: invalid cursor
ORA-06512: at “XXXX.TRANS_LOG”, line 272
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 1

Hi Sasanka,

Consult with oracle DBA team to check the table for any locks …

Cheers !!!

Hi,
can you please try these.

1)Sql query might be updating the record in which the record is getting inserted in database and is causing the deadlock.you can modify upsert operation with some logic.

2)The connection pool size might be low; you can try increasing it through IS Admin for JDBC.

3. You can set Query Time Out if query is taking longer time to execute.Refer documentation for this.

Regards,
Amol.

Deadlocks are almost always due to a coding issue. Examine the services (and possibly code in other systems) that are manipulating the DB items involved. Make sure updates to items occur in the same order in each place.