MySQL Relational Database Server and Its Tools
View All Tags
As soon as a DbContext has a retrying execution strategy, a plain BeginTransactionAsync stops working. The strategy has to know the whole group of statements, so it can run them all again. It only takes a wrapper, with one detail in the lambda and one at the top of it.
SaveChangesAsync protects one call. When several calls have to succeed or fail together, you need a transaction of your own, and it takes three lines. The last of them decides everything: without it, nothing you did is saved.
Yes. Each call to SaveChangesAsync runs in a transaction of its own, so if one row fails, none of them are saved. That's easy to prove with MySQL's own log, and it also shows you where the protection stops.
Cancelling an order puts its stock back. Cancel it twice, and the stock goes back twice. Reading the status and then updating leaves a gap for exactly that to happen. Put the expected status in the WHERE, and only the first update can change the row.
To see locks, you need rights that an application's account shouldn't have. So don't give them to it. Make a small second account just for looking: it can read who's waiting for whom, and it can't read or change your tables.
Reading a quantity, checking it, and then writing a new one takes three steps, and two people can both pass the check. A single UPDATE with the check in its WHERE takes one step. MySQL holds the row's lock from the check to the change, so there's no gap to fall into. It's the simplest safe way to take stock.
Two copies of your app are running, and only one of them should run the nightly report. There's no row to lock. GET_LOCK gives you a lock on a name you make up, held by one connection at a time, and it's the same tool EF Core uses to stop two migrations running at once.
An ALTER TABLE is stuck, and everything that arrives after it queues up behind it. The row-lock views are empty, because this is a different kind of lock: a metadata lock, which protects a table's structure. The holder is often something harmless looking, such as a transaction that ran one SELECT and never finished.
A report runs several queries, and if the numbers move underneath it, they won't add up. A read-only transaction with a consistent snapshot pins down what it sees and guarantees it can't change anything by accident. Here's what each part does.
A report with a bad join can run for hours and take the server down with it. max_execution_time tells MySQL to stop a SELECT after a set time. The limit lives on the server, so it works no matter which program sent the query.
