WCF Transaction Scope SQL insert table lock

Question

Hi All,

I have two services talking to two different Data-stores (i.e SQL). I am using transactionscope: eg:

using(TransactionScope scope = new TransactionScope())
{
    service1.InsertUser(user);//Insert to SQL Service 1 table User
    service2.SavePayment(payment);//Save payment SQL Service 2 table payment
    scope.Complete();
}

Service1 is locking the table (User) until the transaction is completed making subsequent transactions with that table sequential. Is there a way to overcome the lock, so can have more than one concurrent calls to the SQL service1 table while the above code is executing?

I would appreciate any input.

Thanks in Advance.

Lihnid

Answer 1

I would guess that you may have triggers on your user or payment table that update the other one.

The most likely scenario is that your save call probably does some selects, which you cannot do in the same proc where you are inserting. This causes too much locking. Determine if you need to insert with a separate call to the db, using a new transactionscope inside and suppress option, removing the select from the transaction. I know your select would have the nolock, but it appears to be ignored in sql 2005 vs older versions. I've had this same problem.

Make all your calls as simple as possible.