Add support for single statements without transactions

[lauxjpn]

Design decisions:

• We will stop wrapping single-updates SaveChanges with transactions.
• For SQL Server, we will prepend SET IMPLICIT_TRANSACTIONS OFF for SQL Server to ensure autocommit is on. Other providers which have autocommit opt-outs will need to do the same (/cc @lauxjpn for MySQL, where you could prepend SET autocommit=0).
• For single-update SaveChanges that uses multiple SQL commands (INSERT+SELECT), we'll be stopping to use those because of concurrency issues which can affect the results (#27446) (at the same time, INSERT+SELECT should be fine without a transaction).

See dotnet/efcore#27439 (comment) by @roji upstream.

[nightcoder62]

This is old, I know, but I'm currently having issues with 7.0.0-silver.1 (on EF7.0.1, MySqlConnector 2.2.5) where, mysteriously, SOME changes (deletes on certain tables is what we've seen so far - possibly related to foreign keys and/or triggers, but that's still unclear) won't commit unles we start our own transaction and commit it. Database.CurrentTransaction IS null when we call SaveChanges(), SaveChanges() DOES return 1 as expected (and the relevant SQL statements are logged as successfully executed in the debug log), but in the database, nothing happens.

I found the change (optimization) in the EF7 "what's new" doc, and that led me here. I'm definitely suspecting that something is biting us here. Old database (5.6.21-log), but worked flawlessly with EF6.

Any ideas, anyone?

[roji]

@nightcoder62 can you post some code and especially the SQL that EF is sending to the DB?

[nightcoder62]

@nightcoder62 can you post some code and especially the SQL that EF is sending to the DB?

Like so:

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (17ms) [Parameters=[@p0='2826'], CommandType='Text', CommandTimeout='30']
DELETE FROM `Reservation`
WHERE `Id` = @p0;
SELECT ROW_COUNT();

That's what I have saved - I remember seeing the usual log entries about changing entity state to Deleted and so on, nothing unexpected at all. Again, it happens on an table that usually has a delete trigger, but removing the trigger hasn't helped us (unless we messed up while testing, I'm not sure). Wrapping the whole thing in a transaction manually worked like a charm, though (although there are hundreds of places in the code base where we would have to do that, so no thanks).

With debug level logging, there is NOTHING mentioned about a transaction being started or committed (unless we do it manuella), exactly as it is described in the EF7 documentation about the change. Here (search for "Unneeded transactions are eliminated" on the page):

https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/whatsnew

Sorry for the edits, new here ... got the code block right, finally. Also, a mention: @roji . Thanks for noticing me!

[nightcoder62]

Possibly relevant to my issue above - we're using InnoDB. I just saw this page, and it got me thinking that "autocommit = 0" might be counterproductive, as in causing our problem:

https://dev.mysql.com/doc/refman/5.6/en/innodb-autocommit-commit-rollback.html

Also, I just remembered that we once tried executing the delete with raw SQL - same result. It looks like it's working, but doesn't stick (so someone IS starting a transaction under the hood and it's NOT getting committed).

I'll run more tests over the weekend, if I can narrow it down I'll post an issue mentioning this one.

Mentioning @roji again, just in case.

[roji]

Yeah, that's sounding like SQL Server's implicit transactions feature, which we explicitly disable whenever we do SaveChanges where we omit the transaction; this issue would be about doing the same for MySQL.

Although if that's really the source of the issue, then something must be turning off autocommit @nightcoder62? As long as autocommit is set to 1, this shouldn't be a problem.

Finally, you can disable the "don't start a transaction" optimization by using the following (e.g. in your DbContext's constructor):

Database.AutoTransactionBehavior = AutoTransactionBehavior.Always;

[nightcoder62]

Haha @roji , that's great, I had JUST started to dig for how to do that, I knew it had to be in there somewhere. I'll be testing that in a few minutes, I think. I'll be back with the outcome.

[nightcoder62]

MY HERO @roji !

I showed this to my colleague and he had the project open - it worked LIKE A CHARM!

I'll gather my thoughts and open a new issue on it in this project, as I think it's here it should be fixed (in the EF7 compatibility effort).

Suspicion: The suggested change (in the comment on EF7 by @lauxjpn ) is probably implemente somewhere, and breaks things under certain circumstances. But we'll see.

Thanks again!

[roji]

@nightcoder62 the thing to understand, in any case, is why autocommit is off - I know little about MySQL and about the EF provider, but at least for other databases autocommit is definitely on by default.

[nightcoder62]

@roji Yes, that's my thinking, too. It could be a local configuration issue or default we've overlooked, or it could be the MySql EF provider. I'll try to get to the bottom of that before I open an issue, I think (or I'll just mention the different suspicions - regardless of cause, others should perhaps be made aware of the issue).

EDIT: In any case, it happened when upgrading to EF7 (and the prerelease provider supporting it), so it's definitely related to the changed behaviour of single statements in EF7. And as the comment starting this issue mentions SET autocommit=0 as if it were the same as SET IMPLICIT_TRANSACTIONS OFF in SQL Server, while in fact it is the opposite, that may be the root cause (if it ever got implemented in the provider).

[nightcoder62]

@roji An even MORE interesting thing is why it only affected some tables and not all. But it's a really old MySQL database with an originally auto-generated context, and there's weird stuff everywhere, so I'm guessing SOME of the weirdness somehow affects the behaviour. But again, we'll see.