Thursday, August 18, 2011

OSB using BLOB data with DBAdapter results in ora-2049

When you want to poll data in the OSB, you will use the DBAdapter. In a 4-node production cluster we saw some strange errors periodically. These errors came up in the log file of any of the application servers:

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:


No comments:

Post a Comment

Post a Comment