Asynchronous method containing Stored Procedure

Question

In my controller, the[POST] methods usually call a Stored Procedure from the database. I am wondering if I am doing the right thing in order to avoid concurrency issues. Take the following method as example:

    [HttpPost]
    public async Task<IActionResult> Create(Phase phase)
    {
        int releaseId = (int)TempData["id"];
        string connectionString = Configuration["ConnectionStrings:DefaultConnection"];
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string sql = "CreatePhase";

            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                // adding parameters
                SqlParameter parameter = new SqlParameter
                {
                    ParameterName = "@Name",
                    Value = phase.Name,
                    SqlDbType = SqlDbType.VarChar,
                    Size = 50
                };
                command.Parameters.Add(parameter);

                parameter = new SqlParameter
                {
                    ParameterName = "@ReleaseId",
                    Value = releaseId,
                    SqlDbType = SqlDbType.Int
                };
                command.Parameters.Add(parameter);

                connection.Open();
                await command.ExecuteNonQueryAsync();
                connection.Close();
            }
        }

        return RedirectToAction("Index", "Phase", new { id = releaseId });
    }

Is await command.ExecuteNonQueryAsync(); the proper way to avoid concurrency? Does it have any effect? Are there any better ways of achieving this or is this good enough?

Answer 1

Async has nothing to do with concurrency. In fact, if anything, it adds to concurrency issues, since there's no defined order to operations with async, hence "asynchronous". However, in a multi-thread environment like a web app, concurrency is an issue, sync or async, because each of the 1000 or so threads can be doing the same work simultaneously.

There's only two real ways to handle concurrency: locks and concurrency tokens. Concurrency tokens are the best method, but they only work for things that are existing (not inserts). Basically, you have a column that stores the concurrency token, and each time there's an operation on that row, the token is updated. Then, before you actually perform an operation, you check that the token is the same as when you got the row in the first place (meaning nothing has changed). If so, then you can proceed with the operation. If not, then something else has modified it, and thus, it's no longer safe to go ahead. This check is done through a where clause. For example:

update Foos set Bar = 'Baz' where Id = 1 and Version = {token}

If the token in Version is no longer the same, then obviously no row will match and nothing is updated. You can then use the returned count of operations (i.e. zero) to determine that there was a concurrency failure, and recover.

This won't work with an insert, because there's nothing to key off of to determine whether or not a change has occurred. (You can't employ a where clause in an insert.) For that situation, you have no choice but to use locks to prevent anything else from happening while you're doing the operation. However, locks will absolutely kill your throughput, as it essentially forces subsequent requests to queue up and they can only be processed one at a time. As such, you should avoid these types of operations entirely when concurrency is an issue. (Inserts that don't conflict with each other are fine.)

Answer 2

First of all asynchronous is not concurrency or multithreading but improves throughput. Are you trying to solve concurrency or throughput? Throughput means many requests invoke the process without waiting for another one to complete. Concurrency means having multiple workers (threads) to run in concurrent to complete a process. Looking at your example you don't need multiple threads as the process is not processor bounded but I/O bounded so single-threaded asynchronous operation would be fine

If you want your code to be asynchronous the "await command.ExecuteNonQueryAsync();" correct as you are doing an I/O operation and it will not hold the thread until the I/O operation completes. As soon as it hits "await command.ExecuteNonQueryAsync();" it will release the thread to the caller and once the operation completes it returns controls the thread which was executing before.

Please find an excellent answer here,

What is the difference between asynchronous programming and multithreading? and an excellent article https://exceptionnotfound.net/async-await-in-asp-net-csharp-ultimate-guide/