How To Manually Lock a SQL Server Table
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
Micheile Henderson