none
BizTalk Server 2006R2 : WCF Adapter with sql binding - Uncommitable transaction error RRS feed

  • Question

  • Hi All,
    My BizTalk application has a send port with WCF Custom adapter with sql binding.It is calling an stored procedure <QueueSP>.
    The sp looks like this :
    CREATE PROCEDURE <QueueSP>
     @InputXml XML
    AS
    BEGIN
     EXEC <SPStatusUpdate> @InputXML , 'Start'
     BEGIN TRY
         BEGIN TRANSACTION 
      --Send message to Queue
      EXEC <SPStatusUpdate> @InputXML , 'End'
      COMMIT TRANSACTION
     END TRY
     BEGIN CATCH
      ROLLBACK TRANSACTION
      EXEC <SPExceptionUpdate> @InputXML , 'Error'
     END CATCH 
    END

    Sometimes , this sp gave errors like "System.Data.SqlClient.SqlException: Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.    Server stack trace:      at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)     at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)     at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)     at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)    Exception rethrown at [0]:      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)     at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)     at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)     at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)"


    I figured out that WCF adapter has its own transaction , So i removed the Transaction and modified the sp like below :
    CREATE PROCEDURE <QueueSP>
     @InputXml XML
    AS
    BEGIN
     BEGIN TRY
         EXEC <SPStatusUpdate> @InputXML , 'Start'
      --Send message to Queue
      EXEC <SPStatusUpdate> @InputXML , 'End'
      END TRY
     BEGIN CATCH
      EXEC <SPExceptionUpdate> @InputXML , 'Error'
     END CATCH 
    END

    Now , sometimes , I get the error "System.Data.SqlClient.SqlException: Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.    Server stack trace:      at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)     at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)     at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)     at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)    Exception rethrown at [0]:      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)     at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)     at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)     at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)"

    I am not able to understand the reason...
    Please help..

    Thursday, February 24, 2011 12:42 PM

All replies

  • Does one of the procs you call have a transaction coded in the T-SQL too? I would check on those. Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Thursday, February 24, 2011 4:53 PM
    Moderator
  • This thread should be helpful.

    http://social.msdn.microsoft.com/Forums/eu/biztalkgeneral/thread/0c546f19-ed25-4e9d-b974-6660d5c30a16

    Thanks.


    Sriram N http://srirambiztalks.wordpress.com/
    Thursday, February 24, 2011 5:13 PM
  • Well,What I am thinking is :

    1. MSDTC was not enabled in the SQL SERVER. Can it be a reason for these errors , if yes, then how come I got the error only 2-3 times,,most of the times messages got processed succesfully ?

    2. The WCF Custom adapter property - USE AMBIENT TRANSACTION is set to "true" . So, I think it uses SERIALIZABLE transaction which is a table lock. Since I have not mentioned any transaction isolation level in my stored procedures..all sp's were running under SERIALIZABLE mode ??

    3. If I set the ISOLATION LEVEL to READ COMMITED in the <QueueuSP> then , the sp <SPStatusUpdate> will run under "READ COMMITED" or SERILAZABLE or default database transaction isolation level ??

    Friday, February 25, 2011 11:49 AM