MySQL Relational Database Server and Its Tools
View All Tags
Under load, half the orders failed with MySqlException: Deadlock found when trying to get lock; try restarting transaction, and with an HTTP 500. The code looked fine. Each order just locked its products in whatever order the client listed them, so two orders could each hold what the other needed.
As soon as I wrapped two writes in BeginTransactionAsync, the endpoint failed with The configured execution strategy 'MySQLRetryingExecutionStrategy' does not support user-initiated transactions. The message tells you the method to use, and here's the shape of the code it means.
A request that normally takes a millisecond failed after exactly 15 seconds with error connecting: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. MySQL itself was fine and nearly idle. All the app's connections were taken, by requests that were never going to finish.
I set a short CommandTimeout, expecting an error when a statement ran too long. Instead, the request never answered at all. MySQL had finished long ago, and the connection sat idle, out of reach of everything else. If you're on Oracle's MySql.Data, any async statement slower than CommandTimeout can do this, including a default of 30 seconds behind a row lock.
I had EnableRetryOnFailure turned on, so I assumed a deadlock or a short outage would be retried. With Oracle's MySQL provider, nothing was retried, ever, and no message says so. The provider asks each error whether it's temporary, and MySqlException always answers no. The fix is one argument.
SET SESSION innodb_print_all_deadlocks = ON failed with ERROR 1229 (HY000): Variable 'innodb_print_all_deadlocks' is a GLOBAL variable and should be set with SET GLOBAL. The message tells you exactly what to do. There's also a catch, which is that it takes an administrator.
ERROR 1044 (42000): Access denied for user 'lock_viewer'@'%' to database 'inventory' looks like a login failure, and it isn't one: your password was right. This account just has no rights on the database you named. It's different from ERROR 1045, and has a different fix.
Reading sys.innodb_lock_waits failed with ERROR 1356 (HY000): View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them. The view isn't broken. The end of that long message is the real reason: the account lacks a right.
GRANT PROCESS ON inventory.* TO ... failed with ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES. The syntax is fine. Some privileges apply to the whole server, and can't be limited to one database.
A ROLLBACK came back with Query OK, 0 rows affected, 1 warning, and the warning was The creation of some temporary tables could not be rolled back. Your data was rolled back correctly, and only the temporary table stayed. Here's how to see it and avoid it.
