我有一张桌子：
CREATE TABLE [dbo].[Semaphores](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NULL
) ON [PRIMARY]
我有一个存储过程：
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
我有一个简单的 Console .NET Core 应用程序：
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)
{
}
}
}
}
我在 Main 中调用 TryGetLock 方法。应用程序启动后，我转到 ssms 并调用一个简单的select * from Semaphores- 它暂停（正在执行查询），因为 .NET Core 应用程序正在使用该表（可序列化隔离级别）。在我的应用程序中的查询被取消后，我希望 ssms 中的查询完成 - 但它不是，它显然是死锁的。它仅在我关闭 .NET Core 应用程序时完成。我尝试关闭，处理数据库连接，但它没有改变任何东西。
我在这里缺少什么？
我最终摆脱了从 sql 过程中创建事务并将其放置在 c# 代码中。
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();
}
}
}
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句