Caused By: oracle.tip.adapter.sa.impl.fw.ext.org.collaxa.thirdparty.apache.wsif.WSIFException: servicebus:/WSDL/EmailService/BusinessService/WijzigEmailStatus/wijzigStatus [ wijzigStatus_ptt::wijzigStatus(InputParameters,OutputParameters) ] - WSIF JCA Execute of operation 'wijzigStatus' failed due to: Stored procedure invocation error. Error while trying to prepare and execute the "schema/package/procedure" API. An error occurred while preparing and executing the "schema/package/procedure" API. Cause: java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock ORA-06512: at "schema/package", line 113 ORA-06512: at line 1 ; nested exception is: BINDING.JCA-11811 Stored procedure invocation error. Error while trying to prepare and execute the "schema/package/procedure" API. An error occurred while preparing and executing the "schema/package/procedure" API. Cause: java.sql.SQLSyntaxErrorException: ORA-02049: timeout: distributed transaction waiting for lock ORA-06512: at "schema/package", line 113 ORA-06512: at line 1
The DBAdapter was polling data from database, this was a TopLink definition based on four tables. The ProxyService that was started an in the request pipeline the message was routed , to the bussiness service. While in the response pipeline the one of these four tables was updated.
The issue is related not related in high traffic on the system. It is all about using the DBAdapter/Toplink poller mechanism. The adapter is polling data and one of the columns is a BLOB column.
If data in this column is large, the DbAdapter will take some time to process this information to the service. While the set records are locked other services wants to update this data. This will result in the Oracle error.
To resolve this issue, large (blob) data should be processed as a stream in OSB. This can easily be done by enabling the 'Content Streaming' option for this (polling) proxy service.
Remarks from development on this article:
The exception in question is just a transaction timeout. That can be avoided by
- shrinking MaxTransactionSize on inbound so that the transaction started by DbAdapter polling is shorter
- shrinking MaxRaiseSize on inbound so that any transactions started by OSB will be shorter
- increasing transaction timeout
If the problem is that inbound polling is locking a row for a long time that another process is trying to lock, that could be because inbound has configured distributed polling, where the records are locked on select. The default behaviour is to only lock records on update, which should be independent of the time it takes to process the BLOB.My remarks:
Shrinking the MaxTransactionSize to 1, could solve the issue, but if you need to handle ten-thousand or more messages with large binary data, the whole polling process will be slow. I am using the MaxTransactionSize with value 10.
References:
- DbAdapter bulk read improvement
- Oracle Service Bus: Proxy Service Message Handling
- Oracle RDBMS 11g: Error Codes
