none
BTS 2006 R2 WCF-SQL typed polling locks RRS feed

  • Question

  • Hello,

    I have a problem with table lock in the following scenario:

    • One table for tracking changes - bts_Changes with a column Processed (0\1).
    • Multiple triggers from real tables add rows to this table when changes occur.
    • Multiple stored procedures: xxx_hasChanges, xxx_getChanges. Latter selects changed records and sets Processed to 1.
    • Multiple receive locations which use the stored procedures for typed data polling. UseAmbientTransaction is set to true, transaction behavior is set to ReadCommited. Polling while data found is set to false, Polling interval is 10 seconds.
    • Latest SPs and CUs for both BizTalk and Adapter Pack.

    Database administrator says that my stored procedures block each other and triggers (therefore db app users) from writing to bts_Changes table. But I don't get why does wcf-sql adapter lock whole table? It should lock rows it updates, not everything..

    Any thoughts?

    Tuesday, July 10, 2012 11:57 AM

All replies

  • A WCF-based SQL adapter may lock SQL Server resources when the PolledDataAvailableStatement binding property returns a positive result but the actual polling does not return any data.

    Thanks, Raja MCTS BizTalk Server 2010, MCC If this answers your question please mark it accordingly

    Wednesday, July 11, 2012 6:09 PM
  • A WCF-based SQL adapter may lock SQL Server resources when the PolledDataAvailableStatement binding property returns a positive result but the actual polling does not return any data.

    Thanks, Raja MCTS BizTalk Server 2010, MCC If this answers your question please mark it accordingly

    It's not my case though..
    Monday, July 16, 2012 10:37 AM
  • I always use it with AmbientTransaction=false because this reduces locking. It is honestly hard to understand all of your scenario. It sounds like you have a lot of objects interacting. When I used typed polling I tried to minimize the number of records returned and was avoiding all use of triggers on the same objects that WCF-SQL was working against.

    I do not think I ever saw a table lock but I was proactively trying to minimize the process blocking anyway. I would try to avoid triggers and typed polling together, these are 2 technologies doing a similar role but do not have any inherent capability (that I know of) that will enable them to work together.

    Thanks,  


    If this answers your question, please use the "Answer" button to say so | Ben Cline


    Tuesday, July 17, 2012 9:25 PM
    Moderator