SQL Server Lock Error (Distributed Transactions with WCF)

I have a problem with distributed transactions.

I am using SQL Server 2008 R2, Windows 7, .Net 4.0.

Here is what I really want to do:

• I have a buffer database (named A)
• I have another database (named B)
• I want to send data from database A to database B through the WCF web service (SOAP via HTTP, A and B are not on the same computer).
• If the data was successfully sent to database B, the data is deleted from database A

Please note that this is a simplified description of the very problem I am having right now. In my real application, I do not copy data between multiple instances of SQL Server (see Update at the end of the post).

Everything is part of the transaction, so the whole operation is atomic.

Here is what I'm trying to do sequentially (everything in a transaction):

• I read a snippet from database A (say, 50 rows).
• I am updating the rows read in step 1 (I actually set the boolean column of the Sentrows with a value true, so in the future I know that these rows are sent)
• I use the (client side) WCF web service (SOAP 1.2, wsHttpBinding), which is part of the transaction flow (blocking a synchronous call). The webservice request sends the data read in step 1
• The webservice implementation (server-side) inserts data into database B (the data contained in the webservice request)
• • If an exception is not received from the server, data with a value of Sentas trueare deleted from database A
  • FaultException, Sent true A
  • FaultExceptions ( - ), Sent true A

: (A1, A2, A3..., AN) (B1,.... BN). N N .

, . "" A B. ( ), (.. A B).

( ), B . - .

, MSDTC, ( , 3 , 3 3 , 1 , 3 ).

, B SELECT againt B. , SELECT 67, INSERT B .

( - , 1 ), . MSDTC, , "it cannot be aborted because it is not "In Doubt"".

B ? , B ?

:

// Service interface
[ServiceContract]
public interface IService
{
    [OperationContract]
    [FaultContract(typeof(MyClass))]
    [TransactionFlow(TransactionFlowOption.Allowed)]
    void SendData(DataClass data);
}

// Service implementation
[ServiceBehavior()]
public partial class Service : IService
{
    [OperationBehavior(TransactionScopeRequired = true)]
    public void SendData(DataClass data)
    {
        if (data == null)
        {
            throw new FaultException<MyClass>(new MyClass());
        }

        try
        {
            // Inserts data in database B
            using (DBContextManagement ctx = new DBContextManagement())
            {
                // Inserts data using Entity Framework
                // This will add some entities to the context
                // then call context.SaveChanges()
                ctx.InsertData(data);
            }
        }
        catch (Exception ex)
        {
            throw new FaultException<MyClass>(new MyClass());
        }
    }
}

( WCF):

<system.serviceModel>
    <services>
      <service name="XXXX.MyService">
        <endpoint binding="wsHttpBinding" bindingConfiguration="WsHttpBinding_IMyService" contract="XXXX.IMyService" />
      </service>
    </services>
    <bindings>
      <wsHttpBinding>
        <binding name="WsHttpBinding_IMyService" transactionFlow="true" allowCookies="true" >
          <readerQuotas maxDepth="32" maxArrayLength="2147483647" maxStringContentLength="2147483647" />
          <security mode="None" />
        </binding>
      </wsHttpBinding>
    </bindings>
    <behaviors>
      <serviceBehaviors>
        <behavior name="">
          <serviceTimeouts transactionTimeout="00:00:20" />
          <serviceMetadata httpGetEnabled="true" />
          <serviceDebug includeExceptionDetailInFaults="true" />
          <dataContractSerializer maxItemsInObjectGraph="2147483647" />
        </behavior>
      </serviceBehaviors>
    </behaviors>
    <serviceHostingEnvironment multipleSiteBindingsEnabled="true" />
  </system.serviceModel>

:

try
{
    using (DBContextManagement ctx = new DBContextManagement())
    {
        using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted, Timeout = new TimeSpan(0, 0, 30) }, EnterpriseServicesInteropOption.None))
        {
            // First of all, retrieves data from database A
            // Internally queries the database A through Entity Framework
            var data = ctx.GetData();

            // Mark data as sent to the server
            // Internally updates the database A through Entity Framework
            // This actually set the Sent property as true, then call
            // context.SaveChanges()
            ctx.SetDataAsSent(data);

            try
            {
                // Send data to the server
                MyServiceClient proxy = new MyServiceClient();
                MyServiceClient.SendData(data);

                // If we're here, then data has successfully been sent
                // This internally removes sent data (i.e. data with
                // property Sent as true) from database A through entity framework
                // (entities removed then context.SaveChanges)
                ctx.RemoveSentData();
            }
            catch (FaultException<MyClass> soapError)
            {
                // SOAP exception received
                // We internally remove sent data (i.e. data with
                // property Sent as true) from database A through entity framework
                // (entities removed then context.SaveChanges)
                ctx.RemoveSentData();
            }
            catch (Exception ex)
            {
                // Some logging here
                return;
            }

            ts.Complete();
        }
    }
}
catch (Exception ex)
{
    // Some logging here (removed from code)
    throw;
}

:

<system.serviceModel>
    <bindings>
      <wsHttpBinding>
        <binding name="WsHttpBinding_IMyService"
                 closeTimeout="00:01:00"
                 openTimeout="00:01:00"
                 receiveTimeout="00:10:00"
                 sendTimeout="00:01:00"
                 allowCookies="false"
                 bypassProxyOnLocal="false"
                 hostNameComparisonMode="StrongWildcard"
                 transactionFlow="true"
                 maxBufferPoolSize="2147483647"
                 maxReceivedMessageSize="2147483647"
                 messageEncoding="Text"
                 textEncoding="utf-8"
                 useDefaultWebProxy="true">

          <readerQuotas maxDepth="32"
                        maxStringContentLength="2147483647"
                        maxArrayLength="16384"
                        maxBytesPerRead="4096"
                        maxNameTableCharCount="16384" />
          <security mode="None">
            <transport clientCredentialType="None" proxyCredentialType="None" realm="" />
            <message clientCredentialType="UserName" algorithmSuite="Default" />
          </security>
        </binding>
      </wsHttpBinding>
    </bindings>

    <client>
      <endpoint address="http://localhost:8080/MyService.svc"
                binding="wsHttpBinding"
                bindingConfiguration="WsHttpBinding_IMyService"
                contract="XXX.IMyService"
                name="WsHttpBinding_IMyService" />
    </client>

  </system.serviceModel>

, :

• / ?
• B , ?
• ( / / ), (.. /, , , B )?

.

, . , SQL Server. ( ), , .

. A M1, B M2, , , , - , . , SQL Server , :

• SQL Server, ( B). , SQL Server ( Java-backoffice MySQL, PostgreSQL - )
• - SQL Server , ( IMO) ( ).
• -, .

WCF, MSMQ - - . MSMQ ( ), Windows. SOAP 1.2 , SOAP 1.2 (WS-Atomic ).

, WCF .

, (, , ), "" , , , , ( , , ).