EF Core设置隔离级别仍无法锁表，请求表级锁解决方案

我需要在插入数据前做状态检查，但发现就算设置了隔离级别，EF Core也不会对表加读锁。我需要锁定SignTicket和SignTicketApproval表直到事务提交，防止其他读取操作拿到工单状态的错误数据，但设置隔离级别后在SSMS里还是能读取表数据。因为这是仅插入的表，每次操作都必须检查最新记录来确定工单状态，所以我需要表级锁。求帮忙！

现有代码 #

using var _context = new EFContext();

using var transaction = _context.Database.BeginTransaction(System.Data.IsolationLevel.Serializable);

var ticket = _context.SignTicket
                     .Include(x => x.SignTicketApproval)
                     .ThenInclude(x => x.Result)
                     .Single(x => x.Id == cancelSignTicketRequest.SignTicketId.ToLong());

if (_signTicketService.GetTicketStatus(ticket) != TicketStatus.Open)
{
    throw new SignTicketStatusException("Ticket status is not open", ticket);
}

// Create new fake approval request and set __Cancel as outcome
var utcNow = DateTime.UtcNow;

var cancelSignoff = new SignTicketApproval()
            {
                AssignedTo = cancelSignTicketRequest.CanceledBy,
                Description = "User cancel",
                CreateTime = utcNow,
                Result = new SignTicketApprovalResult()
                {
                    Canceled = true,
                    ApproverEmail = cancelSignTicketRequest.CanceledBy,
                    CreateTime = utcNow,
                    Outcome = ReservedButton.CancelButton,
                    Comment = cancelSignTicketRequest.Reason
                }
            };

ticket.SignTicketApproval.Add(cancelSignoff);

_context.SaveChanges();
transaction.Commit();

问题根源 #

Serializable隔离级别默认只会添加行级范围锁来防止幻读，不会直接触发表级锁；加上SQL Server默认开启READ COMMITTED SNAPSHOT后，其他会话还能通过快照读取旧数据，所以你在SSMS里仍能查到表内容。

实现表级锁的具体操作 #

1. 给SignTicket表加排他表锁
   修改查询语句，通过FromSqlRaw添加TABLOCKX表提示，强制锁定整个表：

   var ticket = _context.SignTicket
       .FromSqlRaw("SELECT * FROM SignTicket WITH (TABLOCKX) WHERE Id = {0}", cancelSignTicketRequest.SignTicketId.ToLong())
       .Include(x => x.SignTicketApproval)
       .ThenInclude(x => x.Result)
       .Single();
   

   TABLOCKX会添加排他表锁，直到事务结束，期间其他会话无法读取或修改该表。

2. 锁定SignTicketApproval表
   如果需要同时锁定关联表，在事务开始后执行一个空查询来加锁：

   _context.Database.ExecuteSqlRaw("SELECT 1 FROM SignTicketApproval WITH (TABLOCKX)");
   

3. 注意事项

   • 表级锁会严重降低并发性能，非必要场景优先用行级锁+乐观并发（比如添加RowVersion字段）替代。
   • 尽量缩短事务执行时间，减少锁持有时长，避免阻塞其他业务操作。
   • 若数据库开启了READ COMMITTED SNAPSHOT，需关闭该选项，或者确保读取操作强制读取当前数据而非快照。

内容的提问来源于stack exchange，提问作者qwer11121