0

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.

2
  • My understanding is that you need to await a Task before it will actually execute. I could be wrong. Commented Oct 19, 2016 at 8:10
  • I don't think lock hint on select is effective,unless you use appropriate isolation level Commented Oct 19, 2016 at 8:13

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.