How to wait for SqlTransaction to commit instead of throwing SqlException

Question

I am trying to develop an application of inventory management, and for the moment I am trying to do a proof of concept of adding/removing items with transactions. The site will be hosted on Windows Azure so I have to use SqlTransation to perform transactions (no DTC).

Here is my POC library code :

public static void AddQuantityByProductId(int argProductId, int argQuantity)
{
    UpdateQuantity(argProductId, argQuantity);
}

public static void ReduceQuantityByProductId(int argProductId, int argQuantity)
{
    UpdateQuantity(argProductId, argQuantity * -1);
}
private static void UpdateQuantity(int argProductId, int argQuantity)
{
        string conn = ConfigurationManager.ConnectionStrings["MyConn"].ConnectionString;
        using (SqlConnection connection = new SqlConnection(conn))
        {
            connection.Open();
            using (SqlTransaction transaction = 
                connection.BeginTransaction(IsolationLevel.Serializable))
            {
                SqlCommand command = connection.CreateCommand();
                command.Transaction = transaction;
                command.CommandTimeout = 30;

                command.CommandText = "select [Quantity] from StockItems where [Id] = '" + argProductId + "'";
                int quantity = (int)command.ExecuteScalar();
                int newQuantity = quantity + argQuantity;
                command.CommandText = "UPDATE StockItems SET [Quantity] = " + newQuantity + " WHERE [Id] = '" + argProductId + "'";
                command.ExecuteNonQuery();
                transaction.Commit();
            }
        }
}

The code that i use to test the system :

static void Main(string[] args)
{
    int productId = 6; //hard coded Id , because the line already exists
    for (int i = 0; i < 10; i++)
    {
        Parallel.For(0, 10, (j) =>
        {
            StockItemDal.AddQuantityByProductId(productId, 10);
            StockItemDal.ReduceQuantityByProductId(productId, 10);

         });
    }
 }

The exception :

Can you please help me to locate the problem ?

Answer 1

The problem is two-fold.

First, there is no way to wait out a deadlock. In SQL Server jargon, a block can be waited out. But a deadlock means that multiple queries are waiting for others to release exclusive holds on the same data* while themselves holding locks on other data that the others are all waiting for also. If all continue to wait for the others to give up and release, nothing happens and they remain locked forever. SQL Sever detects this and forces the termination and rollback of all of the queries except one.

Which leads to the second problem.

You are forcing a serializable isolation level transactions, which is the most restrictive. In your code, the need for this level of isolation is understandable because you update a counter and need the read/increment/write operation to be atomic. But the problem arises when read and write queries on the same row of data occur in two different threads in parallel.

This all leads to a rather simple solution. There is a better way to update the quantities than the method that you are using. Instead of discrete read, increment, and write Sql queries, it is possible to do it all in one statement.

        command.CommandText = "UPDATE StockItems " + 
            "SET [Quantity] = [Quantity] + " + argQuantity + 
            " WHERE [Id] = '" + argProductId + "'";
        command.ExecuteNonQuery();
        transaction.Commit();

Now, since this is a single statement, you no longer need a transaction. All of that code can go away.

* data is not very precise (on purpose). In fact, there are many different types of SQL Server locks at different levels of granularity that all work together to minimize contention for the same data and maximize performance, but this detail is not important for the answer.