MySQL Relational Database Server and Its Tools
View All Tags
When two people buy the last item, the safe way to decide who gets it is to let the database decide, in one statement. Put the stock check in the UPDATE's WHERE, and read how many rows changed. That's all of it, and every round I ran gave one winner and four polite refusals.
If another transaction holds a row, MySQL makes your statement wait up to 50 seconds by default. For a web request, that's far too long. Here's how to set a shorter limit from EF Core, on every connection, without relying on CommandTimeout, which doesn't do this job with MySql.Data.
Some settings belong to a connection, not a statement: a time limit, a session variable, a time zone. EF Core has a hook for exactly that. A DbConnectionInterceptor runs your SQL each time a connection opens, and it's inexpensive enough not to think about.
A savepoint lets part of a transaction fail while the rest carries on. In EF Core, you create one with CreateSavepointAsync, and EF Core also creates its own around each SaveChanges inside a transaction. If you've seen __EFSavePoint in MySQL's log, that's where it comes from.
BeginTransactionAsync takes an isolation level as its first argument, and that's the whole API. The useful part is knowing what gets sent, how to confirm that MySQL really is using it, and which levels the driver refuses.
Sometimes a decision can't fit in one UPDATE: you need to read a row, think about it in C#, and only then change it, with nobody else changing it in the meantime. SELECT ... FOR UPDATE holds the row while you do that. EF Core has no LINQ method for it, so it takes FromSql.
When a connection drops during COMMIT, nobody knows whether it worked. ExecuteInTransactionAsync handles that moment: it begins, commits and retries for you, and when the commit's outcome is unknown, it calls a function you supply to find out. You still have to give that function something to look for.
Changing a stock count with ExecuteUpdateAsync and recording the reason with SaveChangesAsync looks like one job, but those are two separate transactions unless you say otherwise. If the second fails, the count has changed and nothing explains why. Here's that failure, and the fix.
EnableRetryOnFailure takes a list of numbers, and that's all it takes. With MySQL I needed two more things: to tell a dropped connection from a full pool, which share one number, and to close a broken connection before retrying. A class of your own does both, in about 20 lines.
EnableRetryOnFailure looks like a switch, and with Oracle's MySQL provider, it isn't one: until you list error numbers, it retries nothing. Here's the list I use, how to confirm it's working, and how long a request can wait.
