HiLo sequence shared across different databases - concurrency issues

[davidroth]

Ported from: dotnet/efcore#21067

Hey folks,

we are experiencing some serious concurrency issues when running our xunit test suite with parallelization enabled.
After hours of debugging (I first thought It is a bug in our test infrastructures code) I finally narrowed it down to EF cores value generation implementation.

The issue: EF caches/re-uses HiLo sequences across multiple db connections.

Technical details

• EfCore 3.1.3
• EfCore.PostgreSQL 3.1

Steps to reproduce

I created a sample application which demonstrates the issue.

async Task Main()
{
	var db = "first";
	var db2 = "second";
	static string GetConnection(string db) => $"Host=localhost;Port=8432;Database={db};User ID=postgres;Password=password";
	var list1 = new List<Person>();
	var list2 = new List<Person>();
	var dumpFirst = new DumpContainer(list1);
	var dumpSecond = new DumpContainer(list2);
	dumpFirst.Dump(GetConnection(db));
	dumpSecond.Dump(GetConnection(db2));

	void Drop(string db)
	{
		using var context = OpenContext(GetConnection(db));
		context.Database.EnsureDeleted();
		context.Database.EnsureCreated();
	}
	Drop(db);
	Drop(db2);

	for (int i = 0; i < 5; i++)
	{
		using (var context = OpenContext(GetConnection(db)))
		{
			var person = new Person();
			list1.Add(person);
			await context.Persons.AddAsync(person);
			dumpFirst.Refresh();
			await context.SaveChangesAsync();
		}
		using (var context2 = OpenContext(GetConnection(db2)))
		{
			var person2 = new Person();
			list2.Add(person2);
			await context2.Persons.AddAsync(person2);
			dumpSecond.Refresh();
			await context2.SaveChangesAsync();
		}
	}

	for (int i = 0; i < 5; i++)
	{
		using (var context2 = OpenContext(GetConnection(db2)))
		{
			var person2 = new Person();
			list2.Add(person2);
			await context2.Persons.AddAsync(person2);
			dumpSecond.Refresh();
			await context2.SaveChangesAsync();
		}
	}
}

private SampleContext OpenContext(string connectionString)
{
	var builder = new DbContextOptionsBuilder<SampleContext>();
	var lf = new LoggerFactory();
	lf.AddProvider(new MyLoggerProvider());
	builder.UseLoggerFactory(lf);
	builder.UseNpgsql(connectionString);

	var context = new SampleContext(builder.Options);
	return context;
}


class SampleContext : DbContext
{
	public SampleContext(DbContextOptions options)
		: base(options)
	{ }

	public DbSet<Person> Persons { get; set; }

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		base.OnModelCreating(modelBuilder);
		modelBuilder.Entity<Person>().Property(x => x.Id).UseHiLo("Person_Sequence");
	}

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

class Person
{
	public int Id { get; set; }
	public override string ToString() => Id.ToString();
}


public class MyLoggerProvider : ILoggerProvider
{
	public ILogger CreateLogger(string categoryName)
	{
		return new MyLogger();
	}

	public void Dispose()
	{ }

	private class MyLogger : ILogger
	{
		public bool IsEnabled(LogLevel logLevel)
		{
			return true;
		}

		public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
		{
			if (eventId == Microsoft.EntityFrameworkCore.Diagnostics.CoreEventId.ValueGenerated)
			{
				LINQPad.Util.SqlOutputWriter.WriteLine(formatter(state, exception) + eventId);
			}
		}

		public IDisposable BeginScope<TState>(TState state)
		{
			return null;
		}
	}
}

Wrong behavior 1 (Unique key exception from database):

As you can see the same sequence is used for both DbContexts. Thats wrong because each of them uses a distinct db connection string.

As a result, each context (context1 + context2) inserts 5 persons via the same sequence.
After the first loop, only context2 tries to add 5 additional items, and queries its own sequence.
=> Boom: Exception because a primary key is used multiple times.

DbUpdateException:
23505: duplicate key value violates unique constraint "PK_Persons"

Wrong behavior 2 (Same key value tracked in context)

If you slightly adjust the sample code (expand the lifetime of context2), the following exception is thrown:

The instance of entity type 'Person' cannot be tracked because another instance with the same key value for {'Id'} is already being tracked.

Code:

     // After Drop(db2);
	using var context2 = OpenContext(GetConnection(db2));
	for (int i = 0; i < 5; i++)
	{
		using (var context = OpenContext(GetConnection(db)))
		{
			var person = new Person();
			list1.Add(person);
			await context.Persons.AddAsync(person);
			dumpFirst.Refresh();
			//await context.SaveChangesAsync();
		}
		
		var person2 = new Person();
		list2.Add(person2);
		await context2.Persons.AddAsync(person2);
		dumpSecond.Refresh();
		//await context2.SaveChangesAsync();
	}

	for (int i = 0; i < 5; i++)
	{
		var person2 = new Person();
		list2.Add(person2);
		await context2.Persons.AddAsync(person2);
		dumpSecond.Refresh();
		await context2.SaveChangesAsync();
	}

Expected behavior:

Sequences must never be shared across different db connections, even when using the same db context.

This is a very tricky issue because its very hard to narrow down when running a unit-test suite concurrently. I never expected this behavior, so this was a bit frustrating to debug 😅

[roji]

Note: this issue is the same as dotnet/efcore#11451, which was never ported to EFCore.PG.