Why isn't this transaction isolated?

Question

I have a few methods - a couple of calls to SQL Server and some business logic to generate a unique value. These methods are all contained inside a parent method:

GenerateUniqueValue()
{
  //1. Call to db for last value
  //2. Business logic to create new value
  //3. Update db with new value created
}

I want the call to GenerateUniqueValue to be isolated, i.e - when two clients call it simultaneously, the second client must wait for the first one to finish.

Originally, I made my service a singleton; however, I have to anticipate future changes that may include load balancing, so I believe a singleton approach is out. Next I decided to try the transaction approach by decorating my service:

[ServiceBehavior(TransactionIsolationLevel = IsolationLevel.Serializable, TransactionTimeout = "00:00:30")]

And my GenerateUniqueValue with:

[OperationBehavior(TransactionScopeRequired = true)]

The problem is that a test of simultaneous hits to the service method results in an error:

"System.ServiceModel.ProtocolException: The transaction under which this method call was executing was asynchronously aborted."

Here is my client test code:

private static void Main(string[] args)
    {

        List<Client> clients = new List<Client>();
        for (int i = 1; i < 20; i++)
        {
            clients.Add(new Client());
        }

        foreach (var client in clients)
        {
            Thread thread = new Thread(new ThreadStart(client.GenerateUniqueValue));
            thread.Start();
        }

        Console.ReadLine();
    }

If the transaction is suppose to be isolated, why are multiple threads calling out to the method clashing?

Answer 1

Transaction is for treating multiple actions as a single atomic action. So if you want to make the second thread to wait for the first thread's completion, you have to deal with concurrency not transaction.

Try using System.ServiceModel.ServiceBehaviorAttribute.ConcurrencyMode attribute with Single or Reentrant concurrency modes. I guess that's what you are expecting.

[ServiceBehavior(ConcurrencyMode=ConcurrencyMode.Reentrant)]

I guess you got the exception because the IsolationLevel.Serializable would enable the second thread to access the volatile data, but wouldn't let it to change it. You perhapse be doing some change operation which is not permitted with this isolation level.