Routine to lock an SQL Server Table Asynchronously

Question

I want to write a routine which should be able to lock a table for a specified amount of time and return asynchronously before any waiting.

I have written the following code

    public static void LockTable(string TableName, string connection )
        {
            var s = @"BEGIN TRAN  
                    SELECT 1 FROM " + TableName + @" WITH (TABLOCKX)
                    WAITFOR DELAY '00:00:55' 
                    ROLLBACK TRAN";

            SqlConnection myConnection = new SqlConnection(connection);

            try
            {
                myConnection.Open();
                SqlCommand myCommand = new SqlCommand(queryStr, myConnection);
                myCommand.CommandTimeout = 60;
                myCommand.ExecuteNonQueryAsync();
            }
            catch (SqlException e)
            {
                LogHelper.Error(queryStr);
                LogHelper.Error(e);
                throw e;
            }
            finally
            {
                myConnection.Close();
            }
}

But I am not getting desired result. myCommand.ExecuteNonQueryAsync(); is not locking the table while myCommand.ExecuteNonQuery(); locks the table but waits for the specified time before returning. I want it to happen asynchronously. Any help is much appreciated.

My understanding is that you need to `await` a `Task` before it will actually execute. I could be wrong. — Sam Axe, Oct 19 '16 at 08:10
I don't think lock hint on select is effective,unless you use appropriate isolation level — TheGameiswar, Oct 19 '16 at 08:13

Routine to lock an SQL Server Table Asynchronously

0 Answers0