Cancel stored procedure in .NET Core

Question

I have a table:

CREATE TABLE [dbo].[Semaphores](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nchar](10) NULL
) ON [PRIMARY]

I have a stored procedure:

ALTER PROCEDURE [dbo].[SpCreateLock] @Name nvarchar(50)
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRANSACTION
        INSERT INTO Semaphores VALUES (@Name)
        Waitfor delay '00:00:10'
    COMMIT TRANSACTION
END

I have a simple Console .NET Core application:

class Program
{
    private static readonly DbContextOptions DbContextOptions;

    static Program()
    {
        var connectionString = "server=(localdb)\\ProjectsV13; database=test; trusted_connection=true";

        DbContextOptions = new DbContextOptionsBuilder()
            .UseSqlServer(connectionString)
            .Options;
    }

    static async Task Main(string[] args)
    {
        await TryGetLock();
        Console.ReadKey();
    }

    private async static Task TryGetLock()
    {
        using (var context = new DatabaseContext(DbContextOptions))
        using (var cancellationTokenSource = new CancellationTokenSource())
        {
            var cancellationToken = cancellationTokenSource.Token;

            cancellationTokenSource.CancelAfter(5000);
            cancellationToken.ThrowIfCancellationRequested();

            try
            {
                context.Database.OpenConnection();
                var result = await context.Database.ExecuteSqlCommandAsync("exec SpCreateLock qwerty", cancellationToken);
            }
            catch (OperationCanceledException e)
            {
            }
        }
    }
}

I'm calling TryGetLock method in Main. After application start I go to ssms and call a simple select * from Semaphores - it gets on hold (the query is being executed) because the .NET Core app is using that table (serializable isolation level). After the query in my application is cancelled, I would expect that the query in ssms is finished - but it's not, it's deadlocked, apparently. It only finishes when i close my .NET Core application. I tried closing, disposing the database connection but it didn't change anything.

What am I missing here?

Answer 1

I ended up getting rid of transaction creation from sql procedure and placing it in c# code.

sql:

ALTER PROCEDURE [dbo].[SpCreateLock] @Name nvarchar(50)
AS
BEGIN
    INSERT INTO Semaphores VALUES (@Name)
    Waitfor delay '00:00:10'
END

c#:

class Program
    {
        private static readonly DbContextOptions DbContextOptions;

        public static readonly LoggerFactory MyLoggerFactory = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

        static Program()
        {
            var connectionString = "server=(localdb)\\ProjectsV13; database=test; trusted_connection=true";

            DbContextOptions = new DbContextOptionsBuilder()
                .UseSqlServer(connectionString)
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(MyLoggerFactory)
                .Options;
        }

        static async Task Main(string[] args)
        {
            await TryGetLock();
            Console.ReadKey();
        }

        private async static Task TryGetLock()
        {
            IDbContextTransaction transaction = null;
            var context = new DatabaseContext(DbContextOptions);
            var cancellationTokenSource = new CancellationTokenSource();

            try
            {
                var cancellationToken = cancellationTokenSource.Token;

                cancellationTokenSource.CancelAfter(5000);
                context.Database.OpenConnection();
                transaction = context.Database.BeginTransaction(IsolationLevel.Serializable);

                var result = await context.Database.ExecuteSqlCommandAsync("exec SpCreateLock qwerty", cancellationToken);

                context.Database.CommitTransaction();
            }
            catch (Exception e)
            {
                context.Database.RollbackTransaction();
            }
            finally
            {
                cancellationTokenSource?.Dispose();
                transaction?.Dispose();
                context?.Dispose();
            }
        }
    }