How To Manually Lock a SQL Server Table

sql Sep 04, 2019

BEGIN TRAN  
SELECT 1 FROM [MyTable] WITH (TABLOCKX)
WAITFOR DELAY '00:01:00' 
ROLLBACK TRAN   
GO 

The above SQL statement will lock the database table MyTable for 1 minute.

I used this to test if an API would timeout and return an error after setting the database's command timeout to a set number of seconds.

For initially testing the command timeout configuration, here is the unit test I wrote. It uses the micro-ORM Dapper and Dapper's global command timeout setting.


using Dapper;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading.Tasks;
using Xunit;

namespace MyUnitTests
{
    public class DatabaseTuningTest
    {
        private const string ConnectionString = @"Data Source=.\SqlExpress;Initial Catalog=TestDatabase;Integrated Security=True";

        [Fact]
        public async Task Dapper_LongRunningCommand_ShouldTimeout()
        {
            // The default command timeout is 30 seconds for SQL Server
            SqlMapper.Settings.CommandTimeout = 5;

            var ex = await Assert.ThrowsAsync<SqlException>(async () => await RunSqlCommand());

            Assert.Contains("Execution Timeout Expired", ex.Message);
        }

        [Fact]
        public async Task Dapper_LongRunningCommand_ShouldTimeoutAfter5Seconds()
        {
            SqlMapper.Settings.CommandTimeout = 5;

            var stopwatch = new Stopwatch();

            try
            {
                await RunSqlCommand();
            }
            catch (SqlException) { }
            finally
            {
                stopwatch.Stop();
            }

            Assert.True(stopwatch.ElapsedMilliseconds < 6000);
        }

        private async Task RunSqlCommand()
        {
            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                await sqlConnection.OpenAsync();
                await sqlConnection.ExecuteAsync("WAITFOR DELAY '0:00:10'", commandType: CommandType.Text).ConfigureAwait(false);
            }
        }
    }
}

Source: https://stackoverflow.com/questions/25273157/t-sql-lock-a-table-manually-for-some-minutes

unsplash-logoMicheile Henderson

Kristina Alberto

Software engineer from Sydney. I work at Domain Group.