Duplicating record in Insert (Add or AddAsync)

[fulviocanducci]

The System runs in Private Cloud and in the test environment I have no problem, but in the production environment at times the Insert duplicates the information generating the same record but as subsequent keys.

The code demonstrates what I basically do using the layer with Entity Framework. I've done several tests and I can't understand why this duplication sometimes in the registrations, I have a network with more than 20 people working on this Web system

Example

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Create(AccountSchedule model)
{
	if (ModelState.IsValid)
	{
		using IDbContextTransaction transaction = UnitOfWork.BeginTransaction();
		try
		{
			UnitOfWork.AccountSchedule.Add(model);
			UnitOfWork.Commit();
			transaction.Commit();
			TempData.AddSuccessCreate();
			return RedirectToAction(nameof(Edit), new { model.Id });
		}
		catch (Exception)
		{
			transaction?.Rollback();
		}
	}
	ViewData.AddError();
	await LoadSelectAsync(model);
	return View("CreateOrUpdate");
}

Code

var options = new DbContextOptionsBuilder<DataAccess>();
options.UseMySQL("Server=localhost;Database=dB;Uid=test;Pwd=232367");
options.LogTo(Console.WriteLine);
using DataAccess dataAccess = new DataAccess(options.Options);
using var transaction = dataAccess.Database.BeginTransaction();
AccountSchedule accountSchedule = new()
{
	AccountTypeId = 1,
	Description = "Example Issue",
	Value = 10.9M,
	ExpirationDay = 10,                
	Active = true
};
dataAccess.AccountSchedule.Add(accountSchedule);
dataAccess.SaveChanges();
transaction.Commit();

Ouput SQL

Executing DbCommand [Parameters=[
@p0='?' (DbType = Int32), 
@p1='?' (DbType = SByte), 
@p2='?' (DbType = DateTime), 
@p3='?' (Size = 300), 
@p4='?' (DbType = Int32), 
@p5='?' (DbType = Decimal)], CommandType='Text', CommandTimeout='30']
INSERT INTO `account_schedule` (`account_type_id`, `active`, `deleted_at`, `description`, `expiration_day`, `value`)
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
SELECT `id`
FROM `account_schedule`
WHERE ROW_COUNT() = 1
AND `id`=LAST_INSERT_ID();

Further technical details

MySQL version: 8.0.28
Operating system: Windows Server 2019 Standard Edition
Pomelo.EntityFrameworkCore.MySql version: 5.0.4
Microsoft.AspNetCore.App version: net5.0

[lauxjpn]

@fulviocanducci That is not enough information you are providing.

Start with posting the actual (full and exact) version numbers that you are using (just edit your post).

Then describe the issue that you are having in more details.
Post us some source code (C#) that produces the issue and post us the SQL that is being generated by EF Core.

[fulviocanducci]

@fulviocanducci That is not enough information you are providing.

Start with posting the actual (full and exact) version numbers that you are using (just edit your post).

Then describe the issue that you are having in more details. Post us some source code (C#) that produces the issue and post us the SQL that is being generated by EF Core.

Thank you, if by any chance relevant information is missing, please let me know and I am at your disposal. @lauxjpn

[lauxjpn]

@fulviocanducci It appears that you are not using the Pomelo.EntityFrameworkCore.MySql provider, but Oracle's provider instead.

Pomelo uses UseMySql().
Oracle uses UseMySQL().

(Different casing of the method name.)

[fulviocanducci]

the lab that I set up has Oracle, but the Web has Pomelo, lack of attention for having already tested everything @lauxjpn

[lauxjpn]

the lab that I set up has Oracle, but the Web has Pomelo, lack of attention for having already tested everything

@fulviocanducci So is the issue also appearing using Pomelo, or only using Oracle's provider?

[fulviocanducci]

already test with both and the problem happens with both, from time to time it duplicates record @lauxjpn

[lauxjpn]

[...] from time to time it duplicates record

Does that mean that the issue is non-deterministic? How often do you get duplicate record inserts (out of x inserts, y inserts are duplicates)?

---

var options = new DbContextOptionsBuilder<DataAccess>();
options.UseMySQL("Server=localhost;Database=dB;Uid=test;Pwd=232367");
options.LogTo(Console.WriteLine);
using DataAccess dataAccess = new DataAccess(options.Options);
using var transaction = dataAccess.Database.BeginTransaction();
AccountSchedule accountSchedule = new()
{
	AccountTypeId = 1,
	Description = "Example Issue",
	Value = 10.9M,
	ExpirationDay = 10,                
	Active = true
};
dataAccess.AccountSchedule.Add(accountSchedule);
dataAccess.SaveChanges();
transaction.Commit();

Were you able to reproduce the issue with this source code?
If so, are you able to reproduce the issue reliably with this source code, or only sometimes?
If only sometimes, how often do you have to run this source code to get one duplicate?

[fulviocanducci]

Were you able to reproduce the issue with this source code? SIM

[...] from time to time it duplicates record

Does that mean that the issue is non-deterministic? How often do you get duplicate record inserts (out of x inserts, y inserts are duplicates)?

var options = new DbContextOptionsBuilder<DataAccess>();
options.UseMySQL("Server=localhost;Database=dB;Uid=test;Pwd=232367");
options.LogTo(Console.WriteLine);
using DataAccess dataAccess = new DataAccess(options.Options);
using var transaction = dataAccess.Database.BeginTransaction();
AccountSchedule accountSchedule = new()
{
	AccountTypeId = 1,
	Description = "Example Issue",
	Value = 10.9M,
	ExpirationDay = 10,                
	Active = true
};
dataAccess.AccountSchedule.Add(accountSchedule);
dataAccess.SaveChanges();
transaction.Commit();

Were you able to reproduce the issue with this source code? If so, are you able to reproduce the issue reliably with this source code, or only sometimes? If only sometimes, how often do you have to run this source code to get one duplicate?

You asked for: "Post us some source code (C#) that produces the issue and post us the SQL that is being generated by EF Core." and that's what I did.

Code causing the problem:

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<ActionResult> Create(AccountSchedule model)
{
	if (ModelState.IsValid)
	{
		using IDbContextTransaction transaction = UnitOfWork.BeginTransaction();
		try
		{
			UnitOfWork.AccountSchedule.Add(model);
			UnitOfWork.Commit();
			transaction.Commit();
			TempData.AddSuccessCreate();
			return RedirectToAction(nameof(Edit), new { model.Id });
		}
		catch (Exception)
		{
			transaction?.Rollback();
		}
	}
	ViewData.AddError();
	await LoadSelectAsync(model);
	return View("CreateOrUpdate");
}

ie from the web application and problems happen from time to time, but anyway it gets in the way of the project. Remembering that this is a real case, duplication happens, but it is not a daily thing, but sporadic that is, the web application and problems happen from time to time, but in any case it hinders the progress of the project. Remembering that this is a real case, duplication happens, but it is not a daily thing, but sporadic

@lauxjpn

[mguinness]

Have you confirmed that the action isn't being called twice from the client? Should be easy to verify from the web server logs.

[lauxjpn]

@fulviocanducci This is going to be an issue in your application code, not in Pomelo. But we will try to help you figure out the issue anyway.

As @mguinness suggested, add some additional logging and also just log all EF Core generated SQL.
Add some logic that can discover, when a duplicated INSERT is happening and do some extensive logging when it happens.

Once you have logged the duplicate INSERT, check the time of the duplicate INSERT against the time of the original INSERT.
Then check the log and SQL of the original INSERT against the log and SQL of the duplicate INSERT.

---

Also think about typical user issues. For example, if the website does not load in time or as expected, users might just refresh the page (e.g. by pressing F5) and might just resubmit the same data again.
It is not uncommon for duplicate data to be the result of user behavior like that.

[fulviocanducci]

@fulviocanducci This is going to be an issue in your application code, not in Pomelo. But we will try to help you figure out the issue anyway.

As @mguinness suggested, add some additional logging and also just log all EF Core generated SQL. Add some logic that can discover, when a duplicated INSERT is happening and do some extensive logging when it happens.

Once you have logged the duplicate INSERT, check the time of the duplicate INSERT against the time of the original INSERT. Then check the log and SQL of the original INSERT against the log and SQL of the duplicate INSERT.

Also think about typical user issues. For example, if the website does not load in time or as expected, users might just refresh the page (e.g. by pressing F5) and might just resubmit the same data again. It is not uncommon for duplicate data to be the result of user behavior like that.

I'm currently changing the code and removing BeginTransaction and I'm going to check the Logs, I emphasize that the problem is not exactly in the code as it is used on another server and everything works as it should.

I will report here what happened.

Thank you very much for your collaboration

I even have a package for EF that does a non-physical deletion of the record, would that be too difficult to put in Pomelo? (https://www.nuget.org/packages/Canducci.SoftDelete/)

[mguinness]

I even have a package for EF that does a non-physical deletion of the record, would that be too difficult to put in Pomelo? (https://www.nuget.org/packages/Canducci.SoftDelete/)

Any soft delete functionality should be addressed upstream, it's under consideration in dotnet/efcore#22959 so please upvote.

[fulviocanducci]

I realized that after I removed "BeginTransaction" in the code (that is) the transaction control these two days became more stable, I wanted to leave it as an investigation of the new API on the subject

[fulviocanducci]

I even have a package for EF that does a non-physical deletion of the record, would that be too difficult to put in Pomelo? (https://www.nuget.org/packages/Canducci.SoftDelete/)

Any soft delete functionality should be addressed upstream, it's under consideration in dotnet/efcore#22959 so please upvote.

@mguinness, How would that be, could you explain in more detail?

[mguinness]

Any soft delete functionality should be implemented in EF Core not at the provider level, i.e. this is universal, not provider specific.

[fulviocanducci]

Any soft delete functionality should be implemented in EF Core not at the provider level, i.e. this is universal, not provider specific.

True, very well remembered!