Is safe to use TransactionScope in Web Applications?

Question

I have a big - and randomly problem when using TransactionScope in web application:

I have a web application, which has some methods that uses transactions:

public class ProductsService
{
    private readonly ProjectDbContext _db;
    public ProductsService(ProjectDbContext db)
    {
        _db = db;
    }

    public void DelteProduct(Guid product_Id)
    {
        Product product = _db.Products.First(p => p.Id == product_Id);

        using (TransactionScope ts = new TransactionScope())
        {
            // Delete comments
            _db.SaveChanges();

            // Delete subscriptions
            _db.SaveChanges();

            // Delete product
            _db.SaveChanges();

            ts.Complete();
        }
    }
}

Testing it locally, i had no problems.

However, when i moved the project into production, i randomly get SqlExceptionTransaction error:

System.Data.SqlClient.SqlExceptionTransaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim.
Rerun the transaction.

I thought transactions should work in a queue, and new Transactions should wait until older ones are committed, and not blocking them - even if an error occurs.

1. Why i am getting this error?
2. Should i avoid using Transactions at all in web applications?
3. I am using TransactionScope wrong?

Answer 1

I thought transactions should work in a queue, and new Transactions should wait until older ones are committed, and not blocking them - even if an error occurs.

Transactions queue, but only if possible. A deadlock occurs when two transactions wait for each other; both holding onto resources required by the other. There are tons of articles out there describing deadlocks and how they can occur in a database.

Why i am getting this error?

If you want to see what is causing the deadlock you can profile your database and analyze the deadlock graph (https://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/). As a general note, I would avoid using the TransactionScope default constructor because it sets the transaction level to serialized (http://blogs.msdn.com/b/dbrowne/archive/2010/05/21/using-new-transactionscope-considered-harmful.aspx).

From the article:

In SQL Server SERIALIZABLE transactions are rarely useful and extremely deadlock-prone. Put another way, when the default READ COMMITTED isolation level does not provide the right isolation semantics, SERIALIZABLE is rarely any better and often introduces severe blocking and deadlocking problems".

---

Should i avoid using Transactions at all in web applications?"

By all means, no. Transactions should be used to define atomic operations, things that must be committed as one should run in one transaction. There is nothing special about web-applications in that retrospect, but: stop using the default constructor.

I am using TransactionScope wrong?"

As I explained above: yes, avoid the default constructor and be specific about your transaction level; "Serialized" is never the right answer