Entity Framework Core, SQL Server, and Deadlock victims

The application I am currently working on loads text files, transforms them, and then saves them as relational data.

I use Entity Framework Core with SQL Server for my persistence. Unfortunately, when inserting so much data I keep experiencing exceptions because SQL Server is throwing deadlock exceptions and preventing my data from being saved.

The insert involves data for 4 related tables, every access to the DB is an insert, which is why I was so surprised to see SQL Server couldn’t cope with it – especially as Firebird SQL didn’t struggle (nor did PostgreSQL).

It turns out the reason for this is that SQL Server sees a small table and decides to use a table lock instead of a rock lock. Apparently, these are quicker to perform and used primarily on small look-up tables. But this isn’t a look-up table, it’s a table that is currently empty but will soon contain millions of rows.

To give SQL Server a clue as to your intentions, you can add OPTION (LOOP JOIN) to the end of SQL statements and prevent this problem, so I wrote an extension to ensure it is appended to Entity Framework Core generated SQL.

It is used like so:

protected override void OnConfiguring(DbContextOptionsBuilder options)
{
	base.OnConfiguring(options);
	options.UseLoopJoinQueries();
}

Here is the extension code you need:

public static class UseLoopJoinQueriesExtension
{
	public static DbContextOptionsBuilder UseLoopJoinQueries(this DbContextOptionsBuilder builder)
	{
		if (builder is null)
			throw new ArgumentNullException(nameof(builder));

		builder.AddInterceptors(new OptionLoopJoinCommandInterceptor());
		return builder;
	}
}

internal class OptionLoopJoinCommandInterceptor : DbCommandInterceptor
{
	public override Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
	{
		AppendOptionToSql(command);
		return Task.FromResult(result);
	}

	public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
	{
		AppendOptionToSql(command);
		return result;
	}

	private static void AppendOptionToSql(DbCommand command)
	{
		const string OPTION_TEXT = " OPTION (LOOP JOIN)";
		string[] commands = command.CommandText.Split(";");

		for (int index = 0; index < commands.Length; index++)
		{
			string sql = commands[index].Trim();
			if (sql.StartsWith("insert into ", StringComparison.InvariantCultureIgnoreCase)
				|| sql.StartsWith("select ", StringComparison.InvariantCultureIgnoreCase)
				|| sql.StartsWith("delete ", StringComparison.InvariantCultureIgnoreCase)
				|| sql.StartsWith("merge ", StringComparison.InvariantCultureIgnoreCase))
			{
				commands[index] += OPTION_TEXT;
			}
		}

#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
		command.CommandText = string.Join(";\r\n", commands);
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities
	}
}