One unit left, five simultaneous orders, and all five answered 201 Created. The order code was in a transaction, and it checked the stock first. It looked correct, and the first time I ran it, it even behaved. The check and the write were two separate steps, and every order did the check before any of them had written.
One request answered 201, and the database had two orders, with the stock taken twice. MySQL had committed the first attempt, the connection dropped before the reply arrived, and EF Core's retry, knowing only that the commit had failed, did it all again. Retrying is only safe when each attempt can tell that an earlier one already worked.
The connection dropped during a transaction, EF Core retried it as it should, and the request created two rows, with different names, from one request. MySQL had rolled the first attempt back. The extra row came from my DbContext, which still remembered the first attempt's entity.
Authentication to host '127.0.0.1' for user 'inventory_app' using method 'caching_sha2_password' failed with message: Too many connections looks like a login problem, and I went to check the password first. The password was fine. The second half of the message is the real error: MySQL had no connections left to give.
MySQL was restarting, and a statement in the middle of running failed with MySqlException: Server shutdown in progress. That's error 1053, it's temporary, and the server will be back in seconds. The fix is one number in the retry list.
Fatal error encountered during command execution sounds like something is badly wrong with the statement. Read the inner exceptions, and it ends with Attempted to read past the end of the stream: the connection went away while the driver was waiting for an answer. The statement isn't the problem, and it's usually worth trying again.
A statement was stuck behind a row lock, so I stopped it with KILL QUERY. The mysql client reports that as ERROR 1317: Query execution was interrupted. My API got System.NullReferenceException from inside MySql.Data. It's a misleading message, and once you know that, it tells you exactly what happened.
MySQL restarted while orders were coming in. The retries did their job for most requests, and then 8 of 96 failed with System.NullReferenceException from deep inside MySql.Data. None of my code was in the failing frames. A retry was using a connection that the driver had left marked as open.
A request took about 4 seconds, and then failed with RetryLimitExceededException: The maximum number of retries (3) was exceeded while executing database operations. That exception means EF Core did its job and ran out of attempts. The cause is one level down, and the client deserves a better answer than a 500.
An order hung for a while and then failed with MySqlException: Lock wait timeout exceeded; try restarting transaction, and the client got a bare 500. Another transaction held the row. Three things make this better: a shorter wait, a clearer answer for the client, and finding what's holding the row.
