Wiggle out of SQL Server database timeout in transactional WCF operations

Question

I would like to pick your brain on how I can wiggle out of an SQL Server timeout scenario. For demonstration purposes, I have two (self describing) WCF services, UserService and AuthorizationService.

I have an operation CreateUser() in UserService and AssignRole() in AuthorizationService. Both operations either start a transaction if none exists or join a transaction if one was initiated by the caller.

Service Contracts

[ServiceContract(Namespace="http://tempuri.org/")]
public interface IUserService
{
    [OperationContract]
    void CreateUser(UserData user);
}

[ServiceContract(Namespace="http://tempuri.org/")]
public interface IAuthorizationService
{
    [OperationContract]
    void AssignRole(UserData user, RoleData role);
}

Proxies:

public sealed class UserProxy : ClientBase<IUserService>, IUserService
{
    void IUserService.CreateUser(UserData user)
    {
        Channel.CreateUser(user);
    }
}

public sealed class AuthorizationProxy : ClientBase<IAuthorizationService>, IAuthorizationService
{
    void IAuthorizationService.AssignRole(UserData user, RoleData role)
    {
        Channel.AssignRole(user, role);
    }
}

Implementation:

public sealed class UserService : IUserService
{
    [OperationBehavior(TransactionScopeRequired=true)]
    void IUserService.CreateUser(UserData user)
    {
        // code to create user in database

        IAuthorizationService authProxy = new AuthorizationProxy();
        // call AuthorizationService.AssignRole to assign user a default role
        authProxy.AssignRole(user, /* default role */);
    }
}

public sealed class AuthorizationService: IAuthorizationService
{
    [OperationBehavior(TransactionScopeRequired=true)]
    void IAuthorizationService.AssignRole(UserData user, RoleData role)
    {
        // code to confirm that user exists (database read) => YOU GET A TIMEOUT HERE, since user created in UserService.CreateUser() has not been committed
    }
}

Description:

The UserService.CreateUser() operation needs to call AuthorizationService.AssignRole() to assign a default role as a user is being created. The idea is that the success of UserService.CreateUser() operation is tied to both its successful execution and that of AuthorizationService.AssignRole() to assign a default role so its very important that they both happen within a single transaction.

Note: AuthorizationService.AssignRole() is not only called from UserService.CreateUser() so it always has to perform its own check to confirm that the user exists.

Trouble: When the UserService.CreateUser() operation is called, it will initiate a transaction, and when it calls AuthorizationService.AssignRole() to assign default role, the operation will join the existing transaction and a check if user exists will cause a timeout since the transaction is not complete and the user is not committed.

I know one quick and dirty (possibly?) way to address this is to add a boolean parameter to AuthorizationService.AssignRole() such that I can use it to control whether the check for user existence should be done (i.e. bypass check if the user is in the process of being created), but I would like to hear if there are more elegant ways of getting round this timeout scenario.

Answer 1

The embarrassing part is that the "timeout" is not exactly the result of inability to read uncommitted data in a transaction. The demonstration scenario I painted above was only half the story. In the real environment, I discovered that the bindingConfiguration setting for one of the services participating in the transaction did not have the transactionFlow attribute set to true - a costly oversight. To demonstrate this, take a look at the system.serviceModel section of the configuration file in the WCF host. There are two binding, one configured to handle large messages and the other having typical/default settings. With transactionFlow="true" missing, even with all participating ServiceContracts, OperationContracts and implementations being decorated with the right attributes, a timeout is experienced as a result of the incongruency.

<system.serviceModel>
    <bindings>
        <netNamedPipeBinding>
            <binding name="defaultNNPBinding" maxReceivedMessageSize="65536" maxBufferSize="65536" maxBufferPoolSize="524288" 
                openTimeout="00:01:00" receiveTimeout="00:01:00" sendTimeout="00:01:00" closeTimeout="00:01:00" >
                    <!-- ... -->
            </binding>
            <binding name="largeMessageNNPBinding" maxReceivedMessageSize="2147483647" maxBufferSize="2147483647" maxBufferPoolSize="524288" 
                openTimeout="00:15:00" receiveTimeout="00:15:00" sendTimeout="00:15:00" closeTimeout="00:15:00" transactionFlow="true">
                    <!-- ... -->
            </binding>
        </netNamedPipeBinding>
    </bindings>
    <services>
        <service name="ServiceLib.UserService" behaviorConfiguration="LargeMessageServiceBehavior">
            <endpoint address="net.pipe://localhost/wcf/samples/UserService" binding="netNamedPipeBinding" 
                bindingConfiguration="largeMessageNNPBinding" contract="ServiceLib.Contracts.IUserService"/>
        </service>
        <service name="ServiceLib.AuthorizationService" behaviorConfiguration="DefaultServiceBehavior">
            <endpoint address="net.pipe://localhost/wcf/samples/AuthorizationService" binding="netNamedPipeBinding" 
                bindingConfiguration="defaultNNPBinding" contract="ServiceLib.Contracts.IAuthorizationService"/>
        </service>
    </services>
    <client>
        <endpoint address="net.pipe://localhost/wcf/samples/AuthorizationService" binding="netNamedPipeBinding" 
            bindingConfiguration="defaultNNPBinding" contract="ServiceLib.Contracts.IAuthorizationService" name="authorizationservice" />
    </client>
    <behaviors>
        <serviceBehaviors>
            <behavior name="LargeMessageServiceBehavior">
                <dataContractSerializer maxItemsInObjectGraph="2147483647" />
            </behavior>
            <behavior name="DefaultServiceBehavior">
            </behavior>
        </serviceBehaviors>
    </behaviors>
</system.serviceModel>

The good part is, there were important lessons to be learnt from it all. Thanks @Bob and all the others for all your contributions. For now, I will vote to close this post

Answer 2

Your example scenario seems to imply contradictory expectations. You wrote:

AuthorizationService.AssignRole() is not only called from UserService.CreateUser() so it always has to perform its own check to confirm that the user exists.

Obviously that can't be true if it also must be true for UserService.CreateUser that:

... the success of UserService.CreateUser() operation is tied to both its successful execution and that of AuthorizationService.AssignRole() to assign a default role so its very important that they both happen within a single transaction.

AuthorizationService.AssignRole can't both require a user and not require a user.

There are two basic solutions:

1. Change AuthorizationService.AssignRole so that a user is not required. Two different code branches would handle the two scenarios you've mentioned: (a) a new user is being created; (b) a user already exists.
2. Exclude from the transaction for UserService.CreateUser the call to AuthorizationService.AssignRole.

Answer 3

Shouldn't the AssignRole job simply to be to Assign a Role, not to check whether there is a valid user or not.

Another method could check if there is a valid user BEFORE AssignRole gets called, in the case of CreateUser you may not need to do that check.