EF Core 6 - This SqlTransaction has completed; it is no longer usable.

Intro

  This post is about the System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable. which may be because you shared your SqlConnection or you tried to SaveChanges twice and all of the other issues that you can google for. I was not so lucky. I spent a day and a half to understand what's going on and only with a help of another dev did I get close to the issue.

TL;DR;

I used a column with identity generation, but it wasn't also a primary key and EF sucks.

Details

  Imagine my scenario first: I wanted to use a database to assign a unique integer to a string. I was first searching for the entry in the DB and, if not found, I would just insert a new one. The SQL Server IDENTITY(1,1) setting would insure I got a new unique value for the inserted row. So the table would look like this:

CREATE TABLE STR_ID (
  STR NVARCHAR(64) PRIMARY KEY,
  ID INT IDENTITY(1,1)
}

Nothing fancy about this. Now for the C# part, using Entity Framework Core 6.

I created an entity class for it:

[Table("STR_ID")]
public class StrId {

  [Column("STR")]
  [Key]
  public string Text { get; set; }

  [Column("ID")]
  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int Id { get; set; }

}

And then I proceeded to test it in the following way:

• create a DbContext instance
• search for a value by STR/Text in the proper DbSet
• if it doesn't exist, insert a new row and SaveChanges
• retrieve the generated id
• dispose the context

I also ran this 20 times in parallel (well, as Tasks - a minor distinction, but it was using the thread pool).

The result was underwhelming. It would fail EVERY TIME, with either an exception about deadlocks or 

System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable.
   at Microsoft.Data.SqlClient.SqlTransaction.ZombieCheck()
   at Microsoft.Data.SqlClient.SqlTransaction.Commit()

I did what every sane developer would do in this situation and bought myself a shotgun (we all know it's the most effective against zombies) then googled for other people having this issue. I mean, it would be common, right? You do some EF stuff in parallel and you get some errors.

No. This is happening in a parallelism scenario, but that's not the cause. Also, it's not about transactions. EF will wrap SaveChanges operations in a transaction and that is causing the error, but the transaction being completed is the issue and no, it's not your code!

I tried everything I could think of. I disabled the EF transaction and made my own, using all types of IsolationLevel, I tried EnableRetryOnFailure with hilarious results (I was monitoring the values inserted in the database with NOLOCK and they were going to 20, then back to 10, then 15, then back to 1 and it was taking ages trying to retry operations that apparently had dependencies to each other, only to almost all to fail after a long time). I even disabled connection pooling, which probably works, but would have made everything slow.

Solution

While I can't say what EXACTLY caused the problem (I would have to look into the Microsoft code and I don't feel like it now), the solution was ridiculously simple: just make the IDENTITY column a primary key instead:

CREATE TABLE STR_ID (
  ID INT PRIMARY KEY IDENTITY(1,1),
  STR NVARCHAR(64)
}

-- because this is what I am searching for
CREATE UNIQUE INDEX IX_STR_ID_STR ON STR_ID(STR) 

[Table("STR_ID")]
public class StrId {

  [Column("ID")]
  [Key]
  public int Id { get; set; }

  [Column("STR")]
  public string Text { get; set; }

}

I was about to use IsolationLevel.ReadUncommitted for the select or just set AutoTransactionsEnabled to false (which also would have solved the problem), when the other guy suggested I would use this solution. And I refused! It was dumb! Why the hell would that work? You dummy! And of course it worked. Why? Donno! The magical thinking in the design of EF strikes again and I am the dummy.

Conclusion

What happened is probably related to deadlocks, more specifically multiple threads trying to read/write/read again from a table and getting in each other's way. It probably has something to do with how IDENTITY columns need to lock the entire table, even if no one reads that row! But what it is certain to be is a bug: the database functionality for a primary key identity column and a unique indexed identity column is identical! And yet Entity Framework handles them very differently.

So, in conclusion:

• yay! finally a technical post
• this had nothing to do with how DbContexts get disposed (since in my actual scenario I was getting this from dependency injection and so I lost hours ruling that out)
• the error about transactions was misleading, since the issue was what closed the transaction inside the Microsoft code not whatever you did
• the advice of some of the AggregateExceptions up the stream (An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure' to the 'UseSqlServer' call.) was even more misleading
• the EF support for IDENTITY columns - well, it needs it because then how would it know not to attempt to save values in those columns - is also misleading, because it doesn't mean it's good support
• while parallel access to the DB made the problem visible, it has little to do with parallelism 
• EF knows how to handle PRIMARY KEYs so that's the solution
• EF sucks!

I really hope this saves time for people in the same situation!