none
Problem with WCF-Custom adapter and Oracle making transactions RRS feed

  • Question

  • One of our BizTalk applications polls a Oracle table every 60 seconds for new records via the WCF-Custom Adapter.

     

    The poll-statement we’re using is: SELECT field1, field2, field3 FROM table1 WHERE status = 2 FOR UPDATE

     

    The post-poll statement we are using is: “DELETE FROM table WHERE status = 2”

    for deleting the records we’ve selected in the poll-statement.

     

    What we’re seeing right now is, that the Adapter sometimes deletes more rows than we’ve selected in the poll statement. The transaction isolation level is set to Serializable in the servicebehavior.

     

    It looks like the adapter is using 1 transaction for the poll statement en 1 transaction for the post-poll statement. We thought the adapter should handle this as 1 transaction.

     

    Are we missing some configuration here? Could you please help?

    Friday, December 18, 2009 10:05 AM

Answers

  • Hi,

    Maybe you could use a workaround. First Update the rows to be deleted. For example from 2 to 3. Then select them (the updated records)  and afterward delete the records with the value 3. So you guarantee that only the selected ones will be deleted. Also Serializable is not needed.

    Best Regards,
    Arman
    Tuesday, December 22, 2009 12:40 PM

All replies

  • Hi, Rick

    ->"What we’re seeing right now is, that the Adapter sometimes deletes more rows than we’ve selected in the poll statement"
    When this happened, could you see exception messages?
    ->"The transaction isolation level is set to Serializable in the servicebehavior."
    Did you try some other serviceBehavior:
    http://msdn.microsoft.com/en-us/library/system.transactions.isolationlevel.aspx

    Regards
    This posting is provided "AS IS" with no warranties, and confers no rights. Microsoft Online Community Support
    Monday, December 21, 2009 3:09 AM
    Moderator
  • Hi Andrew,

    Thanks for your reply.

    We don't see any exception messages at the time the Adapter deletes more rows than we've selected.
    On the other hand, when I looked in our eventlog I sometimes see this messages appearing:

    Event Type: Warning
    Event Source: BizTalk Server 2006
    Event Category: BizTalk Server 2006
    Event ID: 5740
    Date: 
    Time: 
    User:  N/A
    Computer: 
    Description:
    The adapter "WCF-Custom" raised an error message. Details "System.ObjectDisposedException: Cannot access a disposed object.
    Object name: 'TransactionScope'.
       at System.Transactions.TransactionScope.Complete()
       at System.ServiceModel.Dispatcher.TransactionRpcFacet.ThreadLeave()
       at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage7(MessageRpc& rpc)
       at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage6(MessageRpc& rpc)
       at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
       at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)
       at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)".

    I didn't try some other ServiceBehavior. I've read that the oracle adapter only supports Serializable and ReadComitted.
    http://msdn.microsoft.com/en-us/library/dd787944(BTS.10).aspx

    In that case Serializable is the only valid level for us.

    Regards

    Monday, December 21, 2009 10:44 AM
  • Hi,

    Maybe you could use a workaround. First Update the rows to be deleted. For example from 2 to 3. Then select them (the updated records)  and afterward delete the records with the value 3. So you guarantee that only the selected ones will be deleted. Also Serializable is not needed.

    Best Regards,
    Arman
    Tuesday, December 22, 2009 12:40 PM
  • Hi Arman,

    Thanks for your reply.

    The workaround you've mentioned will help us solving the problem of to many records that were deleted. 
    Still I'm very curious why the WCF-Custom adapter behaves this way? I'm really hoping to find out if we're missing a configuration somewhere.

    Best regards,

    Rick
    Tuesday, December 22, 2009 1:19 PM